How to update a table from a DataGrid?

Carlos Sanchez

How can I retrieve data from a Table in a SqlServer, populate a DataGrid, modify the data in the DataGrid and UpDate the Data of the table? I tried this: MyDataAdapter = new SqlDataAdapter("SELECT * FROM MyTable", MyConnection); //Public DataAdapter SqlCommand MyUpDateCommand = new SqlCommand("UPDATE MyTable SET Column1 = @Column1, Column2 = @Column2", MyConnection); MyCommand.Parameters.Add("@Column1",System.Data.SqlDbType.Bit, 1, "Column1"); MyCommand.Parameters.Add("@Column2",System.Data.SqlDbType.VarChar, 50, "Column2"); MyDataAdapter.UpdateCommand=MyUpDateCommand; MyDataSet = new DataSet(); //Public DataSet MyDataAdapter.Fill(MyDataSet, "MyTable"); MyDataGrid.SetDataBinding(MyDataSet, "MyTable");//Also I tried MyDataGrid.DataSource = MyDataSet.Tables["MyTable"]; //Method of de Save Button SaveButton_Click(object sender, System.EventArgs e) { MyDataAdapter.Update(MyDataSet, "MyTable"); } The above it doesn't work. What am I missing? Could Any1 help me? Thanx in advance.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Carlos Sanchez21y ago
It's done!! thank you AW, I was missing the WHERE clause. thank you very much, I appreciate your help. regards!!
0
Andrzej Wegierski21y ago
Setting InsertCommand is not necessary, because You haven't inserted rows. Using AcceptChanges is wrong, it causes that any data is not saved - so You don't get any error. Error is in parameters. I don't use SqlCommand, so I didn't know about this. I found it in help: ----------------------- cmd = new SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " + "WHERE CustomerID = @oldCustomerID", conn); cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); parm = cmd.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID"); parm.SourceVersion = DataRowVersion.Original; da.UpdateCommand = cmd; ------------------------ So: - You must have where clause - for params in where clause You must use Original SourceVersion - use another version Parameters.Add method
0
Carlos Sanchez21y ago
Sorry, the right code is the below: SqlCommand MyUpDateCommand = new SqlCommand("UPDATE MyTable SET Column1 = @Column1, Column2 = @Column2", MyConnection); MyUpDateCommand.Parameters.Add("@Column1",System.Data.SqlDbType.Bit, 1, "Column1"); MyUpDateCommand.Parameters.Add("@Column2",System.Data.SqlDbType.VarChar, 50, "Column2"); MyDataAdapter.UpdateCommand=MyUpDateCommand; Why I have to set the InsertCommand? I didn't. Before to Update I have the next line: MyDataSet.AcceptChanges(); If I supress this line an exception is thrown, somethig like: the UpDate Command expect the parameter @Column1. I inserted the line you told me, examined the xml file and all the rows appear whit the changes. Any idea? what am I doing wrong? Thanx in advance again.
0
Andrzej Wegierski21y ago
Your code looks fine, but UpdateCommand is MyUpDateCommand and parameters are added to MyCommand. Check SaveButton_Click method with code line: MyDataSet.WriteXml("c:\\mydataset.xml",XmlWriteMode.DiffGram); and examine this file. May be SaveButton_Click method isn't called? I think, that You don't insert any row (InsertCommand is not set).
0