Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]  Debug variables Easy Way
     [next Lesson]  DBFAQ: Operation must use Updatable Query

Error Trapping Database Code (by Charles Carroll)

Before you use http://www.asplists.com and send a mail to get your question answered let us take a couple of minutes and try the following and see if it identifies your problem. There are tons of frightening messages that come up like:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression.
/somewhere/something.asp, line 12		 Error #-2147217900
Error desc. -> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','.		 Error #-2147217900
Error desc. -> [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in UPDATE statement.

Here are our guidelines for getting to the heart of the matter. First add some error code to the script to display the messages and bad SQL that causes the problem, for example:

   filename=/learn/test/dbtroubleshoot.asp

<Test Script Below>


<html><head>
<title>dbtroubleshoot.asp</title></head>
<body>
<!--#include file="lib_errors.asp"-->
<%
on error resume next
Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=student;uid=student;password=magic"

SQLstmt = "INSERT INTO junk (city,state,zip) VALUES ('Rockville','MD','20849')"
Set RS = Conn.Execute(SQLStmt)

Call ErrorVBScriptReport("Insert Statement")
Call ErrorADOReport(SQLstmt,conn)

rs.close
set rs=nothing
Conn.Close
set conn=nothing%>

</body></html>

Here is the include file that displays appropriate errors:

   filename=/learn/test/lib_errors.asp

<Test Script Below>


<%
SUB ErrorVBScriptReport(parm_msg)
    If  err.number=0 then
        exit sub
    end if
    pad="&nbsp;&nbsp;&nbsp;&nbsp;"
    response.write "<b>VBScript Errors Occured!<br>"
    response.write parm_msg & "</b><br>"
    response.write pad & "Error Number= #<b>" & err.number & "</b><br>"
    response.write pad & "Error Desc.= <b>" & err.description & "</b><br>"
    response.write pad & "Help Context= <b>" & err.HelpContext & "</b><br>"
    response.write pad & "Help File Path=<b>" & err.helpfile & "</b><br>"
    response.write pad & "Error Source= <b>" & err.source & "</b><br><hr>"
END SUB

SUB ErrorADOReport(parm_msg,parm_conn)
    HowManyErrs=parm_conn.errors.count
    IF  HowManyErrs=0 then
        exit sub
    END IF
    pad="&nbsp;&nbsp;&nbsp;&nbsp;"
    response.write "<b>ADO Reports these Database Error(s) executing:<br>"
        response.write SQLstmt & "</b><br>"
    for counter= 0 to HowManyErrs-1
        errornum=parm_conn.errors(counter).number
        errordesc=parm_conn.errors(counter).description
        response.write pad & "Error#=<b>" & errornum & "</b><br>"
        response.write pad & "Error description=<b>"
        response.write errordesc & "</b><p>"
    next
END SUB
%>

There is a set of links to Microsoft ADO knowledge base articles @
http://www.asptracker.com/demo/adokb1.asp
which may prove invaluable.

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.