Hi Mauro,
Based on the way you have set up this transaction you will always roll
back the entire transaction when you hit an exception as your second
try, except block is within the first. So if the second fails then so
does the first, you may want to set each set of inserts in their own
transactions and test each one.
Can you have records in table 1 if table 2 or 3 fail? Can you give me
an idea of what sort of rules you are trying capture within the transaction.
Also when Committing your transaction you may wish to test for it first,
i.e.:
if MyAdoConnection.InTransaction = True then
MyAdoConnection.CommitTrans;
Kind regards,
Mike.
mauro russo wrote:
> Hi everyone,
>
> I am using Delphi 7.
>
> I am using a TADOConnection object and some design-time TADOQuery objects.
>
> A my procedure works starting by
>
> MyAdoConnection.BeginTrans();
>
> and ending by
>
> MyAdoConnection.CommitTrans();
>
>
> In the block there is a MyADOQuery object for which I use (becaus of
> sometimes it can generate DB exceptions)
>
> try
> MyADOQuery.ExecSQL();
> except
> end;
>
>
> My problem is that when MyADOQuery.ExecSQL() generates an exception, the
> transaction is automatically closed.
>
> I say that because my pascal-like code is
>
> MyAdoConnection.BeginTrans();
> try
> Insert in a MyTable1
>
> for each row on a MyTable2
> do Insert in a MyTable3 through
> a) preparing MyADOQuery
> b) executing MyADOQuery by
> try
> MyADOQuery.ExecSQL();
> except
> end;
>
> MyAdoConnection.CommitTrans();
> except
> MyAdoConnection.RollbackTrans();
> end;
>
>
> and, when MyADOQuery.ExecSQL(); generates an exception for some row, the same
> happens for all following rows,
> but for the following rows it is because MyTable3 has a reference to the row
> initially inserted in MyTable1, which is
> like disappeared (my explanation is simply that the transaction is
> automatically closed with a Rollback before
> continuing the for cycle).
>
> Do someone knows if there is some property which I should set to avoid the
> automatic rollback on the transaction?
>
> Do you need some other detail?
>
>
> Best Regards,
> Mauro Russo.
>
>
>
> ----------
>
> Questa email รจ stata verificata dal sistema centralizzato antivirus della
> UniPlan Software
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> -----------------------------------------------------
> Home page: http://groups.yahoo.com/group/delphi-en/
> To unsubscribe: [email protected]! Groups Links
>
>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.0.233 / Virus Database: 270.10.19/1941 - Release Date: 02/07/09
> 13:39:00
>
>
