Record Has Been Changed By Another User when try to Post or Edit

Question

Am developing application using Delphi 7 and Sql Server express 2008 R2 , am using BDE to connect Sql Server through ODBC, and SQL Native Client is used for driver.

In some particular forms the table component raise EDBEngineError 'Record has been changed by another user' , am just try to append a data, the same problem occur when table.cancel, table.post, table.edit.....

Few days before i changed driver from SQL Server to Sql Native Client because of sql server has problem with "connection has a result set for another hstmt" , so that i changed it to SQL Native client, form this i met this 'Record has been changed by antoher user' error.

Please any one help

Answer 1

1. BDE is not updated long time. And SQL Native Client is updating regularly. Possibly you run into incompatibility of BDE and SQLNC v 10.5.
2. "Record has been changed by another user" means, that API returned <> 1 of the updated records, for example 0 records. In most cases it is not really, that some user changed the record. The number of updated records is returned as a separate message, so if a connection is busy with other messages, then it may return 0. If your tables have the triggers, then try to add SET NOCOUNT ON at top of a trigger.
3. "Connection is busy with result set for another hstmt" means, that you have not fetched all records from an active result set. You can try fetching all records.
4. To avoid all these errors, consider to migrate to a dbExpress or a 3d party library like AnyDAC.

Addition to (2): Other classical reason for "Record has been changed by another user". The WHERE phrase includes a field and a corresponding parameter, which do not match by precision (eg, precision is losted at client side), have different formats (eg, incompatible character sets), etc. You have to check the SQL command and parameter values sent to SQL Server using SQL Profiler.

Answer 2

My friend, you should consider update your developing tools. For this problem you should really work with the ZEOS Component Pack for Delphi. It can connect to many kinds of databases, including Firebird, MySQL, Access and SQL Server. All you have to do is put the dll of the database you are using in your project directory and bin direcoty of delphi (if the target computer does not have the database manager). Then you let the database server to take care of the transactions. If you need more details, just give me a signal.