Board index » delphi » Delphi TUpdateSQL object against SQL Server 6.5 db

Delphi TUpdateSQL object against SQL Server 6.5 db

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!

 

Re:Delphi TUpdateSQL object against SQL Server 6.5 db


Re:Delphi TUpdateSQL object against SQL Server 6.5 db


The error that I reported when executing "Database1.ApplyUpdates" was
because I was using the wrong database object (blush). When I do it "right,"
I get the same error message as when I update one dataset at a time. I also
get this error when I try to delete a row.

Thanks again for your time.

Other Threads