Err Clear form Begin.Transaction

[Maii]

Hi,
I have procedure like below
On Error GoTo ErrHandler
adoConnection.BeginTrans
few SQL Code
adoConnection.CommitTrans
exit sub
ErrHandler:
adoConnection..RollbackTrans
msgbox err.Number & “-“ & Err.Description
err.Clear

When I first time try and if any error occurred in sql code then shows error number , error description
And Rollback occurred.

Thereafter I second time try then the error in begin transaction was
Err.Number -2147168237
Err.Description Cannot start more transactions on this session

why the previous transaction was not clear?

thanks in advance
maii

 

[dreffed]

AS far as I know, and I code this way...
once you have used a transaction statement for a connection, and then done a rollback you have to close and reopen the connection to initialize a new transaction.

I hope this helps...



Dreffed

http://www.Dreffed.com

 

[chiph]

I do it this way:

Public Sub Somefunk()

  ' Dim variables, etc.

  On Error Goto Somefunc_ErrHandler

  ' Open connection, set up SQL statement, etc.

  adoConn.BeginTrans
  bInTrans = true

  ' Execute SQL

  adoConn.CommitTrans
  bInTrans = false

  Goto Somefunc_Cleanup

SomeFunc_ErrHandler:
  ' Save Error info for later
  sErrMsg = Err.Description
  sErrNum = Err.Number

  If Not adoConn Is Nothing Then
    If bInTrans Then
      adoConn.RollbackTrans
      bInTrans = false
    End If
  End If

  ' Log Error using saved error info

  ' Fall through into cleanup

Somefunc_Cleanup:
  ' Close connection, set object variables
  '  to Nothing, etc.
  
End Sub

What happens a lot of time is if you call into another function (like when logging the error) the Err object will be cleared, and your information is lost.

Using this style of error handling means that you no longer need to explicitly call Err.Clear. Using a local boolean variable to track whether or not you're inside a transaction (oddly, there's no property on the connection object to tell you this info), allows you to also handle errors that might ocurr outside the transaction (like when you initially open the connection).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[dreffed]

Hi ChipH

Nice bit of code, I hope it help Maii.

You could also check the err.number to see if this error is relevant to the transaction or something to do with your code

i.e.
SomeFunc_ErrHandler:
' Save Error info for later
if err.number = nnnn ' some transaction error
If Not adoConn Is Nothing Then
adoConn.RollbackTrans
End If
End If

' Log Error using saved error info

' Fall through into cleanup

Somefunc_Cleanup:


David


Dreffed

http://www.Dreffed.com