Nesting SQL Transactions

Hopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.

First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend.

My SQL database is relational and is used to store names, addresses, etc of other companies.

What I need to be able to do is have 2 transactions, one nested within the other. In pseudo-code:

BEGIN TRANSACTION1

BEGIN TRANSACTION2

INSERT INTO COMPANY TABLE

COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2

Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit. Therefore, I can't retrieve the companyID halfway through.

Is what I'm trying to do even possible? Thanks in advance for the help!

BEGIN TRANSACTION

INSERT INTO COMPANY TABLE

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION -OR- ROLLBACK TRANSACTION

dim conn as new sqlconnection("{ConnectString}")
conn.open
dim cmd as new sqlcommand("INSERT INTO Table1(column1) VALUES (@.col1) SELECT SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.col1",sqldbtype.{Whatever}).value={Whatever}
dim MyID as integer=cmd.executescalar
dim cmd2 as new sqlcommand("INSERT INTO Table2(Table1ID,col2) VALUES (@.MyID,@.MyVal)",conn)
cmd2.parameters.add("@.MyID",sqldbtype.integer).value=MyID
cmd2.parameters.add("@.MyVal",sqldbtype.{whatever})
for loop here...
cmd2.parameter("@.MyVal").value={whatever}
cmd2.executenonquery
next
conn.close

Now wrap that in try/catch with a transaction and you are all set.|||

Motley-

Thanks for pointing me in the right direction! At first I wasn't sure exactly how to implement what you gave me with what I already have. After some trial and error and info fromthis site I was able to get my transaction to work exactly as I needed it to.

Thanks again for the help!