Handling an ODBC error

The ODBCConnection, ODBCQuery, and ODBCResultSet classes all contain the following methods:

  • GetError returns the integer error number for the last operation performed by an object of that class.
  • GetErrorMessage returns the error text for the last operation performed by an object of that class.
  • GetExtendedErrorMessage returns extended error text for the last operation performed by an object of that class. If the error is originally reported by the data source, the extended error text contains the data source error message.

These methods are documented under ODBCQuery since they are the same for all three classes. The GetErrorMessage topic documents the error codes and associated error text for each error and provides examples. The documentation for each property and method provides error codes and text for the error paths it contains.

If an operation is successful, the return number is DBstsSUCCESS. Any other number indicates an error.

You can test operations on an individual basis by calling the above methods after each operation. The following code shows the technique for checking the last operation and exiting if an error occurred:

con.ConnectTo("ATDB")
If con.GetError <> DBstsSUCCESS Then
  Messagebox con.GetExtendedErrorMessage,, _
  con.GetError & " " & con.GetErrorMessage
  Exit Sub
End If
qry.SQL = "SELECT * FROM STUDENTS"
If qry.GetError <> DBstsSUCCESS Then
  Messagebox qry.GetExtendedErrorMessage,, _
  qry.GetError & " " & qry.GetErrorMessage
  Exit Sub
End If
result.Execute
If result.GetError <> DBstsSUCCESS Then
  Messagebox result.GetExtendedErrorMessage,, _
  result.GetError & " " & result.GetErrorMessage
  Exit Sub
End If

You can test operations in general with an On Error statement. If the On Error action with no error number is in effect, any operation that returns an error number other DBstsSUCCESS invokes the On Error action. Your error code should test GetError for all objects that may cause the error. The following code shows the technique for trapping any ODBC error and exiting:

  On Error Goto errorHandler
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM STUDENTS"
  result.Execute
  ...
errorHandler:
  If con.GetError <> DBstsSUCCESS Then
    Messagebox con.GetExtendedErrorMessage,, _
    con.GetError & " " & con.GetErrorMessage
  End If
  If qry.GetError <> DBstsSUCCESS Then
    Messagebox qry.GetExtendedErrorMessage,, _
    qry.GetError & " " & qry.GetErrorMessage
  End If
  If result.GetError <> DBstsSUCCESS Then
    Messagebox result.GetExtendedErrorMessage,, _
    result.GetError & " " & result.GetErrorMessage
  End If
  Exit Sub

The default On Error action is Resume Next. If you don't explicitly check for errors and an error occurs, the script continues with undefined results.