Re: ADO and DELETE Statement
- From: "Richard Mueller" <rlmueller-NOSPAM@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 9 Jun 2006 11:39:28 -0500
Hi,
Sorry, I don't understand the Navision code. Besides the BeginTrans,
CommitTrans, and RollbackTrans methods of the ADO connection object, SQL
itself has statements BEGIN, COMMIT, and ROLLBACK (Microsoft Access and
Microsoft SQL Server use BEGIN TRANSACTION). If you can submit more than one
SQL statement at a time (perhaps separated by semicolons), you can enclose
the relevant operations in a BEGIN / COMMIT block. You may not be able to
detect the failure, but if one occurs the purchase order is never posted.
I would expect documentation for Navision to discuss transactions.
--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
"Skeeve" <nospam@xxxxxxxxxxxxxxxxxx> wrote in message
news:m56j82h7of50ajh7cjfl12stcb2lllo6n8@xxxxxxxxxx
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!
.
- References:
- ADO and DELETE Statement
- From: Skeeve
- Re: ADO and DELETE Statement
- From: Richard Mueller
- Re: ADO and DELETE Statement
- From: Skeeve
- ADO and DELETE Statement
- Prev by Date: Re: ADO and DELETE Statement
- Next by Date: Re: ADO and DELETE Statement
- Previous by thread: Re: ADO and DELETE Statement
- Next by thread: Re: ADO and DELETE Statement
- Index(es):
Relevant Pages
|
|
