Help with debugging database bug 2

[graabein]

Hi, I'm looking for some advice on cornering an annoying bug I cannot recreate! I'll just paste the code here:

Public Sub CreateView( _
    lv As ListView, _
    lvTemp As ListView, _
    dtFromDate As Date, _
    dtToDate As Date, _
    Optional sFilter As String = "" _
)
    Dim rs As New ADODB.Recordset
    Dim nDebugStep As Integer
    On Error GoTo error_handler
        
    nDebugStep = 1
    If LoadViewByDate(dtFromDate, dtToDate, sFilter, rs) Then
        nDebugStep = 2
        '...snip...
    End If
    nDebugStep = -1
    CloseAndUnloadRecordset rs
    db.CloseConn
    Exit Sub

error_handler:
    CloseRecordsetAndConnection rs, db
    lv.Visible = True
    LogError "Error creating view.", _
        CStr(nDebugStep) & " Filter[" & sFilter & _
        "] FromDate[" & FormattedDate(dtFromDate) & _
        "] ToDate[" & FormattedDate(dtToDate) & "]", & _
        Err.Number, Err.Description
End Sub


Private Function LoadViewByDate( _
    dtFromDate As Date, _
    dtToDate As Date, _
    sFilter As String, _
    ByRef rs As ADODB.Recordset _
) As Boolean
    Dim cmd As ADODB.Command
    On Error GoTo LoadViewByDate_Error
    LoadViewByDate = False

    dtFromDate = DateOnly(dtFromDate)
    dtToDate = DateOnly(dtToDate)

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = db.Conn
        .CommandType = adCmdStoredProc
        .CommandText = "usp_ViewByDate"
        .Parameters.Append .CreateParameter("@UserID", adInteger, , , g_UserID)
        .Parameters.Append .CreateParameter("@UnitID", adInteger, , , g_UnitID)
        .Parameters.Append .CreateParameter("@FromDate", adDate, , , dtFromDate)
        .Parameters.Append .CreateParameter("@ToDate", adDate, , , dtToDate)
        .Parameters.Append .CreateParameter("@Filter", adVarChar, , 10, sFilter)
    End With
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenDynamic, adLockReadOnly
    If IsRecordsetOpen(rs) Then
        LoadViewByDate = True
    End If
    Set cmd = Nothing
    Exit Function
    
LoadViewByDate_Error:
    LogError "Error loading view from database.", _
        "UserID [" & g_UserID & "] UnitID [" & UnitID & _
        "] FromDate [" & FormattedDate(dtFromDate) & _
        "] ToDate [" & FormattedDate(dtToDate) & _
        "] Filter [" & sFilter & "]", & _
        Err.Number, Err.Description
End Function

Sometimes I get the first error message (Error creating view) in my log. Err.Number and Err.Description is empty and nDebugStep = 1.

Does this mean the error occurs in LoadViewByDate? How come the outer error handler catches it? Help me please!


graabein

 

[jadams0173]

You may consider using mztools to add line numbers to your code and use erl to pinpoint the line and procedure that could be causing your problems.

 

[graabein]

Thanks, I have mztools installed but wasn't aware of Erl!


graabein

 

[TysonLPrice]

If you can make it occur in IDE then in tools, options, general select break on all errors. That may stop it right on your error.

 

[graabein]

The bug occurs in production and I can't recreate it on my development environment.


graabein

 

[JoeAtWork]

It's because you are calling the CloseRecordsetAndConnection subroutine within your error handler. As soon as you do any operation that is successful, the Err object gets cleared.

For example:

Private Sub Command1_Click()
    HasErrHandler
End Sub

Private Sub HasErrHandler()
    Dim j As Integer
    
    On Error GoTo ErrHandler
    
    j = 5 / 0       'Division by Zero!
        
    Exit Sub
    
ErrHandler:
    AnotherSub  'This call will clear the Err object
    MsgBox "Err # " & Err.Number & ", " & Err.Description, , "HasErrHandler"
End Sub


Private Sub AnotherSub()
    Dim j As Integer
    
    On Error GoTo ErrHandler
    
    j = 5 / 1
    
    Exit Sub
    
ErrHandler:
    MsgBox "Err # " & Err.Number & ", " & Err.Description, , "AnotherSub"
End Sub

When HasErrHandler is called, it causes an error because it tries to do division by zero. However, in it's error handler, it then calls AnotherSub that which does a calculation that is perfectly acceptable, and because it succeeds the Err object is cleared. When it returns from its call to AnotherSub, the Err object no longer has the original error caused in HasErrHandler.

Best practice in your error handlers is to capture the context of the error immediately to variables, something like:

error_handler:
    Dim errNum as Long
    Dim errDesc as String

    errNum = Err.Number
    errDesc = Err.Description

    CloseRecordsetAndConnection rs, db
    lv.Visible = True
    LogError "Error creating view.", _
        CStr(nDebugStep) & " Filter[" & sFilter & _
        "] FromDate[" & FormattedDate(dtFromDate) & _
        "] ToDate[" & FormattedDate(dtToDate) & "]", & _
        errNum, errDesc

 

[graabein]

Thanks Joe, I'll update my code with that. Another thing, can I get the line number with Erl?

error_handler:
    Dim errNum As Long
    Dim errDesc As String
    Dim lineNum As Long

    errNum = Err.Number
    errDesc = Err.Description
    lineNum = Erl

graabein

 

[TysonLPrice]

You have to add the line numbers in to capture them. MZtools has a function that will number your entire project at once. It will also un-number it in mass.

http://www.mztools.com/index.aspx

 

[jadams0173]

Like Tyson explained you have to number the procedure using MZtools. Have you been able to do that?

 

[graabein]

Not yet. Been busy working on another project. I've bookmarked this page for later reference.


graabein

 

[HarleyQuinn]

I thought you already had MZTools installed?

If you have, it's as easy as Tools-->MZ-Tools-->Add Line Numbers

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post