Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. Visual C# 2010

Question

i'm sorry. i wouldn't post this if i weren't out of ideas. i've tried everything in the forums, but to no avail.

so i have 2 tables. both are empty at the beginning. the first table is kind of like a customer masterlist where the user creates a new customer profile. here is the code:

        con.Open();
        System.Data.SqlClient.SqlCommandBuilder cb;
        System.Data.SqlClient.SqlCommandBuilder readingcb;
        cb = new System.Data.SqlClient.SqlCommandBuilder(da);
        readingcb = new System.Data.SqlClient.SqlCommandBuilder(readingda);



        DataRow dRow = custMaster.Tables["custMaster"].NewRow();
        DataRow readingdRow = reading.Tables["reading"].NewRow();
        dRow[1] = a_newCust.Text;
        readingdRow[9] = a_newCust.Text;
        dRow[2] = b_newCust.Text;
        readingdRow[10] = b_newCust.Text;
        dRow[3] = c_newCust.Text;
        readingdRow[11] = c_newCust.Text;
        dRow[4] = d_newCust.Text;
        readingdRow[0] = d_newCust.Text;
        dRow[5] = e_newCust.Text.ToString();
        readingdRow[2] = e_newCust.Text.ToString();
        dRow[6] = f_newCust.Text;
        readingdRow[1] = f_newCust.Text;
        dRow[7] = g_newCust.Text.ToString();
        if (radioButton1.Checked == true)
        {
            dRow[8] = radioButton1.Text;
            readingdRow[3] = radioButton1.Text;
        }
        else if (radioButton2.Checked == true)
        {
            dRow[8] = radioButton2.Text;
            readingdRow[3] = radioButton2.Text;
        }
        dRow[9] = "Active";
        readingdRow[12] = "Active";
        readingdRow[4] = 0;
        readingdRow[5] = 0;


        custMaster.Tables["custMaster"].Rows.Add(dRow);
        reading.Tables["reading"].Rows.Add(readingdRow);


        MaxRows = MaxRows + 1;
        readingMaxRows = readingMaxRows + 1;
        inc = MaxRows - 1;
        readinginc = readingMaxRows - 1;


        this.da.Update(custMaster, "custMaster");
        this.readingda.Update(reading, "reading");


        da.Fill(masterDataSet3.custMaster);
        readingda.Fill(streetDataSet.reading);
        masterfileGrid.Invalidate();
        readingGrid.Invalidate();
        masterfileGrid.Refresh();
        readingGrid.Refresh();

        MessageBox.Show("Customer succesfully added!");

        con.Close();

when this part completes, my program will create a new record in the "custMaster" table, and also a new record in the "reading" table (but this record still has some null fields). when the user goes to the "reading" table, he sees the new record, and then updates that record by adding some column values (thereby filling in the null fields). here is the code:

            con.Open();
            System.Data.SqlClient.SqlCommandBuilder readingup;
            System.Data.SqlClient.SqlCommandBuilder custpayadd;
            readingup = new System.Data.SqlClient.SqlCommandBuilder(readingda);
            custpayadd = new System.Data.SqlClient.SqlCommandBuilder(custpayda);
            int test = int.Parse(f_reading.Text);
            int prev = int.Parse(readingNinBox.Text);              


            DataRow readingdRow = reading.Tables["reading"].Rows[test-1];                  


            readingdRow[4] = prev;
            readingdRow[5] = int.Parse(d_reading.Text);
            readingdRow[13] = e_reading.Value.ToShortDateString();
            readingdRow[6] = int.Parse(d_reading.Text) - prev;


            if (g_reading.Text == "Residential")
            {
                DataRow resSearchdRow = water.Tables["water"].Rows[int.Parse(d_reading.Text) - prev];
                readingdRow[7] = resSearchdRow[2];
            }


            else
            {
                DataRow commSearchdRow = commWater.Tables["commWater"].Rows[int.Parse(d_reading.Text) - prev];
                readingdRow[7] = commSearchdRow[2];
            }               

            this.readingda.Update(reading, "reading"); //>>>>>>>>>>this part is where i get the concurrency error.

            readingda.Fill(streetDataSet.reading);                
            readingGrid.Invalidate();
            readingGrid.Refresh();
//elsewhere:

masterDataSet reading;

System.Data.SqlClient.SqlDataAdapter readingda;

int readingMaxRows = 0;

