-
Dec 26th, 2017, 08:16 PM
#1
Thread Starter
Addicted Member
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.
-
Dec 26th, 2017, 09:08 PM
#2
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:
Imports System.Data.OleDb Public Class Form1 Private data As New DataSet Private adapter As OleDbDataAdapter Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load ConfigureDataAdapter() LoadData() End Sub Private Sub ConfigureDataAdapter() Dim connection As New OleDbConnection("connection string here") adapter = New OleDbDataAdapter("SELECT ID, White, Elo_W, Black FROM GameDatabase", connection) Dim insert As New OleDbCommand("INSERT INTO GameDatabase (White, Elo_W, Black) VALUES (@White, @Elo_W, @Black)", connection) With insert.Parameters .Add("@White", OleDbType.VarChar, 100, "White") .Add("@Elo_W", OleDbType.Integer, 0, "Elo_W") .Add("@Black", OleDbType.VarChar, 100, "Black") End With Dim update As New OleDbCommand("UPDATE GameDatabase SET White = @White, Elo_W = @Elo_W, Black = @Black WHERE ID = @ID", connection) With update.Parameters .Add("@White", OleDbType.VarChar, 100, "White") .Add("@Elo_W", OleDbType.Integer, 4, "Elo_W") .Add("@Black", OleDbType.VarChar, 100, "Black") .Add("@ID", OleDbType.Integer, 4, "ID") End With Dim delete As New OleDbCommand("DELETE GameDatabase WHERE ID = @ID", connection) delete.Parameters.Add("@ID", OleDbType.Integer, 4, "ID") With adapter .MissingSchemaAction = MissingSchemaAction.AddWithKey .InsertCommand = insert .UpdateCommand = update .DeleteCommand = delete End With End Sub Private Sub LoadData() adapter.Fill(data, "GameDatabase") BindingSource1.DataMember = "GameDatabase" BindingSource1.DataSource = data DataGridView1.DataSource = BindingSource1 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
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.
-
Dec 26th, 2017, 10:03 PM
#3
Thread Starter
Addicted Member
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
-
Dec 26th, 2017, 11:23 PM
#4
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.
-
Dec 26th, 2017, 11:30 PM
#5
Thread Starter
Addicted Member
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.
-
Dec 27th, 2017, 02:44 AM
#6
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.
-
Dec 28th, 2017, 05:36 PM
#7
Thread Starter
Addicted Member
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.
-
Dec 28th, 2017, 05:46 PM
#8
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.
-
Dec 28th, 2017, 05:57 PM
#9
Thread Starter
Addicted Member
Re: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.
Originally Posted by jmcilhinney
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.
-
Dec 29th, 2017, 01:08 AM
#10
Thread Starter
Addicted Member
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
-
Dec 29th, 2017, 09:06 AM
#11
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
-
Dec 29th, 2017, 11:38 AM
#12
Thread Starter
Addicted Member
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.
-
Dec 29th, 2017, 10:47 PM
#13
Re: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.
Originally Posted by VB-MCU-User
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|