Concurrency violation: the UpdateCommand affected 0 of the expected 1 records, Error occurs for few tables in DB but not for all tables

Question

I am using VB.Net with MS Access for Database (Local DB) I am using Databound Controls and strongly typed Dataset generated through Wizard.

My App is Single User / Single Instance Application so no scope of changing of data by any mean by others.

I have few tables in my DB, working with them I found this error occurs on few of them not with all table update.

what I am trying is Adding new record to binding source (by pressing + button on binding navigator) typed field values and Update (by pressing SaveItem button on BindingNavigator)

Public Class Form2

    Private Sub InvoicesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InvoicesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.InvoicesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CA_OfficeDataSet)

    End Sub

    Private Sub CA_OfficeForm2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
        Me.InvoicesTableAdapter.Fill(Me.CA_OfficeDataSet.Invoices)

    End Sub
End Class

this works fine if I keep adding new Records, but If I try to change value of any record that was just added & press Update button the said Error is thrown. Many Tables have Similar Structure & there forms have Identical code.

Surprising thing here is the Error won't fires with all table update.

I have added different forms for Each table (for Insert / Update / Delete). Performed similar operation adding new record -> saving it -> again changing same record. and found the Concurrency error occurs with few table update and few not.

Answer 1

I found the answer to my question.
Problem is not related to any code.
As I said previously, My app is single user, single instance application using Local MS Access Database for Backend, Neither I was changing any record directly in DB so there was no possibility of Change of data after Update, nor I am using any AutoNumber Field in any Table.

The Problem was with Type of Data Columns in the Tables.
e.g. Consider following 2 Tables with Column Name and Data Types

SuppliersTable
ID --> Number
SuppName --> Text
SuppAddress --> Text
DueAmount --> Number

CustomersTable
ID --> Number
CustName --> Text
CustAddress --> Text
DueAmount --> Number
PaymentRecd --> Yes/No

In the above 2 tables, SuppliersTable will never throw any DB Concurrency Error irrespective  of Number of times you add / edit data to it, But CustomersTable won’t allow you to Edit Newly Added record immediately until reloaded next time.
And this is because of **Boolean Type Data Column** (PaymentRecd) present in the Table.
When we do not set Value for Boolean type column Explicitly, code will only allow to Add new Records but do not allows to Edit any record if new records added in the session.

**SOLUTION**:
We have many Solutions to this
1] Do not Edit the Record in the Same Session after adding.
2] Close and Re-Open the Form, then go for Edit.
3] In the TableAdapter Configuration Wizard -> Advanced Options.. -> Uncheck Use Optimistic concurrency Check Box (surely not recommended for many)
4] Set Value for Boolean type Column Explicitly before Updating Record (be it concerned or not that time).
5] In the Dataset Designer, Set Boolean Type Column’s “DefaultValue” Property to False. (BEST SOLUTION)

When a Dataset is Generated through Wizard, Table column’s few properties like MaxLength are Fetched from DB but Not Default Value for the column if any, so such problems arises.

Answer 2

This is a common issue. What's happening is that, when you add a new record to your DataTable, it is generating a temporary PK value for that record. When you save that data, the row goes from being Added to Unchanged, which is supposed to indicate that it is in the same state as the database. The database is generating the final PK value for the record but you are not retrieving that value from the database back into your DataTable, so the PKs don't match.

If you then edit that record and try to save it, it will try to update the record in the database with the same PK as is stored in the DataTable. That record is either not present or contains data that doesn't match what's in the DataTable. ADO.NET knows that the only legitimate reason for that to happen is that someone else has modified the database outside the application, so it throws a concurrency exception.

The solution is to retrieve the PK value generated by the database back into your DataTable. That's very simple in many databases - a simple case of including a SELECT statement in the InsertCommand.CommandText. The Jet and ACE providers for Access don't support multiple statements in the same command though, so you have to work a little harder.

Here is a thread of mine that provides an explanation and an example of how to do it. With or without a typed DataSet. Basically, you need to handle the RowUpdated event of your table adapter, detect an insert, query the database for the last generated PK and update the DataRow with that. Here's the relevant code from the example in that thread:

Private Sub parentAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs)
    'We are only interested in new records.
    If e.StatementType = StatementType.Insert Then
        'Get the last ID auto-generated by the database.
        Dim lastAutoNumber = Me.parentAdapter.GetLastAutoNumber().Value

        'Update the ID of the local row.
        DirectCast(e.Row, ParentChildDataSet.ParentRow).ParentID = lastAutoNumber
    End If
End Sub

That is calling a method that I added to the table adapter in the designer. ExecuteMode for the method is Scalar, i.e. it returns a single value, and the CommandText is this:

SELECT @@IDENTITY

@@IDENTITY is a global variable in the database that always contains the last auto-generated ID.