int readinginc = 0;

this.readingTableAdapter.Fill(this.streetDataSet.reading);

reading = new masterDataSet();

string readingsql = "SELECT * From reading";

readingda = new System.Data.SqlClient.SqlDataAdapter(readingsql, con);

readingda.Fill(reading, "reading");

readingMaxRows = reading.Tables["reading"].Rows.Count;

readingda.Update(reading, "reading");

i don't understand why i get this, since i use the same lines of updating codes in my other forms but they work well. any help would be greatly appreciated. thanks.

Some additional information: i only get the error the first time a record is added to the "reading" table. when i close and reopen my program, i can update the record fine.

You need to review your update command (ie, the SQL) to make sure it's correct. — Austin Salonen, Mar 01 '12 at 18:19
I guess it's the same problem as in this [question](http://stackoverflow.com/questions/1599230/anyway-see-why-i-get-this-concurrency-violation-in-these-few-lines-of-code) — W van Noort, Mar 01 '12 at 21:07

score 2 · Answer 1 · answered Feb 04 '13 at 06:34

2

i have the same problem. but i am using Visual studio 2010 with C# and windows form. i solved my problem by placing "this.contactDBDataSet.AcceptChanges(); before "this.tableAdapterManager.UpdateAll(this.contactDBDataSet);". here is the sample.

private void peopleBindingNavigatorSaveItem_Click_2(object sender, EventArgs e)
{
    this.Validate();
    this.peopleBindingSource.EndEdit();
    this.contactDBDataSet.AcceptChanges();// added by Humayoo
    this.tableAdapterManager.UpdateAll(this.contactDBDataSet);


}

answered Feb 04 '13 at 06:34

Humayoo

698
9
29

I tried AcceptChanges() method many times but it wasn't helping in my case. I know this is not very interesting to load data on every update but my problem was solved only when I called reload_data_from_db() method to fill my bindingsource again after every update. – Mojtaba Rezaeian Jan 11 '16 at 17:18
@MojtabaRezaeian, I also have to make sure I re-fill my local dataset in order to avoid a concurrency exception, but that is because I have a ModifiedDate column in the database that is updated by a trigger. That leaves my local data out of sync with my database, unless I re-fill. – JMB Sep 29 '20 at 16:48

score 2 · Answer 2 · answered Nov 20 '13 at 05:15

2

this.contactDBDataSet.AcceptChanges();

That Right !! FIN

answered Nov 20 '13 at 05:15

Donot Don't

609
7
12

4

When I call `AcceptChanges` I don't get the concurrency error but no rows get updated in the database. – mohas Feb 01 '16 at 15:54
Looks like calling AcceptChanges will set the row state to unchanged (some sort of commit on the dataset). Then calling update will not see any changes hence nothing will happen to the data source. adapter.update calls acceptchanges internally after finishing with the update. More here https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters – kuklei Nov 25 '20 at 16:50

score 1 · Answer 3 · answered Mar 26 '15 at 12:21

1

I received this error message because my workstation was set to a different time zone than my server. Once I got it back to the same time zone...problem went away

answered Mar 26 '15 at 12:21

elvisbsu

13
2

This rare situation should be very hard to find out and debugged! – Mojtaba Rezaeian Jan 11 '16 at 17:21

score 0 · Answer 4 · edited May 23 '17 at 10:31

0

This error is mostly because of updating multiple changes to database without reloading updated data between requests and when you try to save(Update) stacked changes this error may occur.

For more info check this answer by Henk which was very helpful for me.

And here there is a good example in details.

edited May 23 '17 at 10:31

Community

1
1

answered Dec 12 '15 at 16:01

Mojtaba Rezaeian

8,268
8
31
54

score 0 · Answer 5 · answered May 10 '17 at 15:23

I know this thread is old, but I'd like to share the situation in which I got this error in case anyone runs across this also from an internet search.

I was seeing this error message in a legacy app. It turned out to be a result of a previous programmer coding up some logic that retrieved a DataTable, forcibly removed a column from it, then allowed the user to edit the DataTable in a grid. The DataTable was then passed to the adapter (an OracleDataAdapter in my case) to apply any changes.

So...manually removing a column from a DataTable before sending it to the SqlDataAdapter can also result in this error message.

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. Visual C# 2010

5 Answers5

For more info check this answer by Henk which was very helpful for me.

Linked