Error: A valid UpdateCommand is required for updating when transferring a DataRow collection with changed rows

Question

I use Paging to display data in a datagridview , but when I try to update any data using updatebutton , the data needs to be updated in the datagridview as well as in the database.

But I get this error:

Updates require a valid UpdateCommand when passing a DataRow collection with changed rows

which happens on this line:

 adp1.Update(dt);//here I am getting error 

Below is the code

 public partial class EditMediClgList : Form { public EditMediClgList() { InitializeComponent(); try { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb"); con.Open(); } catch (Exception err) { MessageBox.Show("Error:" +err); } cmd1 = new OleDbCommand("Select * from MedicalColeges order by MedicalClgID", con); ds = new DataSet(); adp1 = new OleDbDataAdapter(cmd1); adp1.Fill(ds, "MedicalColeges"); dataGridView1.DataSource = ds; // Get total count of the pages; this.CalculateTotalPages(); // Load the first page of data; this.dataGridView1.DataSource = GetCurrentRecords(1, con); } private void CalculateTotalPages() { int rowCount = ds.Tables["MedicalColeges"].Rows.Count; this.TotalPage = rowCount / PageSize; if (rowCount % PageSize > 0) // if remainder is more than zero { this.TotalPage += 1; } } private DataTable GetCurrentRecords(int page, OleDbConnection con) { dt = new DataTable(); if (page == 1) { cmd2 = new OleDbCommand("Select TOP " + PageSize + " * from MedicalColeges ORDER BY MedicalClgID", con); // CurrentPageIndex++; } else { int PreviouspageLimit = (page - 1) * PageSize; cmd2 = new OleDbCommand("Select TOP " + PageSize + " * from MedicalColeges " + "WHERE MedicalClgID NOT IN " + "(Select TOP " + PreviouspageLimit + " MedicalClgID from MedicalColeges ORDER BY MedicalClgID) ", con); // + //"order by customerid", con); } try { // con.Open(); this.adp1.SelectCommand = cmd2; this.adp1.Fill(dt); txtPaging.Text = string.Format("page{0} of {1} pages", this.CurrentPageIndex, this.TotalPage); } finally { // con.Close(); } return dt; } private void button1_Click(object sender, EventArgs e) { try { adp1.Update(dt);//here I am getting error } catch (Exception err) { MessageBox.Show(err.Message.ToString()); } } } 

Answer 1

You created the OleDbDataAdapter only with the Select command:

 adp1 = new OleDbDataAdapter(cmd1); 

OleDbDataAdapter requires valid Update , Insert, Delete commands that will be used to save such data:

 adp1.Update(dt);//here I am getting error 

You just need to use OleDbCommandBuilder , which will generate the commands for you:

 adp1 = new OleDbDataAdapter(); adp1.SelectCommand = cmd1; // cmd1 is your SELECT command OleDbCommandBuilder cb = new OleDbCommandBuilder(adp1); 

EDIT

Since you change the Select OleDbDataAdapter at runtime to swap, you need to initialize every time you save data:

 private void button1_Click(object sender, EventArgs e) { try { adp1.SelectCommand = cmd1; // cmd1 is your SELECT command OleDbCommandBuilder cb = new OleDbCommandBuilder(adp1); adp1.Update(dt); //here I hope you won't get error :-) } catch (Exception err) { MessageBox.Show(err.Message.ToString()); } } 

Answer 2

Perhaps you are missing the Primary Key in the table. You must ensure that the primary key is set to a column in your database table.

Answer 3

I had to change my (incrementing) index column to the primary key of my table (as Eaint suggests). After that, I had to raise the DataSet.xsd view in the designer, right-click on the visual DataTable object and select configure. When the TableAdapter Setup Wizard opens, I clicked the Advanced Options button. I checked the Create Insert, Update, and Delete check box, then OK and Finish. After that (still in the designer’s view), I selected the visual TableAdapter object, which gave me all the full properties. SQL has been auto-generated. It took some time for me to trace this, so I hope this helps someone.