I am trying to use updateSQL objects to apply cached updates on two queries
(master/detail). Right now I have only gotten to testing out the Modify SQL
statement and I can't seem to get it to work.
At first, I used the query ApplyUpdates methods so I could control the order
of the updates:
{The "si" procedures deal with the app's db object}
SIStartTransaction;
try
qrItem.ApplyUpdates;
qrRec.ApplyUpdates;
SICommit;
qrItem.CommitUpdates;
qrRec.CommitUpdates;
except
SIRollback;
end;
When I run the program and modify a row in the detail query (qrItem), post
the record and hit the apply button (which runs the code above), it runs
qrItem.ApplyUpdates fine. Then when it runs the next line
(qrRec.ApplyUpdates) I get an error from SQLServer:
"Connection is busy with results for another hstmt."
Why am I getting a SQL Server error anyway? There were no modified rows in
that dataset, so no SQL should have been run. Right? SQL Trace also shows
nothing being sent from the execution of that line.
Then I tried to run it just using
Database1.ApplyUpdates([qrItem, qrRec]);
This successfully avoided the above error and got me this one instead:
EDatabase error with message 'Cannot perform this operation on a closed
database'. Now it's telling me there's a closed database somewhere while I'm
looking at the data from two queries connected to that database.
Here is the Modify SQL from the detail query (the one that actually runs
when I apply updates).
UPDATE ReceiptItem
SET amount = :amount
WHERE moneyid = :OLD_moneyid
UPDATE Money
SET amount = :amount
WHERE id = :OLD_moneyid
UPDATE SalesItem
SET itemid = :itemid,
qty = :qty,
unitPrice = :unitprice
WHERE moneyid = :salesid
And here are the two queries themselves:
qrRec:
SELECT Trans.Dept, Trans.Date, Trans.id,
Rec.TotalAmount Amount, Rec.PayMethod, Rec.PayFrom,
Rec.ReferenceNumber,
(SELECT MIN(studentid)
FROM Money, SalesItem
WHERE Money.transactionid = Trans.id
AND SalesItem.moneyid = Money.id) studentid
FROM TransactionRecord Trans, ReceiptTransaction Rec
WHERE Trans.id = Rec.transactionid
ORDER BY Dept, Date
qrItem:
SELECT transactionid, Money.id moneyid,
Sales.moneyid salesid, Item.id itemid,
:studentid StudentID, RItem.Amount,
Item.Link+' '+Item.ItemType Item, Sales.Qty, Sales.UnitPrice
FROM ReceiptItem RItem, Money, SalesItem Sales, Item
WHERE
Money.transactionid = :id
AND RItem.moneyid = Money.id
AND Sales.moneyid = RItem.invoiceid
AND Item.id = Sales.itemid
Is that clear as mud?!
Thanks a bunch for any possible help!
