Rollback mulitple "insert" statements

[SatishPutcha]

HI everybody,
I have an ASP pag where I am connecting to SQL2000 and inserting data into 7 tables of the SQL DB.
I am using just a single connection object for this and executing the for each of the insert statements.
Is it possible to rollback all the insert statements executed till then if there is an error in one particular insertion?
eg. If "con.execute(insert1)" was successful and so was "con.execute(insert2)" but "con.execute(insert3)" shows an error then can I rollback the first two insert statements? "con" here is the ADODB connection object and insert1/2/3 are the insert statements for different tables.

Hope my question is clear.

Thanks and Regards
satishkumar

 

[codestorm]

1. SQL Server stored procedures do this implicitly, and can further be explicitly coded to do this to suit requirements.

2. never tried it so not sure how it rolls back database calls (if at all), but ASP supports MTS transactions. Check in ASP.CHM. codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[roy29]

For SQL Server the default is to commit a transaction unless you specify BEGIN TRANS. So every time you do a con.execute the server implicitly commits the transaction so you don't have the opportunity to issue a rollback later. In VB and in stored procedures you can say BEGIN TRANS to tell SQL Server not to commit the changes until you specify COMMIT TRANS. I've looked for ways to do this but haven't been successful. I would have to agree with codestorm that a stored procedure would be your best option.

I see your comment about MTS and you're right that it will work that way however creating a package would take far longer and be harder than creating the stored procedure.