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.