Solved

multiple transactions

Posted on 2012-03-30
2
282 Views
Last Modified: 2012-03-30
Hi

I want to use transactions in a vb.net application. This is the working code I have so far:

       Dim sqlCommand As String = "SELECT * FROM tblMyTable"

        Dim da1 As New SqlClient.SqlDataAdapter

        da1 = New SqlClient.SqlDataAdapter(sqlCommand, objConnection)

        Dim cmdClientsBuilder As New SqlClient.SqlCommandBuilder
        cmdClientsBuilder = New SqlClient.SqlCommandBuilder(da1)
        da1.InsertCommand = cmdClientsBuilder.GetInsertCommand()
        da1.DeleteCommand = cmdClientsBuilder.GetDeleteCommand()
        da1.UpdateCommand = cmdClientsBuilder.GetUpdateCommand()

        Dim txn As SqlClient.SqlTransaction = objConnection.BeginTransaction

        da1.UpdateCommand.Transaction = txn
        da1.DeleteCommand.Transaction = txn
        da1.InsertCommand.Transaction = txn

        Try
            da1.Update(Me.dt1)
            txn.Commit()
            Console.WriteLine("Insert succeeded")
        Catch ex As Exception
            txn.Rollback()
            MessageBox.Show(ex.Message)
            Console.WriteLine("Insert Failed")
        Finally
            'Optional. A Finally block is always executed when execution leaves any part of the Try statement.
        End Try

HOWEVER, I am puzzled how to use this if I had another update which I wanted to perform. For example, suppose I wanted to update another table as well e.g. dt2

        Try
            da1.Update(Me.dt1)
            da2.Update(Me.dt2)
            txn.Commit()
            Console.WriteLine("Insert succeeded")

To do this, would I need to have two transaction objects?

Could somebody point me in the right direction, with example code, on how to do this?

Thanks in advance

John
0
Comment
Question by:IssacJones
2 Comments
 
LVL 8

Accepted Solution

by:
gpizzuto earned 300 total points
ID: 37786487
You have to set the transaction property also to da2.

da2.UpdateCommand.Transaction = txn
da2.DeleteCommand.Transaction = txn
da2.InsertCommand.Transaction = txn

Beware: the transaction will rollback BOTH the updates if one of them fails.
0
 

Author Closing Comment

by:IssacJones
ID: 37786565
Many thanks!!!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
No Data for DropDown List 2 28
"Emulate" TAB key when press Enter Key 3 49
Very Large data in MYSQL 7 74
System.net.Sockets Error 5 28
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 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