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
Solved

Begin/End Transaction VB.NET

Posted on 2004-03-24
7
2,506 Views
Last Modified: 2010-05-19
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.

Thanks

0
Comment
Question by:barnetjeb
  • 4
  • 3
7 Comments
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 500 total points
ID: 10670575
You certainly can.

        Dim conn As New SqlConnection("ConnectionString")
        conn.Open()

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

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

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

        sTrans.Commit()
or
        sTrans.Rollback()
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 10670583
just make sure you're using the same COMMAND object to execute all of the procedures or sql calls
0
 

Author Comment

by:barnetjeb
ID: 10681247
How do I remove the parameters after each call?
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 22

Expert Comment

by:Snarf0001
ID: 10682876
com.Parameters.Clear()
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 10682946
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).
0
 

Author Comment

by:barnetjeb
ID: 10687564
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
con.Open
cmd.Connection.BeginTransaction()
cmd.Transaction = sTrans
'Do some stuff
'If good - sTrans.Commit()
'Else - sTrans.Rollback()
con.Close
0
 

Author Comment

by:barnetjeb
ID: 10687581
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!!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

791 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