 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / December 2004TRANS/ROLLBACK Using ADO
I'm in the process of writing an update function within a VB6 application. I have a stored procedure that I'm calling that simply accept parameters and performs an INSERT into a table. My application basically has to loop thru a listview control and insert, row-by-row, each item into the table by calling my SP. My question is this: If I want to use a BEGIN TRANSACTION/ROLLBACK TRANS and COMMIT TRANSACTION statements within my SP, can I also use the .BeginTrans, CommitTrans, and RollbackTrans within ADO? If I do, how does this work by having an ADO BeginTrans within my VB6 code and BEGIN TRANSACTION with my SP? If this works, is it good practice? Does it even matter/ The reason I want to do the BeginTrans within my VB6/ADO application is in case one of the inserts fails when calling my function within VB. I then want all the inserts to fail and rollback to the beginning. Opinions? The easiest way to see if this work for you is to run a quick test. Another thing you may want to do is post to the ADO or SQL Server newsgroups as you may get an answer quicker.  SignatureChris Hanscom - Microsoft MVP (VB) Veign's Resource Center http://www.veign.com/vrc_main.asp -- > I'm in the process of writing an update function within a VB6 application. > [quoted text clipped - 22 lines] > > Opinions? I haven't done that but it sounds fine to me. The transaction affected would depend on what failed (The code calling the SP or within the SP). By the sound of it you need both. I'd do it. > I'm in the process of writing an update function within a VB6 application. > [quoted text clipped - 22 lines] > > Opinions? Given what you stated you want in the last paragraph, you should not have the transaction statements in the SP. Just invoke the BeginTrans method of the Connection object before entering the loop and then CommitTrans after successfully iterating the loop. Be sure to have and error handler in which you RollBackTrans. IOW, all Inserts should be done within the same transaction. There is no reason to have transactions in both the SP and in your VB code, and in fact, transactions in the SP would not do want you want as each Insert would get committed or rolled back on an individual basis. Mike His SP will have to return a success/fail flag. >> I'm in the process of writing an update function within a VB6 >> application. [quoted text clipped - 41 lines] > > Mike Correct -- I guess I was thinking a little ahead. I may plan on having additional queries/deletes within the SP that may need to be rolled back. Yes, I can add the BeginTrans within the VB, my only other thought was that other users may need to manually run the SP from outside the VB application. In which case, I would want the BEGIN TRANS within the SP. My dilemma, if you will, is that I need to call my VB function about 25 times to insert 25 rows separately. If there is an error, I want the Registrants within my VB to rollback all the inserts from the beginning. If I only add the BEGIN TRANS within the SP and it fails, it will only roll back the one item. >> I'm in the process of writing an update function within a VB6 >> application. [quoted text clipped - 41 lines] > > Mike hi, nested triggers and transactions are allowed on sql 2000. see sql books online: "When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0 you are not in a transaction." Signaturekecskemetisrac@free-mail.hu remove "-" from email address when reply. > Correct -- I guess I was thinking a little ahead. I may plan on having > additional queries/deletes within the SP that may need to be rolled back. [quoted text clipped - 53 lines] > > > > Mike > My dilemma, if you will, is that I need to call my VB function about 25 > times to insert 25 rows separately. If there is an error, I want the > Registrants within my VB to rollback all the inserts from the beginning. If > I only add the BEGIN TRANS within the SP and it fails, it will only roll > back the one item. Right. That's why a transaction should not be in the SP at all. It should be in your VB code; started before the 1st Insert and committed upon successful completion of all 25. As Wart said, you might want your SP to return a success/fail value that you can check. If the SP returns failure, have your VB code rollback everything (since everything up to that point it part of a single transaction, there's only one rollback to do and it will rollback everything from the point of the last BeginTrans method). Mike |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.