-
Oct 26th, 2020, 07:09 AM
#1
Thread Starter
New Member
[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
-
Oct 26th, 2020, 08:28 AM
#2
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.
-
Oct 26th, 2020, 09:29 AM
#3
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|