Error: System.Data.DBConcurrencyException: 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 occured. 

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 for your understanding of the Data Row and it's status.

The article 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 is not exist 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

         1. New Row - Added

         2. Modified Rows - unchanged

         3. 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 !!