DataTable.Update Fails and Throws DBConcurrencyException

Question

I'm trying to take advantage of the DataTable.Update method to update a SQL data source. Below is the code for the method that performs the update.

string connString = "SQL connection string...this works.";
string sqlSelect = "SELECT Payment, Amount, Date, Month, StartDate, EndDate, FROM Payment";

private void updateDataSource(DataTable dt) {
    SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connString);
    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    int result = 0; // store the result of dt.Update

    // copy over any rows from dt that have a payment date
    DataTable temp = dt.Clone(); 
    foreach (DataRow dr in dt.Rows) {
        if (dr.ItemArray[5].ToString() != "") // if payment date is not empty
            temp.ImportRow(dr);
    }

    da.ContinueUpdateOnError = true; // this forces an insert but does not update any other records

    try {
        result = da.Update(temp);
    } catch (DBConcurrencyException dbce) {
        alertUser(
            @"There was an error updating the database.\n" +
            dbce.Message + @"\n" +
            @"The payment type id for the row was: " + dbce.Row.ItemArray[1] + @"\n" +
            @"There were " + temp.Rows.Count + @" rows in the table to be updated.\n");
    }

    if (result == temp.Rows.Count) {
        alertUser("Successful update."); // alert the user
        btnSearchCancel_Click(null, null);
    }

    // store updated data in session variable to store data between posts to server
    Session["gv"] = dt;
}

The above method is called when the user clicks an 'Update Table' button.
What is happening is before I included the da.ContinueUpdateOnError = true the try catch would throw the DBConcurrencyException giving Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. And no records would be updated/inserted in the table.
After I added da.ContinueUpdateOnError = true the da.Update() would continue without error but, the first row of DataTable dt would still not be updated, however, the second row of dt would be inserted.

Even more strange is that when I am calling the update passing a table of ~20 rows the update executes perfectly, updating 2, or 3 rows and inserting 2 or three rows. If I call the update passing a table of 2 rows the exception is thrown. The two different tables have the same structure.

score 1 · Accepted Answer · answered Jun 09 '15 at 19:56

1

This error only occurs when, to quote MSDN

An attempt to execute an INSERT, UPDATE, or DELETE statement resulted in zero records affected.

To get this error means that the database has changed since the DataTable was created.

The error tells you that

the UpdateCommand affected 0 of the expected 1 records

One of the records that was trying to be updated was not there anymore or had changed and no longer matches the expected signature.

For reference: DBConcurrencyException and DbDataAdapter.Update Method, a little more explanation.

It seems that there may be some other code that is changing the database after the DataTable is created, or you are running on a production DB and other users are making changes.

answered Jun 09 '15 at 19:56

jwatts1980

7,254
2
28
44

I was filling two different DataTables with two different stored procedures. Inside my `updateDataSource` method I was cloning each of those tables to a new table and then calling `DbDataAdapter.Update` on that. It was after I made the two stored procedures return data in the same structure as each other that the error was eliminated. – gh0st Jun 09 '15 at 20:33
2

@gh0st that makes sense. When the `Update()` is trying to cross reference the DataTable, it is internally building UPDATE, INSERT, and DELETE statements based on the schema. So when the internal system tried to build the UPDATE, something was not matching, and no record could be found. The `Update` command was interpreting this as a change to the database. And with Optimistic Concurrency, it can't work with changes. – jwatts1980 Jun 09 '15 at 20:41

DataTable.Update Fails and Throws DBConcurrencyException

1 Answers1