VB6 ADO Connection - How to Check if in Transaction?

Question

Is there any way to tell, using just the ADODB.Connection object, whether or not it is currently involved in a transaction?

I'd love to be able to test this on the connect object itself, without relying on keeping a boolean next to it updated.

Solution 1

The BeginTrans method can be used as a function that returns the nesting level of the transaction. If you create a property to store this you can check it where ever you need to to see if it is greater than 0. When you commit or rollback you will need to decrement the property yourself.

Private m_TransLevel As Long

Public Property Get TransactionLevel() As Long
    TransactionLevel = m_TransLevel
End Property
Public Property Let TransactionLevel(vLevel As Long)
    m_TransLevel = vLevel
End Property

Public Sub SaveMyData()

    TransactionLevel = adoConnection.BeginTrans()
    ...

End Sub

You could also adapt the return value to work inside a function that returns True/False if the level > 1. I don't like this as well, but it would look something like this (without error handling)

Public Function IsConnectionInsideTransaction(ByVal vADOConnection as ADOBD.Connection) As Boolean
    Dim intLevel As Integer

    If vADOConnection.State = AdStateOpen Then
        intLevel = vADOConnection.BeginTrans()
        IsConnectionInsideTransaction = (intLevel > 1)
        vADOConnection.RollbackTrans
    End If

End Function

Solution 2

If you're connecting to an Microsoft SQL Server and can count on it to respond fast enough (i.e. it's not on the other side of the planet) you can perform the query:

SELECT @@TRANCOUNT

Comments

[Tom Tresansky]

I don't think you can. This was the first place I looked, and it looks like the state values don't have anything to say about being in a transaction or not. See: msdn.microsoft.com/en-us/library/ms675546%28v=VS.85%29.aspx