VBScript Newb: Err.Description Returns Empty String, Making Error Log Ineffective-CodePudding

What I'm trying to do:

Pass a series of integer variables in VBScript to a SQL Server INSERT INTO statement. This is part of a nightly-run script, handled by Windows Scheduler, to grab data from a Cerner-based system and insert it into a table in my web app's (we'll call it "MyApp's") own database. I'm currently running the script in "test mode," manually, through Command Prompt.

The problem:

I get errors firing at On Error Resume Next, and each time, I have the code WriteLine to the log file: cstr(now) & " - Error occurred on [insert argument here]: " & Err.Description.

However, every time, in every instance, Err.Description is just an empty string and doesn't tell me what's going on.

The code snippet:

    Set DataConnExt = CreateObject("ADODB.Connection")
    DataConnExt.CommandTimeout = 90
    DataConnExt.ConnectionTimeout = 90
    If testmode then                                        'tesmode = True, in this case
        sDsn = "MyAppTST"
    else
        sDsn = "MyApp"
    End If
    sUser = "pseudonym"
    sPWD = "***********"
    On Error Resume Next

    If testmode then
        objErrLogFile.WriteLine "   "
        objErrLogFile.WriteLine cStr(now()) & " Error occurred on connection to MyApp_DB: "
        objErrLogFile.WriteLine "   " & Err.Description
    End If

    DataConnExt.Open "DSN=" & sDSN , sUser, sPWD

    If testmode then
        MidnightMailingLog.WriteLine cstr(now) & " - MyApp Export Query: " & sql
    End If

    errMessage = ""

    If Err.Number <> 0 then
        errMessage = Err.Description
        errDesc = "ERROR occurred while executing export to MyApp DB: " & errMessage
        LogError(errDesc)
        SendErrorNotificationEmail              
        WScript.Quit 99
    End If

    DataConnExt.Close
    Set DataConnExt = Nothing
    rsExport.Close
    Set rsExport = Nothing
    
    On Error GoTo 0

What the error log says:

12/28/2021 12:03:22 PM Error occurred on connection to MyApp_DB: 
   
12/28/2021 12:03:22 PM - ERROR occurred while executing export to MyApp DB:

Context:

I'm fairly new to VBScript, but have been developing in VB.Net environments for over 5 years, so it's not completely foreign to me.
The snippet you see here is code I copied over from a file in which the routine works, modified for my specific connection.
The SQL works correctly, as I had the code write the query string to the script's main log, then pasted it into Sql Server Management Studio, executed it, and voila, the new row was inserted successfully.
The connection to my SQL Server database is through ODBC, which passes its connection test every time I run it.
The function SendErrorNotificationEmail runs correctly, as I do receive the email. However, it also has its own instance of On Error Resume Next that does fire, and it too has the same commands to write cStr(now()) & " error occurred during email notification of script error: " & Err.Description to the log file if Err.Number <> 0, and the rest of the error log reads as follows:

12/28/2021 12:03:22 PM error occurred during email notification of script error:

Update - 12/28, 4:00pm

Credit to @DavidBrowne-Microsoft for helping me restructure some things and reduce redundant Error handling. Below is a revision of my code. The good news is, the INSERT statement is now successfully being executed when I run the script. The bad news: Err.Number is still <> 0, and still no Description.

One thing I DID learn is that WScript.Quit shouldn't have been there. The whole file is now structured so that my code runs last, and correctly adds its Errors to the error log; upon test-running, the only Errors found were my "phantom Errors."

The revised code:

' (Beginning of Sub -- Everything you see here is within an "If testmode" condition)
' ...

    On Error Resume Next

    ExportDataToMyApp(sqlA)

    If Err.Number <> 0 then
        errMessage = Err.Description & "; Err.Number: " & Err.Number
        errDesc = "ERROR occurred while executing export to MyApp DB: " & errMessage 
        LogError(errDesc)
        ErrCnt = ErrCnt   1             'necessary for error logging
        'SendErrorNotificationEmail     'don't worry about this one     
    Else
        MidnightMailingLog.WriteLine cstr(now) & " - SQL Server INSERT to MyApp executed successfully."
    End If

    CloseObjects()

End Sub

' *********************************

