UpDate a Table

Question

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(@Column1System.Data.SqlDbType.Bit 1 Column1); MyCommand.Parameters.Add(@Column2System.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.

Carlos Sanchez · Answer

It's done already. I was missin' the WHERE clause. thank you MikeR I appreciate your help n' ur time. regards!

miker · Answer

Something like this (I did not debug it so you will have to do it): MySelectCommand = new SqlCommand(SELECT * FROM MyTable); MyDataAdapter = new SqlDataAdapter(MySelectCommandMyConnection); //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(@Column1System.Data.SqlDbType.Bit 1 Column1); MyUpDateCommand.Parameters.Add(@Column2System.Data.SqlDbType.VarChar 50 Column2); MyUpdateAdapter = new SqlDataAdapter(MyUpDateCommandMyConnection); MyDataAdapter.Update(MyDataSet MyTable); }

Carlos Sanchez · Answer

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(@Column1System.Data.SqlDbType.Bit 1 Column1); MyUpDateCommand.Parameters.Add(@Column2System.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.

miker · Answer

1. Yes it is. 2. Manually create UpdateCommand each time you need to update database.

Carlos Sanchez · Answer

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.

miker · Answer

Check out SqlCommandBuilder class.