Using ADO to run mulitple SQL statement in one transaction.

generic
generic Member Posts: 511
edited 2008-12-28 in NAV/Navision Classic Client
Hello.
I am doing an integration using ADO. My code looks something like below
IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand);   

ActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := varActiveConnection;   

ADOCommand.CommandText := 'insert into Some table ';
ADOCommand.CommandTimeout := 0;   
ADOCommand.Execute; 

ADOCommand.CommandText := 'insert into Some other table ';
ADOCommand.CommandTimeout := 0;   
ADOCommand.Execute;

I am executing two INSERT statement. which are separate transaction. So if second one fails the first one is COMMITED.
I wanted to know how I can run the two insert statements in same Transaction.

Thank you.

Answers

  • garak
    garak Member Posts: 3,263
    edited 2008-12-25
    do you have tested someting like this:

    CommandText := TextConst1 + Command1 + Command2 + TextConst2;

    TextConst1 is a TextConst with the value "beging trans"
    TextConst1 is a TextConst with the value "commit trans" or "end trans"
    Command1 is a TextConst with the value "insert into table XYZ values ABS"
    Command2 is a TextConst with the value "go insert into table TZU values GHT"

    ?
    Do you make it right, it works too!
  • garak
    garak Member Posts: 3,263
    Or you work with

    AdoConn.BeginTrans

    and

    AdoConn.CommitTrans

    The CommitTrans method saves all pending changes made since the last BeginTrans method call was used to initiate a transaction.
    Also take a look here on the method RollbackTrans() if needed

    Regards
    Do you make it right, it works too!
  • generic
    generic Member Posts: 511
    Thanks. That solved it. I'll update the heading.
  • garak
    garak Member Posts: 3,263
    Please and welcome
    Do you make it right, it works too!

Categories