• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2632
  • Last Modified:

Begin/End Transaction VB.NET

I have a VB.Net application that calls a couple of stored procedures that update a couple of tables before updating the final table.  I have a trigger which checks a couple of conditions when updating the final table.  If they don't meet, it rolls back the transaction.  I want to be able to roll back all transactions.  Can I:

Have a begin transaction and end transaction within my .net application which would surround the stored procedure calls and the update on the main table (even though the main table has a begin and end transaction of its own in the trigger).  I want to be able to rollback all changes.


  • 4
  • 3
1 Solution
You certainly can.

        Dim conn As New SqlConnection("ConnectionString")

        Dim com As SqlCommand = conn.CreateCommand()
        Dim sTrans As SqlTransaction

        sTrans = conn.BeginTransaction()
        com.Transaction = sTrans

        'set command text, execute procedures, etc...

just make sure you're using the same COMMAND object to execute all of the procedures or sql calls
barnetjebAuthor Commented:
How do I remove the parameters after each call?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Technically I guess there is no reason to actually only use one command object (I've just always done so because it seems cleaner to me, why make multiple command objects unless you have to?).

If you create multiple command objects, as long as you create them off the same connection, and assign them the same SqlTransaction object, it should work fine, I've just never had an occasion yet where one object wouldn't suffice.

The command above will clear out all of the parameters if you wish to do it that way though, and then just reassign .CommandText (and .CommandType if needed).
barnetjebAuthor Commented:
I'm getting this error:
Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction.  The transaction property of the command has not been initialized.

This command will call 3 stored procedures.  The last stored proc will do an update on a table, which has a trigger.  It checks a couple of conditions and then is rollsback the transaction if they are not met (this transaction exists only in the trigger).  The transaction I want to make will surround all 3 calls.

Can I remove this transaction and have my trigger return something?  If so, how do I do this?

Here is my code:
con is a global to this class and is initialized in the page load event.

Dim cmd as SqlCommand = new sqlCommand()
Dim sTrans as SqlTransaction
cmd.Connection = con
cmd.Transaction = sTrans
'Do some stuff
'If good - sTrans.Commit()
'Else - sTrans.Rollback()
barnetjebAuthor Commented:
Sorry that was confusing....

begin trans 1
stored proc 1
stored proc 2
stored proc 3
 ....fires trigger - begin trans 2, end trans 2
end trans 1

I would like to get rid of trans 2 and just return something from my trigger that says the conditions were not met.  Then only be using 1 transaction and rollback everything when there is a problem in one of the three stored procs.

Thanks for all your help!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now