Help on Creating an MTS(COM+) Transaction loop 1

[DJWL]

Hi, everyone

I have a multiple insert procedure something like below.
The data layer methods each create a connection, run a stored procedure(SQL Server) and close the connection.

Can anybody tell me if this will work and roll back when/if an error is found. I am worried that running the same procedure within a loop will stop it working ( I have never done any transactions before ).

Private sub InsertHeaderItems(strHeaderID,lngTimes as long)
Dim lng as Long
Dim ObjDataLayer as Object
Dim ctx as ObjectContext
set ctx = GetObjectContext
' Insert a header record on the Database
on error goto ErrorHandler
ctx.DisableCommit

Set ObjDataLayer = CreateObject("prjDL.DataLayer&quot
objDataLayer.InsertHeaderOnDatabase(strHeaderID,lngTimes)

For lng = 1 to lngTimes
' Insert an item record on the Database
objDataLayer.InsertItemOnDatabase(strHeaderID,lng)
Next

ctx.EnableCommit
ctx.SetComplete
Exit Sub
ErrorHandler:
ctx.SetAbort
End Sub

 

[SemperFiDownUnda]

Note that if this loop executes 1000 times and your class is in a Component Services Transaction that you will create 1000 connections to the database

 

[DJWL]

Thankyou SemperFiDownUnda , I was aware that if it worked it would have to persist each connection (even a 1000) until the setcomplete (In practice it would be limited to on avg about 10). I can think of better ways of doing this but not without rewriting a lot of code (3 months).

What I really like to know is if this method works, by your answer you seem to suggest it does?

 

[SemperFiDownUnda]

Yes it does. And though it might not be the best solution we have to live within constraints. Yours being you don't have resources to rewrite and test a whole slew of code. Which in my view is a valid reason to do it the way you are doing it when taking in all other conciderations.