Visualize - ASP Tips (Active Server Pages tips and hints on ASP and IIS

home

resources

asp tips

SQL error handling

tips menu | printable single page version

Note: this is my old ASP tips page and is no longer supported. See the new Visualize web site..

ASP Coding and Style - SQL Execution Error Handling

All SQL executions should have some form of error handling, and call a standard error handler in an include file.

Example code could be:


 <!--#INCLUDE FILE='error.inc'--><%
 
 Dim oDBConn, rsTest, sSQL
 Set oDBConn = Server.CreateObject("ADODB.Connection")
 oDBConn.Open "TestDSN"
 
 sSQL = "SELECT Field1, Field2 " &_
        "FROM MyTable "
 
 On error resume next              'catch ODBC/SQL errors in a minute
 Set rsTest = oDBConn.Execute(sSQL)
 On error goto 0                   'error handling back on
 
                                   ' now check for ODBC errors
 IF oDBConn.Errors.Count <> 0 then
     Call MyErrorHandler(oDBConn, sSQL)  ' pass database connection as param
 Else
     '.... process results
     '.... etc
 End If
 
 rsTest.Close
 oDBConn.Close
 Set rsTest = Nothing     ' Free up memory
 Set oDBConn = Nothing

and the actual error handler function itself in the include file can take many forms. As a minimum it could simply output details of the error in the page itself, for example:


Public Sub MyErrorHandler(oDBConn, sSQL)
  Response.Write "An error has occurred.<BR>"
  Response.write "SQLstate=" & oDBConn.Errors(0).sqlstate & "<BR>"
  Response.write "Description=" & oDBConn.Errors(0).Description  & "<BR>"
  Response.write "NativeError=" & oDBConn.Errors(0).NativeError & "<BR>"
  Response.write "SQL=" & sSQL & "<BR>"
  Response.Write "Please telephone the Web Support Team on tel no xxxx"
  On Error Resume Next
  oDBConn.Close
  Set oDBConn = nothing
  On Error goto 0
  Response.End
End Sub

Of course, this routine can be as complicated as you wish. In the past I have written error handlers which initially redirects to a neat user-friendly form. This then gathers some additional user details and informs the web team automatically via email, with error logging, giving full technical details of the error, all hidden from the end-user.

DISCLAIMER: Note these pages are a free resource for anyone wishing to reference them. Although every care is taken to ensure their correctness, the author takes no responsibility for any errors or problems that may occur through their use, or indeed misuse. These pages are copyight of Dave Clarke, Visualize Software Ltd 1997-2000 (all rights reserved).