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

  • Question

  • I have a VB.NET application that reads and updates data in MS Access database.  The above error is encountered only when updating specific records in one of the tables.  By saying "specific" I really mean that some records do get updated without a problem whilst others don't.

    About my setup:

    - I am the only one accessing the database hence it is not concurrency issue
    - The table has a single field primary key and it is included in the SELECT
    - Besides the primary key field all other fields are optional
    - I am using CommandBuilder to generate UPDATE

    I have tried to play with field values but could not find unique pattern of failing records.

    Most of the table fields are of type Date/Time.  I have found one comment on the subject saying that the issue may be caused by having values with date only in some fields and date + time in others.  However, I could not find a proof of that on my system.

    Here is the corresponding code, I am simply filling he dataset, bind some controls on a form and save the data after modification:

     

    	'form initialization
    connection = New OleDbConnection(connectionString)
    connection.Open()
    Dim req = "Select * from MyTable"
    adapter = New OleDbDataAdapter(req, connection)
    Dim builder As DbCommandBuilder = New OleDbCommandBuilder(adapter)
    Dim ds As DataSet = New DataSet
    adapter.Fill(ds, "mytable")
    table = prefs.Tables(0)

    'Bind controls
    DateTimePicker1.Format = DateTimePickerFormat.Custom
    DateTimePicker1.CustomFormat = "M/d/yyyy H:m:s"
    DateTimePicker1.DataBindings.Add("Text", table, "MyDate")
    txtName.DataBindings.Add("Text", table, "ID")

    ...
    'on save
    Me.BindingContext(table).EndCurrentEdit()
    adapter.Update(table)

     

     

    Thursday, January 13, 2011 4:58 PM

All replies

  • It may be concurreny since the update is based on the values of all columns rather than just the updated column.
    It can also be that the format of the date is incorrect..

    Have a look at this for an example of how to figure out what SQL is actually being sent to the database. (C# but same should work in VB):
    http://blogs.msdn.com/b/spike/archive/2010/04/07/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records.aspx
    This posting is provided "AS IS" with no warranties.
    Friday, January 14, 2011 8:24 AM
  • With one table this can happen if you twice tries to update the same dataset.

    The original values in your datarows are than not anymore the same to your current rows in the database.

    However, much more reasons from the same order.

     


    Success
    Cor
    Friday, January 14, 2011 12:15 PM
  • Thanks for the link, I have added SQL tracing code and tracked down the issue to one of the date fields.  The date value used in UPDATE's command WHERE clause for this particular field looks exactly like the date value in the database, i.e. #9/7/2010 1:00:00 PM#.  However, even an Access query with "WHERE MyDate = #9/7/2010 1:00:00 PM#" does not find any single record.  At the same time an Access query with "WHERE MyDate between #9/7/2010 1:00:00 PM# AND #9/7/2010 1:00:01 PM#" does find the record that I am trying to update.  So it seems that it is a database issue even though I have run Compact/Repair.
    Saturday, January 15, 2011 11:07 AM
  • I am 100% sure the update is only done once: I have written a small sample that just opens connection, loads the data and does an update.  The same problem occurs then.
    Saturday, January 15, 2011 11:15 AM