Row cannot be found for Locate?

Question

Hi, I'm converting existing Advantage Database Server application to SQL Server 2005 using D2009, dbGo (ADO). Sometimes i experience the error row cannot be found for locating. I had googled it, according to results i needed to set Update Criteria property of each ADOTable and set cursor location to dynamic. I did so, but sometimes i still get same error. All of the tables has primary key so I've been using Query.Requery(); Query.Locate('ID',ID,[]); before updating record to avoid error, but there has to be better solutions. Any ideas? Or should i move on to MyDAC or Zeoslib?

Answer 1

dbGO/ADO is the natural way to access MS-SQL databases. Are you sure that is the exact error message? Because I have never heard about it, but I have heard about "row cannot be located for updating".

That error message indicates that ADO can not find the record to be deleted or updated. Most often the cause is that the table does not have a primary key defined or there is at least no column in the table where the contents is all unique.

Make sure you define a primary key in your MS-SQL tables.

Answer 2

The usual case for row cannot be updated is that you have default value constraints on the tables. Be sure to set values for the fields in OnNewRecord. You may also want to use myAdoDataset.Properties['Update Criteria'].Value := 0; Then ADO should use only the key for updates.

Answer 3

In case anyone else has the same problem when using triggers, add SET NOCOUNT ON into beginning and SET NOCOUNT OFF into end of trigger.