Wrapping INsert statements in ado.net in sql transactions

In my ASP.NET / VB.NET application , i create a transaction which consists of 3 steps:
1) insert transactionheader row
2) insert transactiondetail rows
3) insert transaction payment rows.
HOw do I wrapp all these into a transaction so, if the steps 2 and steps 3 are not creatd properly then step 1 will be rolled back too. Thanks please help

 Dim dtTransaction As New dsTransaction.TransactionsDataTable
        Dim drTransaction As dsTransaction.TransactionsRow = dtTransaction.NewRow
        With drTransaction
            .UserID = intUserID
            .CustomerID = intCustomerID
            'assign all values here
        End With
        Dim intRtn As Integer = taTran1.Update(dtTransaction)
        drTransaction = dtTransaction.Rows(0)
        Dim dtTRansactioneNtry As DataTable =  'someho get dataable
        Dim drentry As DataRow
       For Each drentry In dtTRansactioneNtry.Rows
            taEntry.Insert(drTransaction.ID, Convert.ToInt32(drentry("ItemID")),all other fields here)
        Dim dtPayments As DataTable = 'getdt
           For Each drPayment In dtPayments.Rows
                taPayment.Insert(drTransaction.ID, Convert.ToInt32(drPayment("PaymentTypeID")), decAmount, strRef)

        Return drTransaction.ID
Who is Participating?
CodeCruiserConnect With a Mentor Commented:
Next option is to use same connection with both adapters, start transaction on this connection and then assign this transaction to all commands on both adapters.
You are using DataAdapters to insert these rows. It would be easier if you use SQLCommand object directly and execute insert statements so you can use transactions easily.

TrialUserAuthor Commented:
I cannot change the existing code due to other reasons. Is there a way to do it with the dataadapters and the way it has already been implemented. Thanks
TrialUserAuthor Commented:
Is there anywy to do it from code, using javascript or vb.net, so the setting is only specific to my application. Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.