title: "Row cannot be located for updating" error [another]
body: <p>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.</p>
  
  <p>traditional solutions include:</p>
  
  <ul>
  <li>setting "Update Criteria" to adCriteriaKey.</li>
  <li>ensure your table has a primary key (and tell the ADO Table about it)</li>
  </ul>
  
  <p><strong>problem 1:</strong>
  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".</p>
  
  <p><strong>problem 2:</strong>
  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".</p>
  
  <p>there shouldn't be any problem locating the record at anytime since the record primary key has not been changed.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>it's as though the Post method or the connection left the database in some intermediate state...</p>
  
  <p>here's what the database log says when I demonstrate problem 1.</p>
  
  <pre><code>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}'
  </code></pre>
  
  <p>here's what the database log says when I demonstrate problem 2.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>setting the "Update Criteria" in various places was of no help.</p>
  
  <p>reduced the table down to just two fields:  facilityid varchar(38), facilityaddress1 varchar(50).  same result...</p>