Error: System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

Error: System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

Problem:   

                        ADO.net Dataset which uses Optimistic Concurrency by default. When updating datarow to database but that datarow no longer exist in database. This causes above error. Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

I am trying to give scenario where error can occur.

    Scenario 1

/* data row has been added */

          Dim dr As DataRow

          dtTab = CType(Session("dtTab"), DataTable)

          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 of error became apparent. The records in Row in table didn’t exist in database. However, the DeleteCommand was trying to remove them from 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 taken place.

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

          dataAdapter.ContinueUpdateOnError = True

          dataAdapter.Update(reportsData)

      If reportsData.ReportColumn.HasErrors Then

          Dim drs As DataRow() = reportsData.ReportColumn.GetErrors()

          For Each dr As DataRow In drs

If dr.RowError.Substring(21) = "Concurrency violation" Then

                    reportsData.ReportColumn.RemoveReportColumnRow(DirectCast(dr, ReportsData.ReportColumnRow))

                End If

            Next

            reportsData.ReportColumn.AcceptChanges()

            ' If the dataset still has errors, then an exception needs to be thrown

            If reportsData.ReportColumn.HasErrors Then

                Throw New DataException("An exception was raised while updating the ReportColumn data table: " + reportsData.ReportColumn.GetErrors()(0).RowError)

            End If

        End If

The ContinueUpdateOnError property is used to stop the aborting of the updates on a 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.

Below Table for understanding Data Row with its 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

Solution:

Code give as  below.

        ' data row has been added

        Dim dr As DataRow

        dtTab = CType(Session("dtTab"), DataTable)

        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 above solution why and where we can use AcceptChanges() in dataset.

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

DataSet.AcceptChanges()

"When you call AcceptChanges on the DataSet, any DataRow objects still in edit-mode successfully end their edits. The RowState property of each DataRow also changes; Added and Modified rows become Unchanged, and Deleted rows are removed."

DataAdapter.Update()

"When an application calls the Update method, the DataAdapter examines the RowState property, and executes the required INSERT, UPDATE, or DELETE statements iteratively for each row..."

This is very clear.  DataAdapter.Update() only recognized those rows that have one of the changed RowStates, and DataSet.AcceptChanges() sets the RowState of all rows to unchanged.  So any other method that attempts to Update a datasource after AcceptChanges() has been called will not see any changes to update!