Problem deleting records

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tonisanta
Posts: 55
Joined: Wed 04 Apr 2007 17:25

Problem deleting records

Post by tonisanta » Wed 11 Apr 2007 16:19

When deleting records from a runtime-created query an error like 'update failed. Found 0 records' appears. I'll send you an email showing the problem.
Toni

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Thu 12 Apr 2007 10:17

This error occurs when the database server is unable to determine which record to modify or delete. In other words, there are either more than one record or no records that suit the UPDATE criteria. Such situation can happen when you omit the unique field in a SELECT statement (TCustomDADataSet.SQL) or when another user modifies the table simultaneously. This exception can be suppressed. Refer to TCustomDADataSet.Options topic in SDAC help for more information.

tonisanta
Posts: 55
Joined: Wed 04 Apr 2007 17:25

Post by tonisanta » Thu 12 Apr 2007 10:27

hi,
did you try the sample/database I sent you by mail? You will see, the primary key is included in the query (select * ..) and there are no duplicates. The interested record is deleted as expected but afterwards the errormsg is raized. Please, let me know. I've see, when setting StrictUpdate to false the message doesn't appear. Since there are a lot of deletes of this kind I preferred to sign you this 'problem' not present with SDAC 3.8.
best regards
Toni

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Fri 13 Apr 2007 11:17

The StrictUpdate mode (TCustomDADataSet.Options.StrictUpdate = True) doesn't support triggers on INSERT, UPDATE, or DELETE statements. As triggers can be BEFORE, AFTER, INSTEAD OF and may containt INSERT, UPDATE, or DELETE statements (like in your case), there is no possibility to determine the correct RowsAffected value returned from server. In your example when the DELETE operation is performed, server returns:
(0 row(s) affected)
(1 row(s) affected)
The first result from the DELETE trigger and the second is from the DELETE statement. But if you declare your trigger as AFTER trigger, results will be received in the different order.
We do not recommend using the StrictUpdate mode with triggers.

tonisanta
Posts: 55
Joined: Wed 04 Apr 2007 17:25

Post by tonisanta » Fri 13 Apr 2007 13:04

Many thanks for investigating into this. So I'll have to check / change my current code. Remains to say in Sdac3.8 it worked without a 'update failed-msg' still if StrictUpdate was true.
best regards, Toni

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Fri 13 Apr 2007 15:34

It was just an accident that this was working with SDAC 3.80.
The fact is that in SDAC 3.80 the RowsAffected property was filled with the number of rows affected by the last executed statement.
In SDAC 4 the RowsAffected property is filled with the number of rows affected by the first executed statement.
Changing the received number of affected rows (from the first statement or from the last) won't solve the problem because you can use AFTER and INSTEAD OF triggers and there is no possibility to determine which type of trigger do you use.

Post Reply