"Row cannot be located for updating" error [another]

Question

I'm working with Delphi (XE3) and need to connect to a MySQL database. I'm having a strange problem that seems quite common but I still haven't entirely solved this problem.

traditional solutions include:

• setting "Update Criteria" to adCriteriaKey.
• ensure your table has a primary key (and tell the ADO Table about it)

problem 1: start the application, execute the code: if the new value happens to match what's already in the database, I get the error at position "B".

problem 2: start the application, execute the code: if the new value happens to be different from what's already in the database, it will execute successfully once and thereafter, give an error at position "A".

there shouldn't be any problem locating the record at anytime since the record primary key has not been changed.

object conMain: TADOConnection
  Connected = True
  ConnectionString = 
    'Provider=MSDASQL.1;Password=p;Persist Security Info=True;U' +
    'ser ID=M;Extended Properties="Driver={MySQL ODBC 5.3 ANSI Dri' +
    'ver};SERVER=yukon;DATABASE=db;UID=M;Pwd=p;PORT=3306;' +
    '"'
  LoginPrompt = False
  Mode = cmShareDenyNone
  Left = 48
  Top = 24
end

object ADOTable1: TADOTable
  Connection = conMain
  IndexFieldNames = 'FacilityID'
  TableName = 'facility'
  Left = 152
  Top = 24
end

procedure TForm1.Button1Click(Sender: TObject);
begin
  conMain.Open;
  ADOTable1.Open;
  ADOTable1.Properties.Item['Update Criteria'].Value:=adCriteriaKey;
  // **A**
  if ADOTable1.Locate('facilityid', '{C0FADCC8-15C9-48C8-8003-3BBD4AB74586}', []) then
    begin
      ADOTable1.Edit;
      ADOTable1.FieldByName('facilityaddress1').AsString:='mickey street';
      ADOTable1.Properties.Item['Update Criteria'].Value:=adCriteriaKey;
      // **B**
      ADOTable1.Post;
    end
    else
    showmessage('not found!');
  ADOTable1.Close;
  conMain.Close;
end;

it's as though the Post method or the connection left the database in some intermediate state...

here's what the database log says when I demonstrate problem 1.

M@D3400 on db
SET NAMES latin1
SET character_set_results = NULL
SET SQL_AUTO_IS_NULL = 0
select database()
select database()
SHOW GLOBAL STATUS
SELECT @@tx_isolation
set @@sql_select_limit=DEFAULT
select * from facility
SHOW KEYS FROM `facility`
UPDATE `db`.`facility` SET `FacilityAddress1`=? WHERE `facilityid`=?
UPDATE `db`.`facility` SET `FacilityAddress1`='mickey street22' WHERE `facilityid`='{C0FADCC8-15C9-48C8-8003-3BBD4AB74586}'

here's what the database log says when I demonstrate problem 2.

SHOW GLOBAL STATUS
M@D3400 on db
SET NAMES latin1
SET character_set_results = NULL
SET SQL_AUTO_IS_NULL = 0
select database()
select database()
SELECT @@tx_isolation
set @@sql_select_limit=DEFAULT
select * from facility
SHOW KEYS FROM `facility`
UPDATE `db`.`facility` SET `FacilityAddress1`=? WHERE `facilityid`=?
// SUCCESSFUL
UPDATE `db`.`facility` SET `FacilityAddress1`='mickey street22' WHERE `facilityid`='{C0FADCC8-15C9-48C8-8003-3BBD4AB74586}'
SHOW GLOBAL STATUS
SHOW GLOBAL STATUS
SHOW GLOBAL STATUS
db
select * from facility
UPDATE `db`.`facility` SET `FacilityAddress1`=? WHERE `facilityid`=?
// ERROR!
UPDATE `db`.`facility` SET `FacilityAddress1`='mickey street22' WHERE `facilityid`='{C0FADCC8-15C9-48C8-8003-3BBD4AB74586}'
SHOW GLOBAL STATUS

setting the "Update Criteria" in various places was of no help.

reduced the table down to just two fields: facilityid varchar(38), facilityaddress1 varchar(50). same result...

Answer 1

form http://www.connectionstrings.com/mysql-connector-odbc-5-2/, I found:

Provider=MSDASQL;Driver={MySQL ODBC 5.3 UNICODE Driver};Persist Security Info=True;Server=yukon;Database=ocean;User=M;Password=p;Option=2;

"Option=2" is suggested for VB http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-combos

works!

Thank you all for your contributions.