Results 1 to 13 of 13

Thread: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jun 2010
    Posts
    177

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

    Hi, I am using a DataGridView to display data from a Microsoft Access file. Everything seems to be working fine. I can INSERT and DELETE rows without any problems. Now, when I try to UPDATE a row sometimes it just won't work or I would get the "Concurrency violation" error. This is the code that I'm using,

    Code:
    Private Sub btnTestingUpdate_Click(sender As Object, e As EventArgs) Handles btnTestingUpdate.Click
    
            dgvGamesList.CurrentRow.Cells(2).Value() = "TESTING ONLY"
    
            Dim update As New OleDbCommand("UPDATE GameDatabase SET White = @White, Elo_W = @Elo_W, Black = @Black WHERE ID = @ID",_                MyConn)
      
            update.Parameters.Add("@White", OleDbType.VarChar, 100, "White")
            update.Parameters.Add("@Elo_W", OleDbType.Integer, 10, "Elo_W")
            update.Parameters.Add("@Black", OleDbType.VarChar, 100, "Black")
            update.Parameters.Add("@ID", OleDbType.Integer, 4, "ID")
    
            da.UpdateCommand = update
    
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
    
    
    End Sub
    The line of code dgvGamesList.CurrentRow.Cells(2).Value() = "TESTING ONLY" works fine. The value in the specified cell gets updated with the new value in the DataGridView. However, the Microsoft Access is not getting updated with the new cell info. Any help is greatly appreciated. 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 records.

    The error message is referring to a DeleteCommand so you are clearly doing something funky that you aren't showing us. Saving data is actually quite simple but so many people complicate it and end up making a mess of it as a result. For one thing, you shouldn't really be creating a data adapter when it comes time to save. You apparently already have a data adapter because you're not creating one in that code so you should already have the UpdateCommand and the DeleteCommand ready to go. Configure the ENTIRE data adapter in one go, not in bits and pieces. Create it first, call Fill to populate a DataTable, bind that to a DataGridView, edit the data in the grid, then save. If you do that then it will work. Don't get fancy for no reason. Here's an example of what your code should look like, based on what I can see of your table:
    vb.net Code:
    1. Imports System.Data.OleDb
    2.  
    3. Public Class Form1
    4.  
    5.     Private data As New DataSet
    6.     Private adapter As OleDbDataAdapter
    7.  
    8.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    9.         ConfigureDataAdapter()
    10.         LoadData()
    11.     End Sub
    12.  
    13.     Private Sub ConfigureDataAdapter()
    14.         Dim connection As New OleDbConnection("connection string here")
    15.  
    16.         adapter = New OleDbDataAdapter("SELECT ID, White, Elo_W, Black FROM GameDatabase", connection)
    17.  
    18.         Dim insert As New OleDbCommand("INSERT INTO GameDatabase (White, Elo_W, Black) VALUES (@White, @Elo_W, @Black)", connection)
    19.  
    20.         With insert.Parameters
    21.             .Add("@White", OleDbType.VarChar, 100, "White")
    22.             .Add("@Elo_W", OleDbType.Integer, 0, "Elo_W")
    23.             .Add("@Black", OleDbType.VarChar, 100, "Black")
    24.         End With
    25.  
    26.         Dim update As New OleDbCommand("UPDATE GameDatabase SET White = @White, Elo_W = @Elo_W, Black = @Black WHERE ID = @ID", connection)
    27.  
    28.         With update.Parameters
    29.             .Add("@White", OleDbType.VarChar, 100, "White")
    30.             .Add("@Elo_W", OleDbType.Integer, 4, "Elo_W")
    31.             .Add("@Black", OleDbType.VarChar, 100, "Black")
    32.             .Add("@ID", OleDbType.Integer, 4, "ID")
    33.         End With
    34.  
    35.         Dim delete As New OleDbCommand("DELETE GameDatabase WHERE ID = @ID", connection)
    36.  
    37.         delete.Parameters.Add("@ID", OleDbType.Integer, 4, "ID")
    38.  
    39.         With adapter
    40.             .MissingSchemaAction = MissingSchemaAction.AddWithKey
    41.             .InsertCommand = insert
    42.             .UpdateCommand = update
    43.             .DeleteCommand = delete
    44.         End With
    45.     End Sub
    46.  
    47.     Private Sub LoadData()
    48.         adapter.Fill(data, "GameDatabase")
    49.  
    50.         BindingSource1.DataMember = "GameDatabase"
    51.         BindingSource1.DataSource = data
    52.  
    53.         DataGridView1.DataSource = BindingSource1
    54.     End Sub
    55.  
    56.     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    57.         BindingSource1.EndEdit()
    58.         adapter.Update(data, "GameDatabase")
    59.     End Sub
    60.  
    61. End Class
    If you do that and edit your data in the grid before clicking the Button then you'll be good to go. There will be no concurrency violations.

    Concurrency violations are supposed to be a way to prevent changes by multiple users interfering with each other. ADO.NET is able to check whether the data currently in the database matches what you originally retrieved and, if it doesn't, then another user must have made changes in the interim. The concurrency violation enables you to merge those changes with those by the current user rather than simply overwriting them. If you're getting a concurrency violation without multiple users interleaving changes then it's because you're messing something up but it's not always clear what you're doing wrong because it's often elsewhere in the code. For instance, one reason can be that people call AcceptChanges when they shouldn't or they mess up their SQL somehow.

    I have a couple of other notes on your code. Firstly, GameDatabase is a terrible name in that context. For one thing, it's a table and not a database. Many databases have 10, 20, 100 or more tables in them. Can you imagine using names like that then? If what you're storing in that table is records representing games then your table should be named either Game or Games, depending on whether you choose to use singular or plural names. Personally, I use singular because I tend to think of a table as a template rather than a collection. Many people use plural though. Whichever you choose, BE CONSISTENT.

    Also, you should prefer using a DataTable over a DataSet unless using a DataSet provides some advantage. A DataSet is basically a container for DataTables anyway so you can't not use a DataTable. Unless you're using multiple DataTables and generally also DataRelations between them, using a DataSet is pointless. It adds overhead for no good reason. Fill and Update methods of a data adapter accept a DataTable and you can bind a DataTable directly without specifying a DataMember.

    Also note that the BindingSource used in my code would be added in the designer. You don't have to use a BindingSource but I always recommend it. It centralises many binding-related functions and makes many of them easier.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jun 2010
    Posts
    177

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

    Hmmm, thanks for your help. You threw a lot of information at me there . Okay, next is the code that I have. I changed the code for the Button1, but that didn't work either. Okay, go easy on me. I'm a rookie . Thanks.

    Code:
    Imports System.Data.OleDb
    
    Friend Class MainForm
    
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\DatabaseGames.accdb"
        Dim MyConn As OleDbConnection
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim tables As DataTableCollection
        Dim source1 As New BindingSource
        Dim table As New DataTable
    
    Private Sub MainForm_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
    
            MyConn = New OleDbConnection
            MyConn.ConnectionString = connString
            ds = New DataSet
            tables = ds.Tables
            da = New OleDbDataAdapter("Select * from [GameDatabase]", MyConn)
    
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey
            da.Fill(ds, "GameDatabase")
    
            Dim builder As New OleDbCommandBuilder(da)
            Dim view As New DataView(tables(0))
    
            dgvGamesList.DataMember = "GameDatabase"    'TESTING
            source1.DataSource = view
            dgvGamesList.DataSource = view
    
            '#### ADD CHECKBOX COLUMN ####
            Dim chk As New DataGridViewCheckBoxColumn()
            chk.HeaderText = "Delete"
            chk.Name = "chk"
            chk.Width = 40
            chk.[ReadOnly] = False
            'dgvGamesList.RowTemplate.Height = 50
            'chk.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
            chk.TrueValue = True
            chk.FalseValue = False
            dgvGamesList.Columns.Insert(1, chk)
            dgvGamesList.Columns(1).ReadOnly = False
    
            Dim r As DataRow
            r = ds.Tables(0).NewRow
            r.Item("White") = "TESTING 1"
            r.Item("Elo_W") = "2150"
            r.Item("Black") = "TESTING 2"
    
            ds.Tables(0).Rows.Add(r)  'TESTING ONLY
    
            Dim insert As New OleDbCommand("INSERT INTO GameDatabase (White, Elo_W, Black) VALUES (@White, @Elo_W, @Black)", MyConn)
    
            insert.Parameters.Add("@White", OleDbType.VarChar, 40, "White")
            insert.Parameters.Add("@Elo_W", OleDbType.VarChar, 10, "Elo_W")
            insert.Parameters.Add("@Black", OleDbType.VarChar, 40, "Black")
    
            da.InsertCommand = insert
    
    
            'Dim update As New OleDbCommand("UPDATE GameDatabase SET White = @White, Elo_W = @Elo_W, Black = @Black WHERE ID = @ID",_ MyConn)
    
            update.Parameters.Add("@White", OleDbType.VarChar, 100, "White")
            update.Parameters.Add("@Elo_W", OleDbType.Integer, 10, "Elo_W")
            update.Parameters.Add("@Black", OleDbType.VarChar, 100, "Black")
            update.Parameters.Add("@ID", OleDbType.Integer, 4, "ID")
    
            da.UpdateCommand = update
    
            da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
    End Sub
    
    
    Private Sub UpdateLinesGames()
           
           Dim dgvRow As New DataGridViewRow
           Dim delete As New OleDbCommand("DELETE FROM GameDatabase WHERE ID = @ID", MyConn)
           Dim RowNumber As Integer
           Dim RowCount As Integer = dgvGamesList.Rows.Count - 1
           
           'FIRST DELETE CURRENT ROW BEFORE INSERTING THE UPDATED ONE. ROW dgvCurrentRowId IS DELETED.
           For RowNumber = RowCount To 0 Step -1
                If dgvCurrentRowId = dgvGamesList.Rows(RowNumber).Cells(0).Value() Then    'SELECT ROW ID.
                    dgvGamesList.Rows.Remove(dgvGamesList.Rows(RowNumber))
                    delete.Parameters.Add("@ID", OleDbType.Integer, 4, "ID")
                    da.DeleteCommand = delete
                    da.MissingSchemaAction = MissingSchemaAction.AddWithKey
                    da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
                End If
            Next
    
            'NOW INSERT THE NEW LINE WITH THE UPDATED DATA.
            Dim r As DataRow
            r = ds.Tables(0).NewRow
    
    
            r.Item("White") = "TESTING 4"
            'r.Item("White") = lblSGWhiteName.Text   'NEW DATA
            r.Item("Elo_W") = "2325"
    
            ds.Tables(0).Rows.Add(r)
    
            Dim insert As New OleDbCommand("INSERT INTO GameDatabase (White, Elo_W, Black) VALUES (@White, @Elo_W, @Black)", MyConn)
    
            insert.Parameters.Add("@White", OleDbType.VarChar, 40, "White")
            insert.Parameters.Add("@Elo_W", OleDbType.VarChar, 10, "Elo_W")
            insert.Parameters.Add("@Black", OleDbType.VarChar, 40, "Black")
    
            da.InsertCommand = insert
    
            da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
    End Sub
    
    
    Private Sub InsertNewLinesGames()
    
            Dim r As DataRow
            r = ds.Tables(0).NewRow
            r.Item("White") = "TESTING 4"
            r.Item("Elo_W") = "2325"
            r.Item("Black") = "TESTING 2"
            ds.Tables(0).Rows.Add(r)
    
            Dim insert As New OleDbCommand("INSERT INTO GameDatabase (White, Elo_W, Black) VALUES (@White, @Elo_W, @Black)", MyConn)
    
            insert.Parameters.Add("@White", OleDbType.VarChar, 40, "White")
            insert.Parameters.Add("@Elo_W", OleDbType.VarChar, 10, "Elo_W")
            insert.Parameters.Add("@Black", OleDbType.VarChar, 40, "Black")
    
            da.InsertCommand = insert
            da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
    
    End Sub
    
    
    Private Sub btnDeleteGames_Click(sender As Object, e As EventArgs) Handles btnDeleteGames.Click
            Dim dgvRow As New DataGridViewRow
            Dim delete As New OleDbCommand("DELETE FROM GameDatabase WHERE ID = @ID", MyConn)
            Dim RowNumber As Integer
            Dim RowCount As Integer = dgvGamesList.Rows.Count - 1
            Dim dgvRowId As Integer
    
            'DELETE ROWS THAT WERE CHECKED
            Try
                For RowNumber = RowCount To 0 Step -1
                    If dgvGamesList.Rows(RowNumber).Cells(1).Value = True Then
                        dgvRowId = dgvGamesList.Rows(RowNumber).Cells(0).Value()    'SELECT ROW ID.
                        dgvGamesList.Rows.Remove(dgvGamesList.Rows(RowNumber))
    
                        delete.Parameters.Add("@ID", OleDbType.Integer, 4, "ID")
                        da.DeleteCommand = delete
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey
                        da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
                    End If
                    'dgvGamesList.Rows(RowNumber).Cells(1).Value = True     'SETS ALL CHECKBOXED TO CHECKED STATUS
                Next
            Catch ex As Exception
    
            End Try
    End Sub
    
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            BindingSource1.EndEdit()
            adapter.Update(data, "GameDatabase")
    End Sub
    
    
    End Class

  4. #4
    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 records.

    The first thing I said is to configure your data adapter in one go at the outset. I don't see where you've done that. I also said that the BindingSource provides centralised access to binding-related functionality. If you want to delete a record then you do it through the BindingSource.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Jun 2010
    Posts
    177

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

    Hmm, it actually works the second time I push the button, but is only works for the previous change not for the current change. Weird! Well, at least I'm getting an update. I'm using this code,

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            dgvGamesList.CurrentRow.Cells(2).Value() = answer
            BindingSource1.EndEdit()
            adapter.Update(data, "GameDatabase")
    End Sub
    It updates the Microsoft Access file the second time that I hit the button.

  6. #6
    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 records.

    Use the BindingSource. That's what it's there for. If you want to add, edit for delete a record, do it through the BindingSource. If you have an unbound column of check boxes then you have no choice but to access those values via the grid but that needs only to be to get the index of a record. Take the index and then go to the BindingSource to use it.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jun 2010
    Posts
    177

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

    Great, it works!!!! I made the following code to work using the BindingSource like you told me. It's actually very easy and people make it hard like you said. The following code is working now,

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            dgvGamesList.CurrentRow.Cells(2).Value() = "TESTING ONLY"
            BindingSource1.EndEdit()
            adapter.Update(data, "GameDatabase")
    End Sub
    Now, the next code that used to work is not working anymore. I have no idea what might be the problem. The following code deletes all the rows where the CheckBox is checked.

    Code:
        Private Sub btnDeleteGames_Click(sender As Object, e As EventArgs) Handles btnDeleteGames.Click
            Dim dgvRow As New DataGridViewRow
            Dim RowNumber As Integer
            Dim RowCount As Integer = dgvGamesList.Rows.Count - 1
            Dim dgvRowId As Integer
    
            Try
                For RowNumber = RowCount To 0 Step -1
                    If dgvGamesList.Rows(RowNumber).Cells(1).Value = True Then       'CHECK IF THE CHECKBOX IS SELECTED
                        dgvRowId = dgvGamesList.Rows(RowNumber).Cells(0).Value()    'SELECT ROW ID.
                        dgvGamesList.Rows.Remove(dgvGamesList.Rows(RowNumber))
    
                        BindingSource1.EndEdit()
                        da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
                    End If
                Next
        End Sub
    " If you have an unbound column of check boxes then you have no choice but to access those values via the grid but that needs only to be to get the index of a record. Take the index and then go to the BindingSource to use it." This might be the reason why it's not working. Can you give me a clue how to do this?

    Thanks,
    Last edited by VB-MCU-User; Dec 28th, 2017 at 05:45 PM.

  8. #8
    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 records.

    This is not using the BindingSource:
    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            dgvGamesList.CurrentRow.Cells(2).Value() = "TESTING ONLY"
            BindingSource1.EndEdit()
            adapter.Update(data, "GameDatabase")
    End Sub
    The grid is for interaction with the user. If you want to manipulate bound data in code then use the BindingSource.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jun 2010
    Posts
    177

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

    Quote Originally Posted by jmcilhinney View Post
    This is not using the BindingSource:
    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            dgvGamesList.CurrentRow.Cells(2).Value() = "TESTING ONLY"
            BindingSource1.EndEdit()
            adapter.Update(data, "GameDatabase")
    End Sub
    The grid is for interaction with the user. If you want to manipulate bound data in code then use the BindingSource.
    Any clues to take me out of my misery? I have checked on a few BindingSource tutorials, but I don't know what you are referring to here.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Jun 2010
    Posts
    177

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

    Problem resolved! Before using the BindingSource, the following code worked fine. It deletes the rows with the checkbox checked.

    Code:
        Private Sub btnDeleteGames_Click(sender As Object, e As EventArgs) Handles btnDeleteGames.Click
            Dim RowNumber As Integer
            Dim RowCount As Integer = dgvGamesList.Rows.Count - 1
    
                For RowNumber = RowCount To 0 Step -1
                    If dgvGamesList.Rows(RowNumber).Cells(1).Value = True Then       'CHECK IF THE CHECKBOX IS SELECTED
                        dgvGamesList.Rows.Remove(dgvGamesList.Rows(RowNumber))
    
                        BindingSource1.EndEdit()
                        da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
                    End If
                Next
        End Sub
    The checkbox was inserted in the code as column 1. For some reason, after using the BindingSource the column number for the checkbox became 0. The following code works fine,

    Code:
        Private Sub btnDeleteGames_Click(sender As Object, e As EventArgs) Handles btnDeleteGames.Click
            Dim RowNumber As Integer
            Dim RowCount As Integer = dgvGamesList.Rows.Count - 1
    
                For RowNumber = RowCount To 0 Step -1
                    If dgvGamesList.Rows(RowNumber).Cells(0).Value = True Then       'CHECK IF THE CHECKBOX IS SELECTED
                        dgvGamesList.Rows.Remove(dgvGamesList.Rows(RowNumber))
    
                        BindingSource1.EndEdit()
                        da.Update(ds, "GameDatabase")   'SAVE THE CHANGES
                    End If
                Next
        End Sub

  11. #11
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

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

    Your code is going to make a connection to your db every time something is deleted. This is counterproductive. JMC is trying to send you down the right path with binding. If anything else you need to get the update call out of that loop.
    Code:
        Dim dset As New DataSet With {.DataSetName = "dset"}
        Dim bs As New BindingSource
    
        Private Sub BindingExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dset.Tables.Add(New DataTable With {.TableName = "YourTable"})
    
            With dset.Tables("YourTable")
                'create columns
                .Columns.Add("Select", GetType(Boolean))
                .Columns.Add("Col1", GetType(String))
                .Columns.Add("Col2", GetType(String))
                .Columns.Add("Col3", GetType(String))
                'create 100 rows
                For i As Integer = 0 To 100
                    .Rows.Add(False, "Col1Value" & i, "Col2Value" & i, "Col3_LOOOOOOOOOONG_Value" & i)
                Next i
            End With
    
            bs.DataSource = dset.Tables("YourTable")
    
            DataGridView1.DataSource = bs
    
        End Sub
    
        Private Sub Btn_RemoveSelected_Click(sender As Object, e As EventArgs) Handles Btn_RemoveSelected.Click
            For Each DRV As DataRowView In bs
                If Convert.ToBoolean(DRV("Select")) Then
                    bs.Remove(DRV)
                End If
            Next
            bs.EndEdit()
            'Now call you update
        End Sub

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jun 2010
    Posts
    177

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

    Kpmc, ooh I see. I'll play with it. I understand JM's point, but I was looking for some examples like the one you just posted. I haven't been able to find a good tutorial about the BindingSouce.

  13. #13
    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 records.

    Quote Originally Posted by VB-MCU-User View Post
    I haven't been able to find a good tutorial about the BindingSouce.
    Have you read the documentation for the BindingSource class? Tutorials are one tool for learning. They are not the only one. Even if you do/can find a tutorial, you should still read the documentation anyway.

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