Concurrency violation

[Vayse]

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.Asse ts) '
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?
Thanks
Vayse

[Bart Mermuys]

Hi,

"Vayse" <va***@nospam.nospam> wrote in message
news:Ok*****************@tk2msftngp13.phx.gbl...

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.Asse ts) '
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

Thanks
Vayse

[Vayse]

Thanks. This is single user system at this point, so its not that.
The user enters the AssetCode, which is the primary key, so its not that
either.
The update command is large, is there a way for me to write a shorter
version?
I got the command via the partial class you sent. Is AssetSet.Designer.vb
the correct location for me to add the partial class?

Thanks
Vayse


UPDATE `Assets` SET `AssetCode` = ?, `Desc` = ?, `SubCategoryId` = ?,
`PurchaseDate` = ?, `SuppliersCode` = ?, `PurchasePrice` = ?, `CostCentre` =
?, `Department` = ?, `Location1` = ?, `Location2` = ?, `Location3` = ?,
`DeprAmount` = ?, `DeprStart` = ?, `DeprMethod` = ?, `DeprLife` = ?,
`RemainingLife` = ?, `DeprFirstPeriod` = ?, `DeprResidual` = ?, `AccumDepr`
= ?, `HasSchedule` = ?, `StatusID` = ?, `AssetNote` = ?, `AssetImage` = ?,
`DisposalSIN` = ?, `DisposalNom` = ?, `SalesValue` = ?, `SerialNum` = ?,
`Ins_Renewal` = ?, `Ins_CompanyId` = ?, `Ins_PolicyNum` = ?, `Tax_Renewal` =
?, `NCT_Renewal` = ? WHERE ((`AssetCode` = ?) AND ((? = 1 AND `Desc` IS
NULL) OR (`Desc` = ?)) AND ((? = 1 AND `SubCategoryId` IS NULL) OR
(`SubCategoryId` = ?)) AND ((? = 1 AND `PurchaseDate` IS NULL) OR
(`PurchaseDate` = ?)) AND ((? = 1 AND `SuppliersCode` IS NULL) OR
(`SuppliersCode` = ?)) AND ((? = 1 AND `PurchasePrice` IS NULL) OR
(`PurchasePrice` = ?)) AND ((? = 1 AND `CostCentre` IS NULL) OR
(`CostCentre` = ?)) AND ((? = 1 AN
D `Department` IS NULL) OR (`Department` = ?)) AND ((? = 1 AND `Location1`
IS NULL) OR (`Location1` = ?)) AND ((? = 1 AND `Location2` IS NULL) OR
(`Location2` = ?)) AND ((? = 1 AND `Location3` IS NULL) OR (`Location3` =
?)) AND ((? = 1 AND `DeprAmount` IS NULL) OR (`DeprAmount` = ?)) AND ((? = 1
AND `DeprStart` IS NULL) OR (`DeprStart` = ?)) AND ((? = 1 AND `DeprMethod`
IS NULL) OR (`DeprMethod` = ?)) AND ((? = 1 AND `DeprLife` IS NULL) OR
(`DeprLife` = ?)) AND ((? = 1 AND `RemainingLife` IS NULL) OR
(`RemainingLife` = ?)) AND ((? = 1 AND `DeprFirstPeriod` IS NULL) OR
(`DeprFirstPeriod` = ?)) AND ((? = 1 AND `DeprResidual` IS NULL) OR
(`DeprResidual` = ?)) AND ((? = 1 AND `AccumDepr` IS NULL) OR (`AccumDepr` =
?)) AND ((? = 1 AND `HasSchedule` IS NULL) OR (`HasSchedule` = ?)) AND ((? =
1 AND `StatusID` IS NULL) OR (`StatusID` = ?)) AND ((? = 1 AND `AssetNote`
IS NULL) OR (`AssetNote` = ?)) AND ((? = 1 AND `AssetImage` IS NULL) OR
(`AssetImage` = ?)) AND ((? = 1 AND `DisposalSIN` IS NULL) OR (`DisposalSIN`
= ?)
) AND ((? = 1 AND `DisposalNom` IS NULL) OR (`DisposalNom` = ?)) AND ((? = 1
AND `SalesValue` IS NULL) OR (`SalesValue` = ?)) AND ((? = 1 AND `SerialNum`
IS NULL) OR (`SerialNum` = ?)) AND ((? = 1 AND `Ins_Renewal` IS NULL) OR
(`Ins_Renewal` = ?)) AND ((? = 1 AND `Ins_CompanyId` IS NULL) OR
(`Ins_CompanyId` = ?)) AND ((? = 1 AND `Ins_PolicyNum` IS NULL) OR
(`Ins_PolicyNum` = ?)) AND ((? = 1 AND `Tax_Renewal` IS NULL) OR
(`Tax_Renewal` = ?)) AND ((? = 1 AND `NCT_Renewal` IS NULL) OR
(`NCT_Renewal` = ?)))
"Bart Mermuys" <bm*************@hotmail.com> wrote in message
news:u9**************@tk2msftngp13.phx.gbl...

