当我点击datagridview的单元格时,我想要更新我的数据库,实际上我想通过单击一个单元格来编辑我的数据库和datagridview。
我在我的数据库中有一个id,它是自动填充的,当我插入并保存s.th我需要由我的sql填充id。并且我的代码不起作用:
代码1。
private void dgvlist_Enter(object sender, EventArgs e) { SqlConnection con = new SqlConnection(); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); con.ConnectionString = "server=(local);database=tel;trusted_connection=yes;"; con.Open(); cmd.Connection = con; da.SelectCommand = cmd; DataGridViewCell cell = dgvlist.SelectedCells[0] as DataGridViewCell; string value = cell.Value.ToString(); cmd.CommandText = ("update information set name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id"); cmd.Parameters.AddWithValue("@id", value); cmd.Parameters.AddWithValue("@name", value); cmd.Parameters.AddWithValue("@family", value); cmd.Parameters.AddWithValue("@telephone", value); cmd.Parameters.AddWithValue("@mobile", value); cmd.ExecuteNonQuery(); }代码2。
private void btnsave_Click(object sender, EventArgs e) { if (txtname.Text == null || txtfamily.Text == null || txttel.Text == null) { MessageBox.Show("please fill the neccessary boxes"); } else { save(); } } void save() { SqlConnection con = new SqlConnection(); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); con.ConnectionString = "server=(local);database=tel;trusted_connection=yes;"; con.Open(); cmd.Connection = con; da.SelectCommand = cmd; cmd.CommandText = ("insert into information id = @id , name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id"); cmd.Parameters.AddWithValue("@id", ""); cmd.Parameters.AddWithValue("@name", txtname.Text); cmd.Parameters.AddWithValue("@family", txtfamily.Text); cmd.Parameters.AddWithValue("@telephone", txttel.Text); cmd.Parameters.AddWithValue("@mobile", txtmobile.Text); cmd.ExecuteNonQuery(); }I want to update my database when i click on a cell of my datagridview in fact i want to edit my database and datagridview by clicking on a cell.
And I have an id in my database that it's auto fill and when i insert and save s.th i need that id fill by my sql.and there are my codes that does not work :
code 1.
private void dgvlist_Enter(object sender, EventArgs e) { SqlConnection con = new SqlConnection(); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); con.ConnectionString = "server=(local);database=tel;trusted_connection=yes;"; con.Open(); cmd.Connection = con; da.SelectCommand = cmd; DataGridViewCell cell = dgvlist.SelectedCells[0] as DataGridViewCell; string value = cell.Value.ToString(); cmd.CommandText = ("update information set name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id"); cmd.Parameters.AddWithValue("@id", value); cmd.Parameters.AddWithValue("@name", value); cmd.Parameters.AddWithValue("@family", value); cmd.Parameters.AddWithValue("@telephone", value); cmd.Parameters.AddWithValue("@mobile", value); cmd.ExecuteNonQuery(); }code 2.
private void btnsave_Click(object sender, EventArgs e) { if (txtname.Text == null || txtfamily.Text == null || txttel.Text == null) { MessageBox.Show("please fill the neccessary boxes"); } else { save(); } } void save() { SqlConnection con = new SqlConnection(); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); con.ConnectionString = "server=(local);database=tel;trusted_connection=yes;"; con.Open(); cmd.Connection = con; da.SelectCommand = cmd; cmd.CommandText = ("insert into information id = @id , name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id"); cmd.Parameters.AddWithValue("@id", ""); cmd.Parameters.AddWithValue("@name", txtname.Text); cmd.Parameters.AddWithValue("@family", txtfamily.Text); cmd.Parameters.AddWithValue("@telephone", txttel.Text); cmd.Parameters.AddWithValue("@mobile", txtmobile.Text); cmd.ExecuteNonQuery(); }最满意答案
简单的答案
DataGridView.Enter仅在DataGridView获得焦点并被输入时触发; 当你进入一个新的细胞时。 此外,在您已经输入单元格之后,还没有进行更改。 最后,您的更新可能会失败,因为where id = @id您认为应该的情况where id = @id不会评估为True 。 您的sql Insert语句格式不正确,因为错误指出:“ 语法不正确 ”。更深入的解释
假设您有以下数据库设置:
+---+-------+--------+-----------+----------+ |id | name | family | telephone | mobile | +---+-------+--------+-----------+----------+ |1 | John | Smith | 123-4567 | 123-4560 | |2 | Jane | Doe | 987-6543 | 987-6540 | |3 | Jenny | Tutone | 867-5309 | 867-5309 | +---+-------+--------+-----------+----------+以及您生成的DataGridView :
现在假设我们拼错了约翰史密斯的名字,而这应该是乔恩 。 假设某些其他控件当时具有焦点,则单击第0行第1列中的单元格 - 现在触发您的方法( 在进行任何更改之前 ):
DataGridViewCell cell = dgvlist.SelectedCells[0] as DataGridViewCell; string value = cell.Value.ToString(); cmd.CommandText = ("update information set name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id"); cmd.Parameters.AddWithValue("@id", value); cmd.Parameters.AddWithValue("@name", value); cmd.Parameters.AddWithValue("@family", value); cmd.Parameters.AddWithValue("@telephone", value); cmd.Parameters.AddWithValue("@mobile", value);
实际上,正在运行的查询是:
update information set name ='John' , family='John' , telephone='John' , mobile = 'John' where id = 'John'由于没有where id = 'John'记录,因此不会更新任何记录。 另一方面,如果您首先单击第0行第0列中的单元格,您的查询看起来会很喜欢:
update information set name ='1' , family='1' , telephone='1' , mobile = '1' where id = '1'这将导致以下数据库更改:
+---+-------+--------+-----------+----------+ |id | name | family | telephone | mobile | +---+-------+--------+-----------+----------+ |1 | 1 | 1 | 1 | 1 | |2 | Jane | Doe | 987-6543 | 987-6540 | |3 | Jenny | Tutone | 867-5309 | 867-5309 | +---+-------+--------+-----------+----------+因此,您需要修复在命令参数中设置的值。 然后,您应该考虑将此更新代码移动到其他方法,例如DataGridView.CellEndEdit ,以便在当前单元格中进行任何更改后更新它。
至于你的插入语句,一个简单的谷歌搜索会显示正确的格式。 更改以下内容:
cmd.CommandText = ("insert into information id = @id , name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id");以下内容:
cmd.CommandText = ("insert into information (name, family, telephone, mobile) values (@name, @family, @telephone, @mobile)");应该不需要将id添加到语句中,因为它应该是数据库中的标识列,并且将自动生成。
Simple Answer
DataGridView.Enter is only triggered when the DataGridView receives focus and is entered; not when you enter a new cell. Also, changes haven't been madeuntil after you've already entered the cells. Lastly, your update probably fails because where id = @id isn't evaluating to True when you think it should. Your sql Insert statement is incorrectly formatted, as the error states: "Incorrect syntax".Deeper Explanation
Let's assume you have the following database setup:
+---+-------+--------+-----------+----------+ |id | name | family | telephone | mobile | +---+-------+--------+-----------+----------+ |1 | John | Smith | 123-4567 | 123-4560 | |2 | Jane | Doe | 987-6543 | 987-6540 | |3 | Jenny | Tutone | 867-5309 | 867-5309 | +---+-------+--------+-----------+----------+And your resulting DataGridView:
Now suppose we misspelled John Smith's name, which should be Jon instead. Assuming some other control has focus at the time, you click on the cell in row 0 column 1 - your method is now triggered (before any changes are made):
DataGridViewCell cell = dgvlist.SelectedCells[0] as DataGridViewCell; string value = cell.Value.ToString(); cmd.CommandText = ("update information set name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id"); cmd.Parameters.AddWithValue("@id", value); cmd.Parameters.AddWithValue("@name", value); cmd.Parameters.AddWithValue("@family", value); cmd.Parameters.AddWithValue("@telephone", value); cmd.Parameters.AddWithValue("@mobile", value);
Effectively, the query that is being run is:
update information set name ='John' , family='John' , telephone='John' , mobile = 'John' where id = 'John'Since there are no records where id = 'John', no records are updated. On the other hand, had you instead first clicked on the cell in row 0 column 0, your query would have looked liked:
update information set name ='1' , family='1' , telephone='1' , mobile = '1' where id = '1'Which would result in the following database changes:
+---+-------+--------+-----------+----------+ |id | name | family | telephone | mobile | +---+-------+--------+-----------+----------+ |1 | 1 | 1 | 1 | 1 | |2 | Jane | Doe | 987-6543 | 987-6540 | |3 | Jenny | Tutone | 867-5309 | 867-5309 | +---+-------+--------+-----------+----------+Therefore, you need to fix which values you set in your command parameters. Then you should consider moving this updating code to a different method, such as DataGridView.CellEndEdit, so that it's updated after any changes are made in the current cell.
As for your insert statement, a simple google search would have shown the correct format. Change the following:
cmd.CommandText = ("insert into information id = @id , name =@name , family=@family , telephone=@telephone , mobile = @mobile where id = @id");To the following:
cmd.CommandText = ("insert into information (name, family, telephone, mobile) values (@name, @family, @telephone, @mobile)");There should be no need to add the id into the statement as it should be an identity column in your database and will be auto-generated.
更多推荐
发布评论