Visual Basic Transactions

Visual basic transactions - I have stored procedures called from visual basic. How do I create transactions in visual basic?

For example, I'd like to do:

start transaction
vb code
stored procedure call
vb code
stored procedure call
if error, rollback
commit
LVL 8
mhuntsAsked:
Who is Participating?
 
dctuckConnect With a Mentor Commented:
       Dim myTrans As System.Data.SqlClient.SqlTransaction
        Dim myConn As New SqlClient.SqlConnection("connection string")
        Try
            myConn.Open()
            myTrans = myConn.BeginTransaction
            Dim myComm As New SqlClient.SqlCommand("Command text",myConn)
            myComm.Transaction = myTrans
           ' Perform your actions....
           ' Repeat the previous two steps for any additional commands
            myTrans.Commit()
        Catch
            myTrans.Rollback()
        Finally
            myConn.Close()
        End Try
0
 
mhuntsAuthor Commented:
Not quite. The example only calls one stored procedure. Look at my question again.
0
 
DarrenDConnect With a Mentor Commented:
Hi,

I have some code in C# to do this if you want to take it and convert it into VB.NET. I haven't out up any code as yet as I'm not sure if C# is any use to you.

Put simply you have to do the following:

This is performed in one method to setup the connection and transaction

// Create the connection.
// open the connection.
// Set the beginning of the transaction.

This is performed in another method so that you can call as many SP's as you want.
// Tell the command object the name of the SP to run.
// Tell the command object that it is part of a transaction.
// Tell the command object that it is going to run a SP.
// Execute the Query

Each time you call the second method above you must clear any parameters that you added for the call to the previous SP.

Then there is another method which I use to either commit the transaction or rollback.

I just have a simgle class which performs all of these operation for me. It's just a DAL.

Darren
0
All Courses

From novice to tech pro — start learning today.