Problem updating a query

[swright]

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]

Hello,

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

[swright]

Thanks,


I had already worked that one out with dbMonitor.

[swright]

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

[AndreyZ]

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