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

  • Question

  • I am using Visual Basic Express 2010 and System.Data.OleDb to manipulate a MS Access 2000 database.  I don’t understand why I can’t add a database record and then immediately deleted without getting a Concurrency violation.  I am told this a an SQL issue, but I don’t understand why because the scope of my SQL statement specifies “all” records.  If I add a record, it should fall within the scope of “all” records.


    I was able to find a work-around – If I clear and re-fill my DataSet after each add call, I can then delete the newly added record without the Concurrency violation.  However, this work-around looks extremely inefficient.  Is there a better way to do what I want here?


    Module JobCPDMain

        Sub Main()

            Dim dbConnection As OleDbConnection
            Dim dbDataAdapter As OleDbDataAdapter
            Dim custCB As OleDbCommandBuilder
            Dim dbDataSet As DataSet

            Dim dbConnString As String
            Dim dbSql As String
            Dim recCount As Integer

            dbConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                         & "User ID=Admin;" _
                         & "Data Source=JobCPD.mdb"

            dbSql = "SELECT * FROM [Job] ORDER BY IndexNum"

            dbConnection = New OleDbConnection(dbConnString)
            dbDataAdapter = New OleDbDataAdapter(dbSql, dbConnection)
            custCB = New OleDbCommandBuilder(dbDataAdapter)
            dbConnection.Open()
            dbDataSet = New DataSet
            dbDataAdapter.Fill(dbDataSet, "Job")

            recCount = dbDataSet.Tables("Job").Rows.Count - 1

            '// ADD NEW RECORD
            dbDataSet.Tables("Job").Rows.Add()
            recCount = recCount + 1
            dbDataSet.Tables("Job").Rows(recCount).Item("IndexNum") = "2011 NEW"
            dbDataSet.Tables("Job").Rows(recCount).Item("ContractNum") = "2011 NEW"
            dbDataSet.Tables("Job").Rows(recCount).Item("JobDescription") = "NEW RECORD ADDED"
            dbDataAdapter.Update(dbDataSet.Tables("Job"))

            '// WORK-AROUND PROCESS
            dbDataSet.Clear()                                  '// This code add to "reset" the ddDataSet so added recs can be
            dbDataAdapter.Fill(dbDataSet, "Job")    '// immediately deleted without Concurrency violation!

            '// DELETE NEW RECORD
            dbDataSet.Tables("Job").Rows(recCount).Delete()
            dbDataAdapter.Update(dbDataSet.Tables("Job"))   '<<== Concurrency violation: the DeleteCommand affected 0 of the expected 1

            dbConnection.Close()

        End Sub

    End Module

    Wednesday, January 5, 2011 4:17 PM

Answers

  • Usually that could be because a table in database does not have a primary key. Do you have it defined inside of your JOB table? Also, in some cases provider is just not able to resolve information from SEWLECT statement to build proper DELETE statement. In this case make sure that you explicitly specified DELETE SQL statement for the DeleteCommand of the DataAdapter.

     


    Val Mazur (MVP) http://www.xporttools.net
    Thursday, January 6, 2011 11:56 AM
    Moderator

All replies

  • Usually that could be because a table in database does not have a primary key. Do you have it defined inside of your JOB table? Also, in some cases provider is just not able to resolve information from SEWLECT statement to build proper DELETE statement. In this case make sure that you explicitly specified DELETE SQL statement for the DeleteCommand of the DataAdapter.

     


    Val Mazur (MVP) http://www.xporttools.net
    Thursday, January 6, 2011 11:56 AM
    Moderator
  • Yes, I do have a primary key.  Do you have a code example of an explicit DELETE SQL statement?  Thanks.
    Thursday, January 6, 2011 1:58 PM
  • Hello Mikel,

    How was your issue? Did you solve it? 

    Because your thread has lasted for a long time, I will close it for you by marking an answer. If you have anything you need or you would like to re-open it , you can unmark or you can post a thread another.

    Thank you for you understanding and support!

     

    Happy everyday!


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 17, 2011 7:38 AM
    Moderator