Results 1 to 3 of 3

Thread: [RESOLVED] Concurrency violation: the DeleteCommand affected 0 of the expected 1

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    11

    Resolved [RESOLVED] Concurrency violation: the DeleteCommand affected 0 of the expected 1

    Hi,

    I've searched over tens of threads about this common error but I can't find out where the error is in my code.

    I have a datagridview with a bindingsource and the data are stored in an access database.

    The insert and update commands works fine but when I insert new data, save to database, then immediately delete the newly added rows, as soon as I try to save, the concurrency violation appears...

    Here's my code:

    Code:
    Private Sub ConfigureDataAdapter()
            Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\DJSOC\DRUGS\CRU\DORA\DORAInv.accdb")
            'Check if the table for this year exists, if not create it
            connection.Open()
            Dim dbSchema As DataTable = connection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, INV, "TABLE"})
            If dbSchema.Rows.Count = 0 Then
                Dim cmd As New OleDbCommand($"CREATE TABLE [{INV}] ([ID] COUNTER PRIMARY KEY, [IDINT] INTEGER, [ORDER] INTEGER, [NUM] TEXT(50), [DESC] MEMO, [SAMPLES] TEXT(50), [SAMPLEST] TEXT(50))", connection)
                cmd.ExecuteNonQuery()
            End If
            connection.Close()
            'Create TableAdapter
            INVENTORYTableAdapter = New OleDbDataAdapter($"SELECT ID, IDINT, ORDER, NUM, DESC, SAMPLES, SAMPLEST FROM {INV} WHERE IDINT = {IntNum}", connection)
            'Create insert command
            Dim insert As New OleDbCommand($"INSERT INTO {INV} ([IDINT], [ORDER], [NUM], [DESC], [SAMPLES], [SAMPLEST]) VALUES (@IDINT, @ORDER, @NUM, @DESC, @SAMPLES, @SAMPLEST)", connection)
            With insert.Parameters
                .Add("@IDINT", OleDbType.Integer, 4, "IDINT")
                .Add("@ORDER", OleDbType.Integer, 4, "ORDER")
                .Add("@NUM", OleDbType.VarChar, 50, "NUM")
                .Add("@DESC", OleDbType.LongVarChar, 5000, "DESC")
                .Add("@SAMPLES", OleDbType.VarChar, 50, "SAMPLES")
                .Add("@SAMPLEST", OleDbType.VarChar, 50, "SAMPLEST")
            End With
            'Create update command
            Dim update As New OleDbCommand($"UPDATE {INV} SET [IDINT] = @IDINT, [ORDER] = @ORDER, [NUM] = @NUM, [DESC] = @DESC, [SAMPLES] = @SAMPLES, [SAMPLEST] = @SAMPLEST WHERE ID = @ID", connection)
            With update.Parameters
                .Add("@IDINT", OleDbType.Integer, 4, "IDINT")
                .Add("@ORDER", OleDbType.Integer, 4, "ORDER")
                .Add("@NUM", OleDbType.VarChar, 50, "NUM")
                .Add("@DESC", OleDbType.LongVarChar, 5000, "DESC")
                .Add("@SAMPLES", OleDbType.VarChar, 50, "SAMPLES")
                .Add("@SAMPLEST", OleDbType.VarChar, 50, "SAMPLEST")
                .Add("@ID", OleDbType.Integer, 4, "ID")
            End With
            'Create delete command
            Dim delete As New OleDbCommand($"DELETE FROM {INV} WHERE ID = @ID", connection)
            delete.Parameters.Add("@ID", OleDbType.Integer, 4, "ID")
            With INVENTORYTableAdapter
                .MissingSchemaAction = MissingSchemaAction.AddWithKey
                .InsertCommand = insert
                .UpdateCommand = update
                .DeleteCommand = delete
            End With
        End Sub
    
        Private Sub LoadData()
            INVENTORYTableAdapter.Fill(INVENTORYDataTable)
            INVENTORYBindingSource.DataSource = INVENTORYDataTable
            dgvInventory.DataSource = INVENTORYBindingSource
        End Sub
    
        Private Sub frmInventory_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ConfigureDataAdapter()
            LoadData()
        End Sub
    And this is where I save the data:

    Code:
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            'Save
            INVENTORYBindingSource.EndEdit()
            INVENTORYTableAdapter.Update(INVENTORYDataTable)
        End Sub
    I guess there's a problem with the ID which is autoincrement and generated when the data are saved to the database...

    Thanks

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Concurrency violation: the DeleteCommand affected 0 of the expected 1

    If your primary key is an AutoNumber then that means that it is generated by the database when you insert the record. When you save new records, do you retrieve that auto-generated value from the database into your DataTable? If not, how can you then delete or update a record identified by that value? When you're using auto-generated PKs, you need to retrieve them when they are created if you expect to be able to use them. Follow the CodeBank link in my signature and you'll find a thread dedicated to the subject of doing so.

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2020
    Posts
    11

    Re: Concurrency violation: the DeleteCommand affected 0 of the expected 1

    Code:
    Private Sub INVENTORYTableAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs) Handles INVENTORYTableAdapter.RowUpdated
            If e.StatementType = StatementType.Insert Then
                Using command As New OleDbCommand("SELECT @@IDENTITY", connection)
                    e.Row("ID") = CInt(command.ExecuteScalar())
                End Using
            End If
        End Sub
    Works perfect, thank you so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width