Re: ADO and DELETE Statement

On Thu, 8 Jun 2006 19:08:28 -0500, "Richard Mueller"
<rlmueller-NOSPAM@xxxxxxxxxxxxxxxxxxxx> wrote:

Hi,

Can you use transactions? The ADO connection object has BeginTrans,
CommitTrans, and RollbackTrans methods. Also, if an error is raised when the
delete fails, you should be able to trap that. In brief:

read record
BeginTrans
post purchase order
delete record
CommitTrans

If the CommitTrans method is never invoked because the delete failed, the
purchase order is never posted. Transaction rollbacks apply to INSERT,
UPDATE, and DELETE statements, but not SELECT, CREATE, ALTER, or DROP.

Well, I am doing this in Navision (Dynamics-NAV) using the MS ActiveX
Data Access lib. I have looked at all kinds of properties on the
connection object, nothing indicated an error status (same values,
regardless of success or failure).

To give you an idea of what I am doing, here is the relevant code
snippet:


sSql := '';
sSql := 'DELETE FROM Test_Warehouse_Transfer WHERE PO_No = ' +
'''' + 'P00000983' + '''';
SQL_Execute_Delete(sSql);

And then the function:

SQL_Execute_Delete(pInSQL : Text[1024])
IF ISCLEAR(adoRS2) THEN
CREATE(adoRS2);

adoRS2 := adoConnection.Execute(pInSQL);

temp1 := adoConnection.State;
//temp2 := adoRS2.RecordCount;

So the recordset is bascially empty because the DELETE operation
doesn't return a recordset.

The purchase order is posted in Navision. THat means that even if I
use the BeginTrans and CommitTrans in ADO and it fails, the PO is
still posted in Navision. It looks like this:

The first line is what posts the PO line in Navision. So if that
succeeds (TRUE is returned), I need to make 100% sure that the record
is deleted in SQL.

IF NOT lCuPostPO.RUN(lRecPO) THEN BEGIN
ERROR('Could not successfully post PO %1',lRecPO."No.");
END
ELSE BEGIN
// delete record from SQL
sSql := '';
sSql := 'DELETE FROM '+ gRecInventorySetup."Receiving
Confirmation" + ' WHERE CO = ' + '''' + COMPANYNAME() + '''';
sSql += ' AND Tag_No = ' + '''' + lTagNo + ''''; // The TAG
number from the receiving system is unique
SQL_Execute_Delete(sSql);
END;

Thanks for your help!
.

Relevant Pages

  • Re: ADO and DELETE Statement
    ... CommitTrans, and RollbackTrans methods of the ADO connection object, SQL ... I would expect documentation for Navision to discuss transactions. ...
    (microsoft.public.data.ado)
  • ADO and rollbacktrans, committrans, begintrans
    ... committrans and rollbacktrans methods of ... the connection object. ... i do a .begintrans, I open a adodb.recorset1 and then i add some ... the new added records unless i .committrans, ...
    (microsoft.public.data.ado)