“无法找到行以进行更新"错误[另一个] [英] "Row cannot be located for updating" error [another]

问题描述

我正在使用Delphi(XE3)，需要连接到MySQL数据库.我遇到了一个看起来很普遍的奇怪问题，但我仍然没有完全解决这个问题.

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.

传统解决方案包括:

• 将更新条件"设置为adCriteriaKey.
• 确保您的表具有主键(并告知ADO表)

问题1:启动应用程序，执行代码:如果新值碰巧与数据库中已有的值匹配，则在"B"位置出现错误.

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".

问题2:启动应用程序，执行代码:如果新值恰好与数据库中已有的值不同，它将成功执行一次，然后在位置"A"给出错误.

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;

就好像Post方法或连接使数据库处于某种中间状态...

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

这是我演示问题1时数据库日志的内容.

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}'

这是我演示问题2时数据库日志的内容.

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.

将该表缩减为仅两个字段:facilityid varchar(38)，facilityaddress1 varchar(50).同样的结果...

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

推荐答案

form http://www.connectionstrings.com/mysql-connector-odbc-5-2/，我发现:

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

Provider = MSDASQL; Driver = {MySQL ODBC 5.3 UNICODE 驱动程序};持久性安全信息= True; Server = yukon;数据库= ocean; User = M; Password = p; Option= 2;

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

对于VB，建议使用"Option = 2" http://dev.mysql.com/doc/connector-odbc/zh-CN/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-combos

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

有效！

谢谢大家的贡献.

这篇关于“无法找到行以进行更新"错误[另一个]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！