Wrapping INsert statements in ado.net in sql transactions

Posted on 2012-09-13
Medium Priority
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
  • 2
  • 2
LVL 83

Expert Comment

ID: 38398130
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

ID: 38405424
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

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

Accepted Solution

CodeCruiser earned 2000 total points
ID: 38407178
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.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

807 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