Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

Vayse

 

2/20/2008

违反并发性: UpdateCommand 影响了预期 1 条记录中的 0 条

新MIS系统采用了tableadapter和bindingsource，但在更新数据时会出现

违反并发性: UpdateCommand 影响了预期 1 条记录中的 0 条

的错误提示，搜索无数解决方案，都是你抄我我抄你，没有一个可以解决问题的。

 

没办法翻译成英文

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

很快找到了一个解决方法，目前还不知道是不是会有隐患。

http://www.thescripts.com/forum/thread433337.html

 

套用长江七号里星爷经常用的开场白：“我们虽然很穷。。。但我们可以借用老外的东西！”不过国内外编程水平的差异可见一斑，国人需要继续努力。

Posts: n/a

#1: Dec 8 '05

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

Get more answers. Share this question on:

Twitter Facebook StumbleUpon Digg

 

Bart Mermuys

 

Posts: n/a

#2: Dec 8 '05

re: Concurrency violation

---

Hi,

"Vayse" <[e-mail]> wrote in message
news:[e-mail]...[color=blue]
> 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?[/color]

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

[color=blue]
> Thanks
> Vayse
>[/color]

Vayse

 

Posts: n/a

#3: Dec 12 '05

re: Concurrency violation

---

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" <[e-mail]> wrote in message
news:u9UvLRC$[e-mail]...[color=blue]
> Hi,
>
> "Vayse" <[e-mail]> wrote in message
> news:[e-mail]...[/color]

Bart Mermuys

 

Posts: n/a

#4: Dec 12 '05

re: Concurrency violation

---

Hi,

"Vayse" <[e-mail]> wrote in message
news:OiRsJF0$[e-mail]...[color=blue]
> 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.[/color]

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 ?
[color=blue]
> The update command is large, is there a way for me to write a shorter
> version?[/color]

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.
[color=blue]
> I got the command via the partial class you sent. Is AssetSet.Designer.vb
> the correct location for me to add the partial class?[/color]

Don't think you're supposed to put it there, any other file will do.

HTH,
Greetings

[color=blue]
>
> 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" <[e-mail]> wrote in message
> news:u9UvLRC$[e-mail]...[color=green]
>> Hi,
>>
>> "Vayse" <[e-mail]> wrote in message
>> news:[e-mail]...[/color]
>
>[/color]

Vayse

 

Posts: n/a

#5: Dec 13 '05

re: Concurrency violation

---

"Bart Mermuys" <[e-mail]> wrote in message
news:%23gTLg60$[e-mail]...[color=blue]
> Hi,
>
> "Vayse" <[e-mail]> wrote in message
> news:OiRsJF0$[e-mail]...[color=green]
>> 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.[/color]
>
> 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 ?
>[/color]

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

 

Posts: n/a

#6: Dec 28 '05

re: Concurrency violation

---

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

 

转自:http://bytes.com/topic/visual-basic-net/answers/433337-concurrency-violation