Post
Ask Question

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

Problem

An ADO.Net Dataset uses Optimistic Concurrency by default. If an attempt is made to update a row to the database when the row no longer exists in the database then the result is the error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

I am trying to give a scenario where that error can occur. 

// data row has been added 
DataRow dr= null;

dtTab = (DataTable)Session("dtTab");

dr = dtTab.Rows(e.RowIndex);

dr.Delete();

dtTab.AcceptChanges();

 
// If Acceptchanges() being not called, row status will be
//detached, that will not be updated to database.
 
//Without updating database Acceptchanges() called.Row status
//changed to deleted. If this update to database,
// it will give concurrency error-  
// because row no longer exist in database.

The reason for the error became apparent. The records in the row in the table didn't exist in the database. However, the DeleteCommand was trying to remove them from the Database. And when the data adapter class attempts to delete a record and doesn't see any rows being changed, it assumes that a concurrency violation has occurred. 

The solution was to take a little more control of the deletion process.

dataAdapter.ContinueUpdateOnError = true;
dataAdapter.ContinueUpdateOnError = true;
dataAdapter.Update(reportsData);
if (reportsData.ReportColumn.HasErrors) {
      DataRow[] drs = reportsData.ReportColumn.GetErrors();
      foreach (DataRow dr in drs) {
            if (dr.RowError.Substring(21) == "Concurrency violation") {
                  reportsData.ReportColumn.RemoveReportColumnRow((ReportsData.ReportColumnRow)dr);
            }
      }
      reportsData.ReportColumn.AcceptChanges();
      // If the dataset still has errors, then an exception needs to be thrown
      if (reportsData.ReportColumn.HasErrors) {
            throw new DataException("An exception was raised while updating the ReportColumn data table: " + reportsData.ReportColumn.GetErrors()(0).RowError);
      }
}

The ContinueUpdateOnError property is used to stop the aborting of the updates on an exception.  Once this is done, we check to see if the data table has any errors.  If so, we make sure that every row that has a concurrency violation is removed from the Dataset.  The AcceptChanges method call is required to update the HasErrors flag, if all of the errors had been eliminated in this manner. 

The following table is for your understanding of the Data Row and its status.

The article is given for understanding data set row status.

Execution Sequence Row Event Row Status Accept Changes() called Result.
1 Add Added No Row Added
2 Delete Detached No After deletion row is detached, it will not send to database until AcceptChanges()called.
3     Yes Row will be deleted, call database and update
         
1 Add Added No Row Added
2     Yes Without updating in Database
3   Unchanged No Added row status changed to unchanged
4 Delete Deleted No Row not exists in database

It causes Concurrency violation

 
// data row has been added
DataRow dr = null;

dtTab =(DataTable)Session("dtTab");

dr = dtTab.Rows(e.RowIndex);

dr.Delete();

dtAdapt.Update(dtTab);
//without AcceptChanges(), update data in database. Once AcceptChanges() called row will //be marked as deleted. It will find in database table.
//If you have row //in Table and you have fetched that trying to delete and commit AcceptChanges() //then it will delete from database.

I have explained in the above solution why and where we can use AcceptChanges() in a dataset.

I have given some idea for AcceptChanges() and Update().

DataSet.AcceptChanges()

  1. When AcceptChanges is being called on the DataSet, the DataRow object will finish their edits successfully.
  2. RowState property changes to
    • New Row - Added
    • Modified Rows - unchanged
    • Deleted Rows - Removed

DataAdapter.Update()

When the Update method is being called, the DataAdapter will see RowState Property, and executes respective statements INSERT, UPDATE, or DELETE.

Conclusion 

DataAdapter.Update() will recognize only those rows that have changed RowStates.

DataSet.AcceptChanges() sets the RowState of all rows to unchanged.

So any other method attempted to be called after the AcceptChanges() method has been called will not see any changes to update !!


Similar Articles