none
Concurrency violation: the DeleteCommand affected 0 of the expected 1 records. RRS feed

  • Question

  •  

    I receive this error in the subject with the following code...

     

    Code Snippet

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    'Delete a record

    If ListBox1.SelectedIndex >= 0 Then

    Dim table1 As DataTable

    table1 = Me.Free_Password_SafeDataSet.FPS

    Dim deletion_query As String = "ID = '-1'"

    Dim rows = table1.Select(deletion_query)

    rows(0).Delete()

    Me.FPSTableAdapter.Update(Me.Free_Password_SafeDataSet.FPS)

    End If

    End Sub

     

     

    Can anyone explain why I receive this concurrency error when I click the button?

     

    The situation is as follows;

     

    I open the program, and add a record. The record displays in the Listbox. Then I select the record from the listbox, and press the Delete button (Button2) - the error appears.

     

    Then, I open the program, and delete the record and it deletes without error.

     

    Any idea what is happening?

     

    Thanks in advance,

     

    A. Obrzut

    Monday, November 3, 2008 7:52 PM

Answers

  • What happens is when you have an identity column, DataTable generates temporary negative identities for new rows added to the DataTable. When you call Update method of the DataAdapter, it will know which rows are the new one (based on status of the rows) and should be inserted into database. Provider will insert the rows and mark them as existing in database, but will NOT automatically update ID column with the generated identity for that row. DataAdapter will not do it automatically. On next call you might receive above error because provider looks for the row (that marked as existed in database) with provided identity in your DataTable and this identity does not exist.
    Here are some samples that show how to deal with the identity columns in .NET

    http://support.microsoft.com/kb/320141

    http://support.microsoft.com/kb/815629
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, May 13, 2009 10:06 AM
    Moderator

All replies

  •  

    Another point of interest;

     

    The primary key, or ID, field in the database that I am using to delete a record starts at -1 when the program is adding records, follows onto -2, -3 and so forth. However, upon closing and opening the program again, the records start at 1,2,3,4 and so on. Has this anything to do with the record deletion?

    Monday, November 3, 2008 7:56 PM
  • Do you get this error when you call Update method of TableAdapter or somewhere else? Do you have primary key for your table in a database?

    Monday, November 3, 2008 7:56 PM
    Moderator
  •  

    I can add records, and I call the update method there without error. I have a primary key, the ID field, as a unique identifier of the database that changes as records are added.

     

    However, as I mentioned, the ID (primary key) field changes from minus to positive on program reload.

     

    Another point to make clear, is when the ID is positive, the record deletes without error.

    Monday, November 3, 2008 8:01 PM
  •  

    Yes, from my testing I can safely say that when the ID field is negative, the records do not get deleted and the concurrency error is produced. However, on 'reboot' of the program, when the records are positive, they are immediately deleted without error. However, whenever I add a new record, I am presented with an ID of -1 or below/above...

     

    I would like to add, when I am adding a new record, I am leaving the ID field blank (ommited from the row1 dataset) because if I set it to 0, then all records are 0 and the primary key refuses to accept a duplicate.

     

    Therefore, how do I go about getting a positive number as a primary key that ascends - without having to check the previous key and add one to it?

    Monday, November 3, 2008 8:19 PM
  •  

    Actually, I set the ID to time of day hour,minute, then second. Tried deleting a record, got the same error. Upon reboot - the ID had changed again to a single digit positive - and the record could be deleted again.

     

    Any ideas?

    Monday, November 3, 2008 8:27 PM
  • I have the same problem, any one?
    • Proposed as answer by JonBrave Tuesday, May 12, 2009 5:46 PM
    • Unproposed as answer by VMazurModerator Wednesday, May 13, 2009 9:54 AM
    Friday, May 1, 2009 6:10 PM
  • [Sorry, I didn't mean to propose the previous one as an answer --- this is the answer!]

    • The "Concurrency Violation" message is just SQL/ASP.NET's way of saying "I couldn't find a matching row to delete".
    • By default, when you add a row to a (disconnected) DataTable, incrementing IDs actually start at -1 and go further negative (so that there's no clash with any genuine IDs in the existing rows).
    • When you send your row off to be inserted, SQL ignores your ID and assigns it the next available highest positive one.
    • If you try to delete the row you started with in your DataSet, it will still have the negative ID, SQL won't find it, and you'll get the error.
    • As part of your SQL INSERT, you need to read back the @@IDENTITY value that SQL just used, and AcceptChanges() into your DataTable so that the row you added gets modified to show the ID it actually got.
    • There should be examples of this around, try "Concurrency Violation" in MSDN and there should be an example.
    Tuesday, May 12, 2009 5:55 PM
  • What happens is when you have an identity column, DataTable generates temporary negative identities for new rows added to the DataTable. When you call Update method of the DataAdapter, it will know which rows are the new one (based on status of the rows) and should be inserted into database. Provider will insert the rows and mark them as existing in database, but will NOT automatically update ID column with the generated identity for that row. DataAdapter will not do it automatically. On next call you might receive above error because provider looks for the row (that marked as existed in database) with provided identity in your DataTable and this identity does not exist.
    Here are some samples that show how to deal with the identity columns in .NET

    http://support.microsoft.com/kb/320141

    http://support.microsoft.com/kb/815629
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, May 13, 2009 10:06 AM
    Moderator