Solved

Visual Basic Transactions

Posted on 2007-03-29
4
971 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:mhunts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 18

Expert Comment

by:Ajay Sharma
ID: 18821915
0
 
LVL 8

Author Comment

by:mhunts
ID: 18821959
Not quite. The example only calls one stored procedure. Look at my question again.
0
 
LVL 18

Assisted Solution

by:DarrenD
DarrenD earned 100 total points
ID: 18822092
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
 
LVL 7

Accepted Solution

by:
dctuck earned 400 total points
ID: 18822589
       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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 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