Yep, it helps explain what I say next..
DB Concurrency exceptions occur because this huge where clause causes 0 records to be found:
an update really only needs the primary key to work.. UPDATE table SET field = value WHERE primary_key = some_pk
But all that extra junk in the where clause is checking that all the values in the table are the same as when the data was downloaded
Suppose you download:
Select name, age, address From Person Where Name = 'john'
You get:
john, 23, NULL
Now suppose somewhere else in code or in another app on another machine, age is updated to 24
Your query, with all the junk in the where clause will be:
UPDATE person SET age = 25 WHERE
name = 'John' and
((age is null and 0 = 1) or age = 23) and
((address is null and 1 = 1) or (address = null)
let us explain why this complex IS NULL part comes:
((field IS NULL and ? = 1) or field = ?)
In this, the first ? parameter is set to the result of "If ORIGINAL value is null set parameter = 1 else 0"
and the second ? parameter is set to the ORIGINAL value that was downloaded.
Thus if you originally downloaded a null (which cannot be compared with =, hence the "if" part) this part of the clause evaluates to TRUE, and we have checked that a field that was null when it was downloaded is still null now
For fields that had a value when we downlaoded them, it is the other part that checks they are still the same value.. the field = ? part
Consider our person example again:
((age is null and 0 = 1) or age = 23) and
This is the part of the where clause that is FALSE, and causes 0 records to update. Age has a value.. it was 23 when we downlaoded it.. Someone else changed it to 24, and now we're falling over at the "or age = 23" part..
Thats why you get the concurrency exception.. you become aware that the data has changed since you downloaded it as a result of this excpetion.. You would then ordinarily solve it by presenting the user witha choice: keep theirs, overwrite theirs, attempt to merge..
The curious thing for you is why it's failing on your second save and you seem to assert that noone else (not your code, not a trigger, stored procedure, automatic anything) is changing your code.. Youre going to have to find out which column is causing the update to fail..
I recommend at the point you get the fail, in the catch handler for it use your tableadapter you download a new copy of the datatables and compare them:
PrivateSub BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorSaveItem.Click
Me.Validate()
Me.EvaluationsBindingSource.EndEdit()
try
Me.EvaluationsTableAdapter.Update(QA_DSEvaluation.Ev aluations)
catch DbConcurrencyException
dim dt as evaluationsdatatable = EvaluationsTableAdapter.GetData() 'or getdataby... if there are parameters to the select query
'lets see which original row value is mismatched
for each oldro as EvaluationsDataRow in QA_DSEvaluation.Evaluations
Dim nuro as EvaluationsDataRow = dt.FindByIDEVAL(oldro.IdEval)
For Each dc as DataColumn in dt.Columns
if oldro.Item(dc, DataRowVersion.Original) <> nuro.Item(dc) Then
MsgBox(dc.ColumnName & " value differs on row with PK " & nuro.IdEval)
End If
Next dc
End try
EndSub
The IF in my code might need a bit of work to cope with null values, but it's a start. Note it has been written without VB.NET so there may be minor syntax error or typos in names of classes, enums etc.. it's a pseudocode presentation of an idea only.. good luck!
