UpDate a Table

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.

6 Replies

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

Carlos Sanchez21y ago
It's done already. I was missin' the WHERE clause. thank you MikeR, I appreciate your help n' ur time. regards!
0
miker21y ago
Something like this (I did not debug it, so you will have to do it): MySelectCommand = new SqlCommand("SELECT * FROM MyTable"); MyDataAdapter = new SqlDataAdapter(MySelectCommand,MyConnection); //Public DataAdapter MyDataSet = new DataSet(); //Public DataSet MyDataAdapter.Fill(MyDataSet, "MyTable"); MyDataGrid.SetDataBinding(MyDataSet, "MyTable");//Also I tried MyDataGrid.DataSource = MyDataSet.Tables["MyTable"]; //Save Button Method SaveButton_Click(object sender, System.EventArgs e) { 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"); MyUpdateAdapter = new SqlDataAdapter(MyUpDateCommand,MyConnection); MyDataAdapter.Update(MyDataSet, "MyTable"); }
0
Carlos Sanchez21y ago
Ok, but when I use the below code an exception is thrown at the UpDate line. MyDataAdapter = new SqlDataAdapter("SELECT * FROM MyTable", MyConnection); //Public DataAdapter 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; MyDataSet = new DataSet(); //Public DataSet MyDataAdapter.Fill(MyDataSet, "MyTable"); MyDataGrid.SetDataBinding(MyDataSet, "MyTable");//Also I tried MyDataGrid.DataSource = MyDataSet.Tables["MyTable"]; //Save Button Method SaveButton_Click(object sender, System.EventArgs e) { MyDataAdapter.Update(MyDataSet, "MyTable"); } The exception that is thrown is something like: the UpDate Command expect a parameter @Column1 What am I doing wrong? Thank you one more time.
0
miker21y ago
1. Yes, it is. 2. Manually create UpdateCommand each time you need to update database.
0
Carlos Sanchez21y ago
I have just done that and my catch blok cacth the next exception: Dynamic SQL generation is not supported against multiple base tables. My select command retrive data from 2 tables, is this the problem? What can I do? thanx in advance again.
0
miker21y ago
Check out SqlCommandBuilder class.
0