ConcurrencyViolation Error.... Driving me nuts!

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

This is more of an plea for help question. Before I go
into details, I am using Windows 2k, VS 2003, MySql Server
and an open source MySql .NET driver (MySqlDriverCS). (I
am aware that the fact that I am using an open source
driver could be the cause of my error).

I have checked many message boards about my error and have
found that people using Access, SQL Server, MySql, Oracle
all get this error and for similar reasons.

Error: Concurrency violation: the
DeleteCommand affected 0 records.

I receive the exception whenever I call the
DataAdapter.Update() method after marking DataTable Rows
for deletion.

My DeleteCommand.Text = "DELETE FROM mynames WHERE ID =
@ID"

I delete the Row, but doing a:

Dim key as new Integer(whatevermykeyis)
row = myTable.Rows.Find(key)
row.Delete()

Sometimes the row will be deleted from my Database,
sometimes it wont, but I still get the error no matter
what happens. After I get the exception I cannot do
anything else in my app and am forced to restart it.


What I believe could be the reason:

When add Parameters to mySqlCommands, I cannot add a size
property. Also, the datatypes do not properly match up
with MySql database types. In my database I used a
SmallInt(4) for my ID Column data type. But when I add
the parameters to my DeleteCommand, the best I can come up
with is DBType.Int16 (which is a 5 digit number).

Things I have tried:
1. Changed Database to NOT use a ID field and use a
varchar(16) field.
2. Changed my deleteCommand parameters from almost every
datatype I could, Int16, Int32,...
3. Changed my DeleteCommand String.
4. Many other changes to my code...etc

No matter what changes I do, I've always gotten that
exception. Can anyone fill me in, or give me some ideas
to try out. My database data doesnt change from the time
I Fill my DataSet to the time I Update my Database.

Thanks,

Dan
 
with the following code

myDataAdapater.ContinueUpdateOnError = True

I am now able to add/delete items without a problem. If
having this property value being true is a bad thing, it
doesnt really matter as this is for a small class project
and our teacher isnt that familar with ADO.NET.

I will be doing at least 3 database projects soon(using
Oracle, Access and Sql Server 2k), so any information on
the effects of the above property change would be
appreciated.
 
This property tells ADO to ignore errors returned by any
Update/Delete/Insert exceptions. Let's say you make a dozen changes to the
rows in the detached DataSet. When you execute Update, ADO.NET starts making
the Updates one-by-one. If one of these fails, the process of updating is
terminated with an exception--unless you use ContinueUpdateOnError=True. In
this case, if an update fails, the fact that the action query did not
succeed is recorded in the RowState and the Update method continues on.
However, it also means you must check the HasErrors property post Update to
see if anything went wrong. You can then filter on the
RowState to pick off the rows that did not update and take corrective
action.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Dan,

Bill's right. ContinueUpdateOnError isn't a magic wand.
Setting the property to True simply tells the DataAdapter to
continue processing the rest of the pending changes when a failed
update attempt occurs.

The DataAdapter submits updates by examining the pending
changes stored in a row, determining which updating logic to use
(InsertCommand/UpdateCommand/DeleteCommand), applies values to
the parameters of the command based on the SourceColumn and
SourceVersion properties of the parameter objects, and executes
the command. The DataAdapter uses the RecordsAffected
information returned by the command to determine whether the
update attempt succeeded or failed. If RecordsAffected returned
0, the DataAdapter assumes the update attempt failed because of a
failed concurrency check and throws a DBConcurrencyException.

The exception is a common one. However, most posts to the
newsgroup regarding this exception involve scenarios where the
row was not updated in the database. The scenario generally
involves another user modifying data in the database, or not
including logic in the DataAdapter to fetch server-generated data
(key values, timestamps, defaults). There are also scenarios
where action queries executed by triggers with SQL Server could
result in the "records affected" value being misleading, but such
scenarios can be resolved by using SET NOCOUNT within the trigger
and caused failed update attempts to look successful rather than
the other way around.

Your best bet in understanding the problem and getting help
would be to isolate and reproduce the problem. I'd recommend
trying to execute the command through your own code and checking
the value reported for records affected.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top