DataAdapter Concurrency violation
Posted by: Gal Rubinstein
Date: January 15, 2010 09:47AM

Hello all,

.NET framework = 3.5, MySQl .Net connector = the latest.

I'm running into a "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." exception when I try to use the Update method, here is the code:

the table :
CREATE TABLE IF NOT EXISTS `vcard` (
`vcard_idx` INT(9) NOT NULL AUTO_INCREMENT ,
`pincode` INT(4) UNSIGNED NULL DEFAULT NULL ,
`rfid` INT(6) NULL DEFAULT NULL ,
PRIMARY KEY (`vehicle_card_idx`) )
ENGINE = INNODB

the update SQL:
private const string UPDATE_SQL = "UPDATE vcard SET pincode = @pincode, rfid = @rfid WHERE vcard_idx = @idx";

here is how I load the update MySqlCommand: (da = DataAdapter)

da.UpdateCommand = new MySqlCommand();
da.UpdateCommand.CommandText = UPDATE_SQL;
da.UpdateCommand.Parameters.Add("@idx", MySqlDbType.Int16);
da.UpdateCommand.Parameters.Add("@rfid", MySqlDbType.Int16);
da.UpdateCommand.Parameters.Add("@pincode", MySqlDbType.Int32);

(also tried without giving a DBType)

at this point it is also important to add the fact that there was a query done to the database using
the same adapter, which, from the return results one of the rows is entered onto a form for update.

after the data was modified by user.
comes the code that creates the Exception:
(ds = the DataSet, table = the DataTable)
VCard vc = (VCard) entity; //this is a very basic object entity object with properties
DataRow row = table.Rows[CurrentRecord];
row.BeginEdit();
row["pincode"] = vc.Pincode;
row["rfid"] =vc.Rfid;
row["idx"] = vc.CardIdx;
row.EndEdit();
da.UpdateCommand.Connection = getConnection();
da.Update(dataSet, tableName); //<- the exception is thrown here
dataSet.AcceptChanges();
da.UpdateCommand.Connection.Clone();

and:

protected MySqlConnection getConnection()
{
return new MySqlConnection(connectionString);
}

the get was added because of a no connection to UpdateCommand exception.

just for the sake of argument I tryed a different approach with the same SQL
update and Parameter that works, yes, THIS WORKES!

VCard vc = (VCard) entity;
MySqlConnection conn = getConnection();
conn.Open();
MySqlCommand cmd = new MySqlCommand(UPDATE_SQL, conn);
MySqlParameter idx = new MySqlParameter();
idx.ParameterName = "@idx";
idx.MySqlDbType = MySqlDbType.Int16;
idx.Value = vc.CardIdx;
cmd.Parameters.Add(idx);

MySqlParameter pin = new MySqlParameter();
pin.ParameterName = "@pincode";
pin.MySqlDbType = MySqlDbType.Int16;
pin.Value = vc.Pincode;
cmd.Parameters.Add(pin);

MySqlParameter rfid = new MySqlParameter();
rfid.ParameterName = "@rfid";
rfid.MySqlDbType = MySqlDbType.Int32;
rfid.Value = vc.Rfid;
cmd.Parameters.Add(rfid);

cmd.ExecuteNonQuery();

conn.Close();

any idea?

Options: ReplyQuote


Subject
Written By
Posted
DataAdapter Concurrency violation
January 15, 2010 09:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.