Problem updating a query

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Problem updating a query

Post by swright » Mon 15 Nov 2010 16:03

HELP!!!!!!!

I have a problem with a query and update updating more than one record where only one record should be changed.

the code adds the sql to qryAnima, opens it

it then does

if (not qryAnimal.IsEmpty) then
begin
qryAnimal.Edit;
{ sets 3 fields eartag / idlot / idtransaction }
qryAnimal.Post;
end;

problem is the edit and post update multiple records, because the lock and update does not take 'Detained No' field into consideration.


I have used dbmonitor to show the SQL

The Initial query :-

SELECT TOP 1 "ANIMAL"."Lot No","ANIMAL"."Kill Date","ANIMAL"."Kill Time","ANIMAL"."Species","ANIMAL"."Participant Code","ANIMAL"."EarTag","ANIMAL"."idLot","ANIMAL"."idTransaction" FROM "ANIMAL" WHERE "ANIMAL"."Species"='PIGS' AND "ANIMAL"."Participant Code"='001' AND "ANIMAL"."Kill Date"='2010-11-15' AND "ANIMAL"."Lot No"='LOT1 ' AND "ANIMAL"."Detained No"=10 AND "ANIMAL"."idTransaction" IS NULL

then Savepoint LOCK_qryAnimal

SELECT * FROM ANIMAL
WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
WHERE
[Lot No] = ? AND [Kill Date] = ? AND [Kill Time] = ? AND Species = ? AND [Participant Code] = ? AND EarTag = ? AND idLot IS NULL AND idTransaction IS NULL

the update

UPDATE ANIMAL
SET
EarTag = ?, idLot = ?, idTransaction = ?
WHERE
[Lot No] = ? AND [Kill Date] = ? AND [Kill Time] = ? AND Species = ? AND [Participant Code] = ? AND EarTag = ? AND idLot IS NULL AND idTransaction IS NULL

AndreyZ

Post by AndreyZ » Wed 17 Nov 2010 10:43

Hello,

To solve the problem you should include the primary key fields into your initial query.

swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Post by swright » Wed 17 Nov 2010 10:46

Thanks,


I had already worked that one out with dbMonitor.

swright
Posts: 28
Joined: Wed 23 Dec 2009 12:19

Post by swright » Wed 17 Nov 2010 10:50

Is there another method to specify the primary key fields for the re-read and update.

AndreyZ

Post by AndreyZ » Wed 17 Nov 2010 14:27

You can use the KeyFields property of the TMSQuery component. For more information please read SDAC Reference Manual.

Post Reply