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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mhuntsAuthor Commented:
Not quite. The example only calls one stored procedure. Look at my question again.
0
DarrenSenior Software EngineerCommented:
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
dctuckCommented:
       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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.