concurrency violation : Updated 0 of the expected 1 records

Question

Can some one please assist me? How to get rid of this error?

concurrency violation : Updated 0 of the expected 1 records

I have been struggling with the update command for the last couple of days. I tried many solutions from the internet but I am unable to resolve this issue.

Imports Microsoft.Office
Imports System.Data.OleDb

Public Class Form1
    Dim dt As New DataTable
    Dim cnn As New OleDb.OleDbConnection

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'filling analyst filter
        Dim sText As String = String.Empty
        Dim sConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\ecxx-store\Exxx xxxxsk\Txx Axxx\Enxx Flxxx\Source\Exx_Exxx_Flagging.accdb" 'Put your connection string in here
        Using cn As New OleDb.OleDbConnection(sConnString)
            cn.Open()
            Dim cmd As New OleDb.OleDbCommand("SELECT Distinct Analyst FROM EF ORDER BY Analyst", cn)
            Dim r As OleDb.OleDbDataReader = cmd.ExecuteReader()
            While r.Read()
                ComboBox7.Items.Add(r("analyst"))
            End While
            r.Close()
            cn.Close()
        End Using
        'end of filling analyst filter
        Me.EFTableAdapter.Fill(Me.Exx_Exxxx_ShippingDataset.EF) 
        Me.Label19.Text = "Welcome " & StrConv(Environment.MachineName, vbProperCase)
        Me.Label20.Text = EFBindingSource.Count.ToString() & " entries"
        'Setting Followup Status
        Me.ComboBox8.Text = "Waiting for approval"
        'disabling Second Analyst Details
        Me.txtsecondanalyst.Enabled = False
        Me.txtapproval.Enabled = False
        Me.txtnotes.Enabled = False 
    End Sub

    Private Sub txtdof_ValueChanged(sender As Object, e As EventArgs) Handles txtdof.ValueChanged
        Me.txtnd.Text = DateDiff(DateInterval.Day, CDate(txtdof.Text), Now())
    End Sub

    Private Sub ComboBox7_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox7.SelectedIndexChanged
        If IsNothing(Me.ComboBox8.Text) Then
            EFBindingSource.Filter = "[Analyst] LIKE '%" & ComboBox7.Text & "%'"
        Else
            EFBindingSource.Filter = "[FollowupStatus] LIKE '%" & ComboBox8.Text & "%' AND [Analyst] Like '%" & ComboBox7.Text & "%'"
        End If
        'count of datagrid
        Me.Label20.Text = EFBindingSource.Count.ToString() & " entries"
    End Sub

    Private Sub ComboBox8_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox8.SelectedIndexChanged
        If IsNothing(Me.ComboBox7.Text) Then
            EFBindingSource.Filter = "[FollowupStatus] LIKE '%" & ComboBox8.Text & "%'"
        Else
            EFBindingSource.Filter = "[FollowupStatus] LIKE '%" & ComboBox8.Text & "%' AND [Analyst] Like '%" & ComboBox7.Text & "%'"
        End If
        'count of datagrid
        Me.Label20.Text = EFBindingSource.Count.ToString() & " entries"
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'update
        Me.txtnd.Visible = True
        Me.txtnd.Text = DateDiff(DateInterval.Day, CDate(txtdof.Text), Now())
        On Error GoTo SaveErr
        EFBindingSource.EndEdit()
        EFTableAdapter.Update(Exx_Exxxx_ShippingDataset.EF)
        Me.txtsecondanalyst.Enabled = False
        Me.txtapproval.Enabled = False
        Me.txtnotes.Enabled = False
        MsgBox("Record Updated or Saved")
SaveErr:
    End Sub

    Private Sub Form1_Closed(sender As Object, e As EventArgs) Handles Me.Closed
        EFBindingSource.EndEdit()
        Me.TableAdapterManager1.UpdateAll(Me.Exx_Exxx_ShippingDataset)
    End Sub
End Class

This is too much source in my opinion. Consider write an [MCVE](http://stackoverflow.com/help/mcve). You might find your solution alone that way too. — J. Chomel, Jul 18 '16 at 15:21
Seems like it's vb6 converted to vb.net. `On Error GoTo` and `MsgBox()` are for vb6 support. Class level `Dim` just hurts my eyes. Hey, at least he has a `Using` block (albeit with `cn.Close()` :). — djv, Jul 19 '16 at 17:40

score 0 · Answer 1 · edited May 23 '17 at 10:28

According to Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. DB concurrencyException was unhandled

You call

Me.EFTableAdapter.Fill(Me.Exx_Exxxx_ShippingDataset.EF)

in Form_Load, but there is no guarantee that the rows aren't changed by the time you call

EFTableAdapter.Update(Exx_Exxxx_ShippingDataset.EF)

in Button1_Click

In the linked answer,

ADO.Net keeps the value of the column when it was selected from the database. When it performs the update the condition is that none of the columns have changed when you commit.

Perhaps you should call

dt = dt.GetChanges()

immediately before updating in Button1_Click, so that the datatable gets updated with the latest values in the database.

Thank you so much @verdolino, I will try your solution and update you. — rocky09, Jul 19 '16 at 15:09

concurrency violation : Updated 0 of the expected 1 records

1 Answers1