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

Article
2017-12-09

Question

_{Saturday, December 9, 2017 8:30 AM}

problem

update code

 Me.Validate()
            DataAdapter1.FillSchema(DataSet1, SchemaType.Mapped)
            BindingSource1.EndEdit()
            DataAdapter1.Update(DataSet1, "web_tb")

another way

  Me.Validate()
            BindingSource1.EndEdit()
            DataAdapter1.Update(DataSet1, "web_tb")
            DataSet1.AcceptChanges()
            DataGridView1.Refresh()

All replies (9)

_{Monday, December 11, 2017 9:19 AM ✅Answered | 1 vote}

Hi ahmeddc,

Please take a look the following code, you can get the affect row number.

 Dim adapter As OleDbDataAdapter
    Dim builder As OleDbCommandBuilder
    Dim ds As New DataSet("TestTable")
    Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
       
        Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb;Persist Security Info=False;"
        Dim sql As String = "select * from Test14"
        Dim conn As New OleDbConnection(str)
        conn.Open()
        Dim cmd As New OleDbCommand(sql, conn)
        adapter = New OleDbDataAdapter(cmd)
        builder = New OleDbCommandBuilder(adapter)
        adapter.Fill(ds, "TestTable")
        BindingSource1 = New BindingSource(ds, "TestTable")
        DataGridView1.DataSource = BindingSource1

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dr = BindingSource1.AddNew()
        dr("Id") = 7
        dr("Column1") = "G"
        dr("Column2") = "GG"
        BindingSource1.EndEdit()
        Dim value As Integer = adapter.Update(ds, "TestTable")

    End Sub

Best Regards,

Cherry

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].

_{Saturday, December 9, 2017 4:20 PM}

What is the principle of a DataAdapter.

You read datarows (overloaded also datasets and datables which include datarows) and change those.

In the datarows are original fields and changed fields.

Before the update is done, the dataadapter checks if the original field is still the same as it was with the first time reading.

If that is not the case, an Concurrency violation is detected and nothing is updated.

If there is no concurrency violation the update takes place and the dataadapter does it inbuild acceptchanges.

The acceptchanges set the changed fields over the original fields.

This should mean that the original fields are always the same as the database fields. However, that is not always the case for instance not with deleted rows or the first record.

A simple solution for that is to do a Fill again after an Update.

Success
Cor

_{Saturday, December 9, 2017 5:46 PM}

string

 Public con As New OleDb.OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\web_database.accdb;Jet OLEDB:Database Password=12345")
    Dim DataSet1 As New DataSet
    Dim BindingSource1 As BindingSource
    Dim BindingSource2 As BindingSource
    Dim DataAdapter1 As New OleDbDataAdapter
    Public DataTable1 As New DataTable

conection code

 Dim Table1 As OleDbCommand = New OleDbCommand(" SELECT  * from web_tb ", con)
            DataAdapter1 = New OleDbDataAdapter(Table1)
            Dim builder As New OleDbCommandBuilder(DataAdapter1)
            DataSet1 = New DataSet()
            DataAdapter1.Fill(DataSet1, "web_tb")

            BindingSource1 = New BindingSource(DataSet1, "web_tb")

            BindingSource2 = New BindingSource(DataSet1, "web_tb") Label2.DataBindings.Add("Text", BindingSource1, "ID_web", True, 1, "")

add new row

 BindingSource1.AddNew()
        Label2.Text = DataSet1.Tables("web_tb").Compute("Max(ID_web)", "ID_web >= 0") + "1"
        DataGridView1.Refresh()

update

 Me.Validate()
            BindingSource1.EndEdit()
            DataAdapter1.Update(DataSet1, "web_tb")
            DataSet1.AcceptChanges()
            DataGridView1.Refresh()

_{Sunday, December 10, 2017 12:43 AM}

What happens if you comment out the AcceptChanges ?

Have you checked HasChanges prior to executing DataAdapter.Update(DataSet1,""web_tb)?

This is from an example I just posted, does the saves, no accept changes. It uses a TableAdapter rather than a DataAdapter but below the surface of both the mechanics are the same. Ignore the KarenDialogs, that is simple a static class which wraps MessageBox into clean functions to present a MessageBox with standard options pre-set.

Private Sub customersBindingNavigatorSaveItem_Click_1(ByVal sender As Object, ByVal e As EventArgs)
    If northWindDataSet.HasChanges() Then
        If KarenDialogs.Question("Save changes back to database?") Then
            Me.Validate()
            Me.customersBindingSource.EndEdit()

            Me.ordersBindingSource.EndEdit()

            Me.tableAdapterManager.UpdateAll(Me.northWindDataSet)
        End If
    Else
        MessageBox.Show("There are no changes")
    End If

End Sub

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Sunday, December 10, 2017 8:19 AM}

What is the variable asked for ??

UpdateAll

Me.tableAdapterManager.UpdateAll(Me.northWindDataSet)

_{Sunday, December 10, 2017 12:39 PM}

It's asking for a (in this case) a NothWindDataSet as per below as shown in the tooltip.

_{Sunday, December 10, 2017 4:21 PM}

The same problem and the same error

_{Sunday, December 10, 2017 8:47 PM}

The same problem and the same error

There's another way you can do this.

Use the BindingSource's .EndEdit method (like you're doing), do NOT use .AcceptChanges.

If you ask for what the changes are, then "Nothing" means that you don't want to try to update -- if you do then you'll get the exception that you're getting.

You can use the DataTable's .GetChanges method which returns a DataTable:

https://msdn.microsoft.com/en-us/library/k2552649(v=vs.110).aspx

All you really need to know is if the .GetChanges IsNot Nothing. If that's the case (ergo, a datatable was returned so there are changes) then use the .Update. If you do it that way, it should work.

Understand though: If you try to update (save) and there's nothing to update (there are no changes) you'll get that exact exception.

"A problem well stated is a problem half solved.” - Charles F. Kettering

_{Sunday, December 10, 2017 10:02 PM}

The same problem and the same error

Do you know what the error message is telling you that is coming back from SQL Server? If you don't know the circumstance that causes the exception, then how can you fix it?

Share via

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

Question

All replies (9)

Additional resources