Concurrency violation

Concurrency violation

Postby xterm » Mon 16 Jan 2006 23:12

i am using Optimistic Concurrency and mysqldirect .net 3.20.6 in a vb.net 2005 project with the following way. My tableadapter uses the updatecommand:
Code: Select all
UPDATE `protocol`.`proto` SET `id` = :id, `proto_number` = :proto_number, `proto_date` = :proto_date, `Last_Update` = now() WHERE `id` = :id_1 AND `Last_Update` = :Last_Update

Last_Update` describes when the row was last updated.
When i try to update the dataset with the adapter, i get a Concurrency violation error.This isn't supposed to happen because i am the only one who updates the db.My database has one table the 'proto'.

Please Help, Sorry for my english
xterm
 
Posts: 9
Joined: Tue 15 Nov 2005 13:24

Postby Serious » Tue 17 Jan 2006 09:11

We think you use parameters in incorrect way. Here is small sample of using TIMESTAMP fields.
Table definition
Code: Select all
CREATE TABLE test.opt_lck (
  id INT(11) NOT NULL AUTO_INCREMENT,
  f_char VARCHAR(45),
  f_timestamp TIMESTAMP(0) default 'CURRENT_TIMESTAMP',
  PRIMARY KEY (id)
)
Update command text
Code: Select all
UPDATE
  test.opt_lck
SET
  f_char = :f_char,
  f_timestamp = now()
WHERE (id = :orig_id) and (f_timestamp = :orig_timestamp);
Parameters created with DataAdapter designer
Code: Select all
updateCommand.Parameters.Add(new CoreLab.MySql.MySqlParameter("f_char", CoreLab.MySql.MySqlType.VarChar, 4, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "f_char", System.Data.DataRowVersion.Current, null));

updateCommand.Parameters.Add(new CoreLab.MySql.MySqlParameter("orig_id", CoreLab.MySql.MySqlType.Int, 1, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "id", System.Data.DataRowVersion.Original, null));

updateCommand.Parameters.Add(new CoreLab.MySql.MySqlParameter("orig_timestamp", CoreLab.MySql.MySqlType.TimeStamp, 19, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "f_timestamp", System.Data.DataRowVersion.Original, null));
Note that orig_id and orig_timestamp parameters have Original DataRowVersion.
Serious
 

Postby xterm » Tue 17 Jan 2006 10:00

I have already done what you suggested me...
the problem is that when i change something and then i try to update a record for the first time everything works fine.But if ichange something again in the same record and i update my record it raises an concurrency error.
xterm
 
Posts: 9
Joined: Tue 15 Nov 2005 13:24

Postby Serious » Tue 17 Jan 2006 12:38

You have to fetch inserted timestamp value when updating table. New command text for DataAdapter.UpdateCommand
Code: Select all
update
  test.opt_lck
set
  f_char = :f_char,
  f_timestamp = now()
where (id = :orig_id) and (f_timestamp = :orig_timestamp);

select f_timestamp from opt_lck where id = :orig_id;
You must set MySqlCommand.UpdatedRowSource to UpdateRowSource.Both.
Serious
 


Return to dotConnect for MySQL