Sub ExportDataToMyApp(ByRef sql)
    Set DataConnExt = CreateObject("ADODB.Connection")
    DataConnExt.CommandTimeout = 90
    DataConnExt.ConnectionTimeout = 90
    If testmode then
        sDsn = "MyAppTST"
    else
        sDsn = "MyApp"
    End If
    sUser = "pseudonym"
    sPWD = "***********"

    DataConnExt.Open "DSN=" & sDSN , sUser, sPWD

    ' ACTUALLY, I THINK THE FIX FOR THE INSERT STATEMENT WAS HERE, BECAUSE I WAS REFERENCING THE WRONG sql VARIABLE (there are several in this vbs file).
    Set rsExport = DataConnExt.Execute(sql,Recs,1)  

End Sub

' **************************

Sub LogError(eDesc)
    objErrLogFile.WriteLine "   "
    objErrLogFile.WriteLine(cstr(now()) & " - " & eDesc)
End Sub

' ***************************************

Sub CloseObjects()
    rsExport.Close
    Set rsExport = Nothing

    DataConnExt.Close
    Set DataConnExt = Nothing
End Sub

The error log:

   
12/28/2021 2:52:59 PM - ERROR occurred while executing export to MyApp DB:

CodePudding user response：

Turn off ON ERROR RESUME NEXT everywhere except in your outermost script. So something like:

sub LogError(error)
  WScript.Echo error
end sub

    
sub Run(testmode)

    Set DataConnExt = CreateObject("ADODB.Connection")
    DataConnExt.CommandTimeout = 90
    DataConnExt.ConnectionTimeout = 90
    If testmode then                                        'tesmode = True, in this case
        sDsn = "MyAppTST"
    else
        sDsn = "MyApp"
    End If
    sUser = "pseudonym"
    sPWD = "***********"

    DataConnExt.Open "DSN=" & sDSN , sUser, sPWD

    If testmode then
        MidnightMailingLog.WriteLine cstr(now) & " - MyApp Export Query: " & sql
    End If


    DataConnExt.Close
    Set DataConnExt = Nothing
    rsExport.Close
    Set rsExport = Nothing
    
end sub

on error resume next
Run(True)
If Err.Number <> 0 then
    errMessage = Err.Description
     errDesc = "ERROR occurred while executing export to MyApp DB: " & errMessage 
     LogError(errDesc)
     'SendErrorNotificationEmail              
     WScript.Quit 99
End If

CodePudding user response：

David Browne was not kidding when he says that "Error handling is probably the worst thing about VBScript." It is a fickle, fickle creature.

Come to find out: there was no Error at all. Here are the steps I took to solve the problem:

I moved the Error handling BACK into the dedicated Sub ExportDataToMyApp.
I split up the Error handling and added On Error GoTo 0 after each time. It now handles DataConnExt.Open and DataConnExt.Execute separately.
Instead of dealing with Err.Number, I found a working example of using Select Case Err, with only two cases: 0 and Else.
I only called the CloseObjects Sub within the Error handlers.

Revised and working solution:

' (Beginning of Sub -- Everything you see here is within an "If testmode" condition)
' ...

    ExportDataToMyApp(sqlA)

End Sub

' *********************************

Sub ExportDataToMyApp(ByRef sql)
    Set DataConnExt = CreateObject("ADODB.Connection")
    DataConnExt.CommandTimeout = 90
    DataConnExt.ConnectionTimeout = 90
    If testmode then 
        sDsn = "MyAppTST"
    else
        sDsn = "MyApp"
    End If
    sUser = "pseudonym"
    sPWD = "***********"

    On Error Resume Next
    DataConnExt.Open "DSN=" & sDSN , sUser, sPWD
    Select Case Err
        Case 0
            MidnightMailingLog.WriteLine cstr(now) & " - Open Connection to MyApp_DB executed successfully."
        Case Else
            LogError(Err.Description)
            ErrCnt = ErrCnt   1
            SendErrorNotificationEmail
            On Error Goto 0
            CloseObjects
    End Select
    On Error GoTo 0

    On Error Resume Next
    Set rsExport = DataConnExt.Execute(sql,Recs,1)
    Select Case Err
        Case 0
            MidnightMailingLog.WriteLine cstr(now) & " - SQL Server INSERT to MyApp executed successfully."
        Case Else
            LogError(Err.Description)
            ErrCnt = ErrCnt   1
            SendErrorNotificationEmail
            On Error Goto 0
            CloseObjects
    End Select
    On Error Goto 0

End Sub

Lo and behold, the error log file vanished from the folder, the database table was still accepting new rows, and all "success" lines were written to the log. Thank you to all who helped.