OleDBConcurrency exception

[AndyLD]

Hi!

I use this code to copy a row in a DataTable bound by a BindingSource to a DataGridView:

Dim oldRowCopy As DataRow
'Create new row
oldRowCopy = myDataTable.NewRow()
'copy values
oldRowCopy.ItemArray = myDataTable.Rows(indexOfTheRowIWantToCopy).ItemArray
'mark the original row as copied
myDataTable.Rows(newMainGrid2.CurrentRow.Index).Item("wasCopied") = True
myDataTable.Rows(newMainGrid2.CurrentRow.Index).Item("record_state") = "UPDATED"
'Change some values in the copy of the original row
oldRowCopy("id") = DBNull.Value
oldRowCopy("date_time") = Now()
oldRowCopy("record_state") = "NEW"
myDataTable.Rows.Add(oldRowCopy)

The DataTable gets filled and updated by an OleDbDataAdapter, where the Insert, Update and Delete Commands are set by an OleDbCommandBuilder.
Then later, when the user has finished editing the copied row, I call

myDataAdapter.Update(myDataTable)

, to save the changes to the database.
Now, this row copying throws an OleDb Concurrency Exception saying that 0 rows affected.
I wonder, if this approach of copying the row is wrong.

[jmcilhinney]

This bot doesn't make sense to me:

'copy values
oldRowCopy.ItemArray = myDataTable.Rows(indexOfTheRowIWantToCopy).ItemArray
'mark the original row as copied
myDataTable.Rows(newMainGrid2.CurrentRow.Index).Item("wasCopied") = True
myDataTable.Rows(newMainGrid2.CurrentRow.Index).Item("record_state") = "UPDATED"

You seem to be referring to the same row in two different ways. If you're referring to the current row in the grid then there's only one way you should be referring to it and that's via the Current property of the BindingSource:

Dim oldRow = DirectCast(myBindingSource.Current, DataRowView).Row
Dim newRow = myDataTable.NewRow()

I'd make that change first and then see if you still get the same issue. I suspect that you will but let's see.

[AndyLD]

Thank you very much for the suggestion.
I changed that, but the exception still is thrown. I wonder, if this line newRow.ItemArray = oldRow.ItemArray copies the values, or gives the array "pointer" from the old to the new row.
By the way, the copied row gets inserted into the database but without any changes made in the DGV

Dim oldRow As DataRow = DirectCast(myGridBindingSource.Current, DataRowView).Row
Dim newRow As DataRow = myDataTable.NewRow()
newRow.ItemArray = oldRow.ItemArray
oldRow.Item("was_copied") = True
oldRow.Item("record_state") = "UPDATED"
newRow("id") = DBNull.Value
newRow("date_time") = Now()
newRow("record_state") = "NEW"
myDataTable.Rows.Add(newRow)
myDataAdapter.Update(myDataTable)

This makes me think: "if you're referring to the current row in the grid then there's only one way you should be referring to it ", do you mean this only for the current row, or should I access the datatable shown in the DGV only throught the bindingsource in general? This would make a lot more work, as there doesn't seem to be an easy way to access the DataTable throught the bindingsource, while I can access it easily throught columnindex and rowindex.

[jmcilhinney]

Yes, you should generally access the bound data only via the BindingSource, which is pretty much the point of a BindingSource: a one-stop-shop for working with the bound data. It's not more work at all. You can index the BindingSource itself to get a row in the form of a DataRowView and, for most things, you can treat that DataRowView in the same way as you can the corresponding DataRow. For those situations where you need a DataRow, you simply get it from the Row property of the DataRowView.

One situation where you must use the BindingSource is when the data in the grid is sorted or filtered. In such cases, the data in the grid is not in sync with the data in the DataTable so it's not safe to use row indexes from the grid to get a row from the table as you may end up referring to a completely different row.

I still can't see any reason for a concurrency violation in the code you're showing us. The use of ItemArray should not be a problem as it's not a "live" property, i.e. when you get the property value a new array object is created and the row's field values copied into it while when you set the property the array elements are copied into the row fields and the array discarded. Is that all the editing your doing? I would suggest examining the contents of the DataTable before you call Update. Examine the RowState of each DataRow. A concurrency violation occurs when the RowState is Modified or Deleted and the original values in the DataRow are not the same as those in the database for the corresponding record. Maybe your UpdatedCommand is broken and that's what's causing the issue. Can you show us the code that creates it, or are you using a command builder?

[AndyLD]

Thank you very much for explaining this to me. I guess, then I will first of all change all access to the data to access through the bindingsource and then see if this concurrency exception arises. While every other tested scenario in my application works without a problem, the case where the row is copied throws this exception, but maybe this originates somewhere else where data is being accessed not through the bindingsource, just the problem shows up only in this row copying case.

Yes, I use the commandbuilder for the adapter commands, as the DGV shows data only from one table.


Edit:
The problem, as I see it, was that I called the Update method right after I added the new row copy to the datatable. This called the InsertCommand of the dataadapter, and the row was inserted in the database. Then I let the user make changes to the row in the datagridview. However, using this approach the row in the datagridview does not contain the id field of the row from the database, so after the user finished editing this record, I called again the update method of the adapter and the adapter probably looks at the datatable bound to the datagridview, sees that the row has no ID, so it asumes it's a new row and not an edited one, so it tries to insert the row, but then again sees that it has been inserted already. Maybe this all is just the product of my fantasy, something completely other happens. My solution - (I'm sorry for not implementing everything correctly) - after I insert the row to the database, I clear the table and refill it through the adapter, which makes the adapter to read the data and get the new row with it's id from the database.