Error : Update requires a valid UpdateCommand when passed DataRow collection with modified rows

Question

I am using Paging to show data in datagridview, but when i try to Update any data with updatebutton data should be updated In datagridview as well as in database.

But I get this error:

Update requires a valid UpdateCommand when passed DataRow collection with modified 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()); } } }

Chris · Accepted Answer · 2013-09-02 18:21:47Z

You have Created the OleDbDataAdapter with a Select command only:

adp1 = new OleDbDataAdapter(cmd1);

OleDbDataAdapter requires valid Update, Insert, Delete commands to be used to save the data like this:

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

You just need to use a OleDbCommandBuilder that 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 command of the OleDbDataAdapter at runtime for paging, what your need is to initialize each 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()); } }

Yes you are right ,now I am not getting error and data is updated,Thank you :)

Eaint · Accepted Answer · 2015-01-29 08:48:58Z

It could be that you are missing Primary Key in the table. You need to make sure primary key is set on a column in your data base table.

Community · Accepted Answer · 2017-05-23 12:16:38Z

I had to alter my (incrementing) index column to the primary key of my table (as Eaint suggest). After this, I had to pull up the DataSet.xsd in designer view, right click on the visual DataTable object and select configure. When the TableAdapter Configuration Wizard opened, I clicked the Advanced Options button. I checked the Generate Insert, Update and Delete statements checkbox then OK and Finish. After this (still in designer view), I selected the visual TableAdapter object which gave me all the full properties. The SQL was autogenerated. Took a while for me to track this down, so I hope it helps someone.

Uwe Keim · Accepted Answer · 2021-04-07 07:00:51Z

Thanks to "@Chris" the code above works for me. I needed to specify the Database Table name that will be updated when I Update. You can read more about that here:

DataAdapter: Update unable to find TableMapping['Table'] or DataTable 'Table'

// This Adapter and Dataset are used for Populating my datagridview, // so I use them also when I need to Update the Datagridview SqlDataAdapter kundeTlfAdapter; DataSet kundeTlfDataSet; try { SqlConnection connection = new SqlConnection("Data source=BG-1-PC\\SQLEXPRESS; Database = Advokathuset; User Id = abc; Password = abc;"); SqlCommand cmd1 = new SqlCommand("Select* From Kunde_Tlf", connection); SqlCommandBuilder builder = new SqlCommandBuilder(kundeTlfAdapter); kundeTlfAdapter.SelectCommand = cmd1; // cmd1 is your SELECT command kundeTlfAdapter.Update(kundeTlfDataSet, "Kunde_Tlf"); //I get eror here if I dont add the name of the table that needs Update "Kunde_Tlf" } catch (Exception err) { MessageBox.Show(err.Message.ToString()); }

Collectives™ on Stack Overflow

Error : Update requires a valid UpdateCommand when passed DataRow collection with modified rows

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Linked

Related