"Unable to locate the update row" is reported when MYSQL is updated using ADO. Some values may have been changed after the last read.

"Unable to locate the update row" is reported when MYSQL is updated using ADO. Some values may have been changed after the last read.

This strange error occurred when I used ADO to connect MYSQL to update data through ODBC in DELPHI today: the row cannot be updated or located. Some values may have changed since the last read.

I was puzzled, So I went online to check. Some say that there is no primary key, but I have some in this table; some say that there are default values. I checked that all fields do not have default values; some say that they are caused by First INSERT and then EDIT, this is pure EDIT, POST, no INSERT, so it does not exist.

After searching for another half a day, I finally found a similar situation. Some people say that if the data modified by EDIT is the same as the original data, this error will be reported during POST. I checked it. Actually, the value assigned to the field by the EDIT operation is the same as that before the modification. If the field value is changed slightly, the POST operation can be submitted normally. The solution is to determine whether the values are the same before assigning values to the fields. This is enough for BT.

The problem is solved, but why? By enabling ODBC tracking logs for analysis, we can probably guess the cause. The following section shows the SQL trace log that assigns the same value to all fields:

BEXE 13f4-1118EXIT SQLExecDirectW with return code 0 (SQL _SUCCESS)
HSTMT 0x0362C568
WCHAR * 0x0B774FC8 [-3] "UPDATE 'test'. 'table1' SET 'table1id' = ?, 'Tid' = ?, 'Name' = ?,... 'Bkwfid' =? WHERE 'table1id' =? AND 'tid' =? AND... AND 'bkwfid' =? \ 0"
SDWORD-3

BEXE 13f4-1118 EXIT SQLRowCount with return code 0 (SQL _SUCCESS)
HSTMT 0x0362C568
SQLLEN * 0x0018E804 (0)
Note that SQLRowCount returns 0, indicating that 0 rows are updated.

The following is the SQL trace log with a value change:
BEXE 13f4-1118 EXIT SQLExecDirectW with return code 0 (SQL _SUCCESS)
HSTMT 0x0362C568
WCHAR * 0x0B76FEC8 [-3] "UPDATE 'test'. 'table1' SET 'table1id' = ?, 'Tid' = ?, 'Name' = ?,... 'Bkwfid' =? WHERE 'table1id' =? AND 'tid' =? AND... AND 'bkwfid' =? \ 0"
SDWORD-3

BEXE 13f4-1118 EXIT SQLRowCount with return code 0 (SQL _SUCCESS)
HSTMT 0x0362C568
SQLLEN * 0x0018E804 (1)

At this time, SQLRowCount returns 1, which indicates that one row is updated.

The two SQL statements are identical, except that the content of the record after the previous SQL statement is executed is not changed, and the other one is changed! Then MYSQL will tell you that you have not updated any records. In fact, you have updated a record, but the results are the same before and after UPDATE, which has the same effect as those without UPDATE, so MYSQL returned 0, so ADO thought that the data was not updated, so it was deemed that someone else had modified the data, so it reported the error "unable to UPDATE the location line.

Supplement:

Later, I went on to study. To verify this problem, I directly used the SQL command to update a record with NAME = 'announcement:

Mysql> update table1 set name = 'announcement 'where table1id = 1;
Query OK, 0 rows affected
Rows matched: 1 Changed: 0 Warnings: 0

Mysql> update table1 set name = 'announcement 1' where table1id = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

Mysql>

Apparently, it is because 0 rows affected is returned when the value is the same.

Then, when searching the Internet, I found that MYSQL has this problem. You only need to modify the ODBC connection configuration of MYSQL to solve the problem:

Http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-errors.html

Write Conflicts or Row Location Errors

How do I handle Write Conflicts or Row Location errors?

If you see the following errors, selectReturn Matching RowsOption in the DSN configuration dialog, or specifyOPTION=2, As the connection parameter:

Write Conflict. Another user has changed your data.Row cannot be located for updating. Some values may have been changedsince it was last read.

Modify the ODBC connection configuration as follows:

After the change, restart the application, and everything is normal.


In DELPHI, there is a warning that the location row cannot be updated. Some values can be changed after the last read.

Refresh the data before modification!

VB: Do not update the location line. Some values can be changed after the last read.

This is because the database is opened exclusively.