Hi,

"Vayse" <va***@nospam.nospam> wrote in message
news:Ok*****************@tk2msftngp13.phx.gbl...

[Bart Mermuys]

Hi,

"Vayse" <va***@nospam.nospam> wrote in message
news:Oi*************@TK2MSFTNGP12.phx.gbl...

Thanks. This is single user system at this point, so its not that.
The user enters the AssetCode, which is the primary key, so its not that
either.
Yeah, that's strange. Are you doing anything special; changing keys,
changing any of the generated commands manually ? You also mentioned that it
fails on the same row, what are you doing before/when it fails ?
The update command is large, is there a way for me to write a shorter
version?
You can turn off "Optimistic Concurrency" and then the WHERE clause will
only include the pk. You could try this to see if the problem goes away,
but it shouldn't be necesairy to turn it off since nobody else is changing
the record so you shouldn't get a concurrency violation in the first place.

You can change Concurrency Control if you (re)configure the TableAdapter:
Menu->Data->Show Data Sources
Right click on your AsserSetDataSet and choose "Edit DataSet with Designer".
You should visually see your DataTable and TableAdapter.
Right click the TableAdapter and choose Configure.
Click on "Advanced Options..." and uncheck "Use Optimistic Concurrency".
Finish the wizard.
I got the command via the partial class you sent. Is AssetSet.Designer.vb
the correct location for me to add the partial class?
Don't think you're supposed to put it there, any other file will do.

HTH,
Greetings


Thanks
Vayse


UPDATE `Assets` SET `AssetCode` = ?, `Desc` = ?, `SubCategoryId` = ?,
`PurchaseDate` = ?, `SuppliersCode` = ?, `PurchasePrice` = ?, `CostCentre`
= ?, `Department` = ?, `Location1` = ?, `Location2` = ?, `Location3` = ?,
`DeprAmount` = ?, `DeprStart` = ?, `DeprMethod` = ?, `DeprLife` = ?,
`RemainingLife` = ?, `DeprFirstPeriod` = ?, `DeprResidual` = ?,
`AccumDepr` = ?, `HasSchedule` = ?, `StatusID` = ?, `AssetNote` = ?,
`AssetImage` = ?, `DisposalSIN` = ?, `DisposalNom` = ?, `SalesValue` = ?,
`SerialNum` = ?, `Ins_Renewal` = ?, `Ins_CompanyId` = ?, `Ins_PolicyNum` =
?, `Tax_Renewal` = ?, `NCT_Renewal` = ? WHERE ((`AssetCode` = ?) AND ((? =
1 AND `Desc` IS NULL) OR (`Desc` = ?)) AND ((? = 1 AND `SubCategoryId` IS
NULL) OR (`SubCategoryId` = ?)) AND ((? = 1 AND `PurchaseDate` IS NULL) OR
(`PurchaseDate` = ?)) AND ((? = 1 AND `SuppliersCode` IS NULL) OR
(`SuppliersCode` = ?)) AND ((? = 1 AND `PurchasePrice` IS NULL) OR
(`PurchasePrice` = ?)) AND ((? = 1 AND `CostCentre` IS NULL) OR
(`CostCentre` = ?)) AND ((? = 1 AN
D `Department` IS NULL) OR (`Department` = ?)) AND ((? = 1 AND `Location1`
IS NULL) OR (`Location1` = ?)) AND ((? = 1 AND `Location2` IS NULL) OR
(`Location2` = ?)) AND ((? = 1 AND `Location3` IS NULL) OR (`Location3` =
?)) AND ((? = 1 AND `DeprAmount` IS NULL) OR (`DeprAmount` = ?)) AND ((? =
1 AND `DeprStart` IS NULL) OR (`DeprStart` = ?)) AND ((? = 1 AND
`DeprMethod` IS NULL) OR (`DeprMethod` = ?)) AND ((? = 1 AND `DeprLife` IS
NULL) OR (`DeprLife` = ?)) AND ((? = 1 AND `RemainingLife` IS NULL) OR
(`RemainingLife` = ?)) AND ((? = 1 AND `DeprFirstPeriod` IS NULL) OR
(`DeprFirstPeriod` = ?)) AND ((? = 1 AND `DeprResidual` IS NULL) OR
(`DeprResidual` = ?)) AND ((? = 1 AND `AccumDepr` IS NULL) OR (`AccumDepr`
= ?)) AND ((? = 1 AND `HasSchedule` IS NULL) OR (`HasSchedule` = ?)) AND
((? = 1 AND `StatusID` IS NULL) OR (`StatusID` = ?)) AND ((? = 1 AND
`AssetNote` IS NULL) OR (`AssetNote` = ?)) AND ((? = 1 AND `AssetImage` IS
NULL) OR (`AssetImage` = ?)) AND ((? = 1 AND `DisposalSIN` IS NULL) OR
(`DisposalSIN` = ?)
) AND ((? = 1 AND `DisposalNom` IS NULL) OR (`DisposalNom` = ?)) AND ((? =
1 AND `SalesValue` IS NULL) OR (`SalesValue` = ?)) AND ((? = 1 AND
`SerialNum` IS NULL) OR (`SerialNum` = ?)) AND ((? = 1 AND `Ins_Renewal`
IS NULL) OR (`Ins_Renewal` = ?)) AND ((? = 1 AND `Ins_CompanyId` IS NULL)
OR (`Ins_CompanyId` = ?)) AND ((? = 1 AND `Ins_PolicyNum` IS NULL) OR
(`Ins_PolicyNum` = ?)) AND ((? = 1 AND `Tax_Renewal` IS NULL) OR
(`Tax_Renewal` = ?)) AND ((? = 1 AND `NCT_Renewal` IS NULL) OR
(`NCT_Renewal` = ?)))
"Bart Mermuys" <bm*************@hotmail.com> wrote in message
news:u9**************@tk2msftngp13.phx.gbl...

