Hi,
Vayse said:
In my save code, most of items save fine. But sometimes I get a
concurrency violation message.
"Concurrency violation: the UpdateCommand affected 0 of the expected 1
records."
It happens on the same records each time - once this happens, it never
saves ok again.
The immediate window also has states:
A first chance exception of type 'System.Data.DBConcurrencyException'
occurred in System.Data.dll
Me.AssetsBindingSource.EndEdit()
Me.AssetsTableAdapter.Update(Me.AssetsDataSet.Assets) '
Error occurs here
Catch ole As OleDb.OleDbException
MsgBox(ole.Message, MsgBoxStyle.Critical, "Save Asset -
ole")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Save Asset") '
Catch here
Anything in particular I should be checking for?
Concurrency Violation occurs because a Command failed, it didn't affect any
rows. Things that may cause this:
1. The Update/Delete Command can be build in a way to have optimistic
concurrency control. This means that the the Command has a long WHERE
clause where it checks all old values. A modified DataRow has both old and
new values, so it uses these old values to compare with the values in the DB
and if they are not the same this normally means someone else has modified
the values in the DB between the last Fill and the current Update and
therefore it throws a Concurrency Violation.
Be carefull not to call AcceptChanges except for one situation mentioned in
2. AcceptChanges will transfer the new values to the old ones.
AcceptChanges is implicitly called by DataAdapter.Update when each DataRow
was succesfully updated in the DB.
2. Or something is wrong with the key. The Update/Delete commands use a key
to find the right record. So if the key is wrong, it won't find any or the
wrong one.
This problem occurs mostly with table's that have auto-generated key, for
which the key needs to be retrieved after inserts.
If you are using Access, then you need to add a (partial) class that uses
the RowUpdated event to get the new key, eg:
Namespace AssetsDataSetTableAdapters
Partial Public Class AssetsTableAdapter
Private cmd As New OleDb.OleDbCommand("SELECT @@IDENTITY")
Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
If (e.Errors Is Nothing AndAlso e.StatementType = StatementType.Insert)
Then
cmd.Connection = e.Command.Connection
e.Row("yourpkcolumname") = cmd.ExecuteScalar()
e.Row.AcceptChanges()
End If
End Class
End Namespace
If neither of this helps then you can add the following (partial) class to
check what CommandText and parameters where used when a violation occurs and
together with the values in the DB you might figure out why (i'm not saying
it's going to be easy though):
Namespace AssetsDataSetTableAdapters
Partial Public Class AssetsTableAdapter
Private Sub _adapter_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
If (e.Errors IsNot Nothing) Then
Console.WriteLine("CommandText={0}", e.Command.CommandText)
For Each par As OleDb.OleDbParameter In e.Command.Parameters
Console.WriteLine(" {0}= {1}", par.ParameterName,
par.Value.ToString())
Next
Console.WriteLine()
End If
End Sub
End Class
End Namespace
HTH,
Greetings
