Monday, January 5, 2009

Script Code: Basic VBscript Error Handler

This is really basic and simple.  If you do a fair amount of scripting you will not find this to be very advanced I'm sure.  But for anyone just starting out, it might help for including in your script files to make a more standard error handler easier to use.

Sub ErrorTrap (strMsg)
    If err.Number <> 0 Then
        wscript.echo "message: " & strMsg
        wscript.echo "error: " & err.Number & ": " & err.Description
        err.Clear
        wscript.Quit
    End If
End Sub

Example of usage:

    On Error Resume Next
    strUser = "johndoe"
    Set objUser = GetObject("WinNT://contoso/" & strUser & ",user")
    ErrorTrap "user " & strUser & " not found"
    objUser.AccountDisabled = True
    objUser.SetInfo()
    Set objUser = Nothing

The key here is to be sure to use On Error Resume Next just before the line where you expect that something might fail.  Then immediately follow that line with the ErrorTrap call.  If you don't use On Error Resume Next, the line that results in an error will fail and send the result directly to the script engine (wscript or cscript).  The downside is that you may not be able to insert your own tips to identify which line caused the error.  Consider the following example with a database connection.  There are multiple points that can fail in such cases.  The connection.  The recordset request.  And so on.  Assume for this example that the constants (in purple) are defined within the script...

On Error Resume Next

Set objConn = CreateObject("ADODB.Connection")
Set objCmd CreateObject("ADODB.Command")
Set objRS CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM hr.employees WHERE emp_active=1 ORDER BY empid"

objConn.Open str_dsn_connection
ErrorTrap "ado-connection-open"

objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenDynamic
objRS.LockType = adLockPessimistic

Set objCmd.ActiveConnection = objConn

objCmd.CommandType = adCmdText
objCmd.CommandText = strSQL
objRS.Open objCmd
ErrorTrap "ado-recordset-open"

If objRS.BOF And objRS.EOF Then
    objRS.Close
    objConn.Close
    Set objRS = Nothing
    Set objCmd = Nothing
    Set objConn Nothing
    ErrorTrap "no records were found"
End If

cols objRS.Fields.Count
rows objRS.RecordCount

Do Until objRS.EOF
     empid objRS("empid").Value
     lname objRS("lname").Value
     fname objRS("fname").Value
     objRS.MoveNext
Loop

objRS.Close
objConn.Close
Set objRS = Nothing
Set objCmd = Nothing
Set objConn = Nothing

The caveate here would be that to do this properly, you would add some lines into the ErrorTrap code to close and release the database object variables.  Otherwise, if it crashed out within the Do-Until loop, it would leave an open connection to the database which is not good.

If you leave out the ErrorTrap use, it will likely crash with the following error:

-2147467259: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

But with the ErrorTrap code, it crashes something like this...

message: ado-connection-open
error: -2147467259: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

As I said, this is pretty basic, but often helpful.  I hope it helps you as well.
Post a Comment