Concurrency violation using ACCESS DATABASE... [FIXED]

[Johnkokk]

Project in VS2008.Although i searched here for fixes, i didn't find anything of use. I saw this http://www.codeproject.com/Forums/1650/NET-Framework.aspx?fid=1650&select=2572091&tid=2572091[^] solution, but i didn't try it because it in C, and i can't translate it to VB :) Ok, there is an ACCESS DATABASE, where the user can store various information. The database DOES have an autogenarated unique primary key. When the user saves the record and then tries to delete right away THIS record, the "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records." comes up. However if the user reruns the program and tries to delete, everything is normal and work. The problem is that when the user updates the database, for some reason the **record does not get the normal primary key until you rerun the program.**This much i know.What i dont't know, is how to fix this problem.Trying to refill the adapter after i update the record does not seem to correct it. The code to update the database is this.

DataSet31.Tables(0).NewRow()
....
.... Part where the user fills in the information he wants to store in the database
....
....
DataSet31.Tables(0).Rows.Add()
OleDbDataAdapter3.Update(DataSet31.Tables(0))
DataSet31.Tables(0).AcceptChanges()

And the code to delete the record is this

DataSet31.Tables(0).Rows(rowindex).Delete()
OleDbDataAdapter3.Update(DataSet31.Tables(0)) <------ Here is when the problem occurs
DataSet31.Tables(0).AcceptChanges()

Thanks in advance.

modified on Saturday, November 7, 2009 4:22 AM

[Johnkokk]

Well, some minutes later I seem to have solved the problem. I actually ported the C, solution to VB and it solved. Here is the solution. On adapter rowupdated event add the following code.

Private Sub OleDbDataAdapter3_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles OleDbDataAdapter3.RowUpdated
If (e.StatementType = StatementType.Insert) Then

        Dim cmd As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)

        'If your ID field has different name than **Id**, replace the Id part on the next line, whith the correct
        e.Row("Id") = cmd.ExecuteScalar()
        e.Row.AcceptChanges()

    End If

End Sub

Hope this helps