Hi,

"Vayse" <va***@nospam.nospam> wrote in message
news:Ok*****************@tk2msftngp13.phx.gbl...

[Vayse]

"Bart Mermuys" <bm*************@hotmail.com> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl...

Hi,

"Vayse" <va***@nospam.nospam> wrote in message
news:Oi*************@TK2MSFTNGP12.phx.gbl...

Thanks. This is single user system at this point, so its not that.
The user enters the AssetCode, which is the primary key, so its not that
either.

Yeah, that's strange. Are you doing anything special; changing keys,
changing any of the generated commands manually ? You also mentioned that
it fails on the same row, what are you doing before/when it fails ?

Thanks Bart.
I've compact/repair the database, and it doesn't happen on any new records.
It now only happens on the one record now, which I can't seem to fix for
love nor money.
Going to turn off the Optimistic Concurrency anyway, and work away for now.
It may just have been a corrupt db. (or the fact that VS crashes nearly
every day.)
Its something I'll have to come back to, maybe Santa will bring me the
answer.
Thanks for your help though, I learned a lot.
Vayse

[jsdude99]

While I was searching an answer to my problem, I got really helpful
method from Bart Mermuys for detecting row change event in
TableAdapters in VS2005. As you might know they do not raise events
similar to DataAdapters. Then I though I might contribute something to
Vayse's problem.

I had the same problem before and I found out that it was due to
datetime field configuration of the dataadapter. You use
'PurchaseDate' and maybe some other date (or time) field(s) in your
design. When you configure the adapter, it defaults to 'Date'. But if
the actual date field in the database also contains time information
the adapter reads the dateand time correctly and tries to store date
only component. This raises the conflict and it thinks there is a
concurrency problem. In fact, it creates its own concurrency problem.

To resolve this issue, you can either make sure every date or datetime
field in the database contains ONLY the date information or you modify
your DataAdapter and edit the date or datetime fields in select,
update, delete, add statemets and choose 'DateTime" type instead of
'Date' type.

Hope this helps
--
jsdude99
------------------------------------------------------------------------
jsdude99's Profile: http://www.hightechtalks.com/m610
View this thread: http://www.hightechtalks.com/t2304455