Check for a Transaction??

[PogoWolf]

Hey All,
I'm hoping this is easy.. How does one check to see if
there's an active transaction before useing the
RS.RollbackTrans command?

Would this be done same way as checking to see if a record set is open?

If RS.State <> adStateClosed Then RS.Close
?

TIA!!!!!

The PogoWolf

http://www.4TheGames.net

 

[SemperFiDownUnda]

Recordsets don't have transactions the connection object has transactions.

Via good design you should know if you've started a transaction. Honestly if you don't know if you have started a transaction on a connection object then you need to rethink your program a bit.

State is just if the connection is open or not.

 

[CCLINT]

You need to be keeping track of the transactions that you initiate in code.

Anything less is bad programming, regardless if ADO would provides a mean to see if a transaction was started or not.

The next question would have to then be: How many nested levels of transactions are there on a certain connection? (a transaction with-in a transaction with-in a transaction...).

The BeginTrans returns a long, so you should create a variable of type long and set it to the return value of each BeginTrans call.

Then, after each CommitTrans, and of course after each RollBack, reduce the value stored in the variable by one.

Want to see if a RollBack is possible. See if the Variable is > 0. Want to see how many levels of nested transactions exist for a connection? Check the value in the variable.
[/b][/i][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[PogoWolf]

SemperFiDownUnda:
I completely agree with you.. however, the issue I was seeing in my code was that the function was erroring out BEFORE the 'BeginTrans' was called.. and the Error handling is built to clean up the connections, recordsets, and other objects before dieing gracefully. And that's where the problem was at.. If there was no Transaction set.. the program would die badly.. something that I couldn't have. =)

Thank you Both for the help!!! =)
As FYI, I did get some code that makes things work:

Dim iRecStatusOK As Integer
Dim iRSState As Integer
iRecStatusOK = RecordStatusEnum.adRecOK
If RS.Status <> iRecStatusOK Or RS.State Then
DBConn.RollbackTrans
End If


The PogoWolf

http://www.4TheGames.net

 

[CCLINT]

That will not work always the way that you expect. If the record is being edited, or any error happened, or whatever, but no transaction was started, the RollBack will be called. This is still not a good idea.

You also need to know how many times RollBack needs to be called. There could be several nested transactions.

And therefore, you should use a long variable when a transaction is started. [/b][/i][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[SemperFiDownUnda]

Issueing a Rollback Tran isn't needed in many cases. Let me explain.

If you have code and it crashes and you try to clean up and close the connection then all you need to do is close things.

If SQL server does not get a Commit before a connection is closed then it assumes a rollback.

Now in the middle tier this isn't always case because of connection pooling but you should be having those components as part of a MTS transaction and issueing GetObjectContext.SetAbort() and MTS will roll back all transaction involved with that current transaction.

Connection object itself only supports 1 transaction and 1 transaction level per connection object. Using multiple levels of transactions is HIGHLY discouraged for the amount of resources it requires on the server side.

So in the short, either in your error handler (should be its own procedure so you can do error handling) ignore (On Error Resume Next) the error or 2) don't issue a RollbackTran.

Error handlers should be VERY simple. Sad to say but SQL is made to have apps cut off from it and do the right thing. So what you could do is just &quot;END&quot; and SQL will clean itself up. Not saying do that, I would clean up, write a error log file, etc. But I wouldn't worry about issuing a conn.RollBackTran statement as I know that SQL will do that for me in all but 1 situation and I know how to tackle that situation as I described above.

Hope this was more helpful

 

[CCLINT]

>>&quot;Issueing a Rollback Tran isn't needed in many cases....&quot;
First of all, the questioner has not identified that SQL server is being used.

>>&quot;...Connection object itself only supports 1 transaction and 1 transaction level per connection object...&quot;
Secondly, you most certainly can have nested transactions on one connection - but not all servers support nested transactions, or even transactions at all.

Regardless if the provider can handle nested transactions or not, not issuing a RollBack, along with keeping track of transactions, is simply poor programing.

Some programs may be written with the capability to handle different providers, maybe where a clean up isn't handled properly, or at all.

So, keep track of transactions (if intialized and the nested level) regardless of the provider.

&quot;...(should be its own procedure so you can do error handling) ignore (On Error Resume Next) the error or 2) don't issue a RollbackTran.&quot;
I do not consider this to be a correct way, esp. when there is a cleaner way to check if a transaction has been started or not.

You need to take into consideration users who post questions here may not be using SQL server, or even using a database server as well, or one single particular provider. Keeping track, will solve all situations. [/b][/i][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[PogoWolf]

Whew.. lot's of feed back here.. I'm Useing Oracle as the back end.. BUt everything is being accessed though Visual Basic, and ADO.

Perhaps I need to give a little more information about the issue. Basicly what is happening is what'SemperFiDownUnda' was stating. I have Error code, that when called, issues the Rollback. Knowing that sometimes there's no transaction to roll back.. I wanted to know HOW to check to see if there's a transaction currently open.

I've not useing Transactions before, nor have am I 100% fluent in Orcale. However, I am aware of that I can NOT (and will not) have multiable transactions within my code. Nor can my program have more then one connection to the database itself.

However.. Knowing that the code posted above will not work in all cases (as testing has shown) the main question still hasn't really been answered... How does one check to see if there's a current (Active/Uncommited) transaction.

Would this problem be solved with nothing more then something simple as

Foo=objCONN.BeginTrans

and in the error code, check

IF foo = 1 then
objCONN.RollBack
else
objCONN.Close
End if






The PogoWolf

http://www.4TheGames.net