Wrapping INsert statements in in sql transactions

Posted on 2012-09-13
Last Modified: 2012-11-14
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
Question by:TrialUser
    LVL 83

    Expert Comment

    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.

    Author Comment

    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

    Author Comment

    Is there anywy to do it from code, using javascript or, so the setting is only specific to my application. Thanks
    LVL 83

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now