Programming | Advanced 8.5.1 Errors Collection
The Errors collection contains an Error object for each error that is caused by the execution of a single ADO command. The reason for using the Errors collection is that the OLE DB provider needs to provide a way to notify the customer that more than one error has occurred while the execution of a command may cause multiple errors.
There are two important areas to note about the Errors collection:
· Each time you execute an ADO command, if an error occurs, empty the set of errors and replace them with the new error content. Of course, the Errors collection will not be affected if no errors occur. Therefore, even if the ADO command executes successfully, the collection may contain error messages.
· An OLE DB provider might load a message or warning containing information into the Errors collection with an error number of 0. So you can't just check the error number in the collection and assume that the error has occurred. For example, using an ODBC provider to connect to SQL Server may get an "error" telling the default database has changed.
If you look back at the object model that this chapter starts with, you may find that the Errors collection can only be accessed by the Connection object. Readers may find it strange that if you do not explicitly create a connection object, how do you access the Errors collection? The Recordset object has a ActiveConnection property that contains the connection object of the current Recordset, which means that the Errors collection can be obtained as follows:
RsAuthors.ActiveConnection.Errors
If you want to see all the errors that occur, you need to traverse the entire errors collection:
For each errauthors in RsAuthors.ActiveConnection.Errors
' Display error
Next
In order to display some reasonable error messages, you need to know exactly what is in the Errors collection.
The Error object contains the properties shown in table 8-5:
Table 8-5 the properties and description of the Error object
Property
Description
Number
ADO Error number
NativeError
Error number obtained from the data provider
SQLState
5-bit SQL status code when connecting to a SQL database
Source
The object that caused the error
Description
Error description text
This means that the loop process can now become this:
For each errauthors in RsAuthors.ActiveConnection.Errors
Response.Write "Number:" & Errauthors.number & _
"<br>nativeerror:" & Errauthors.nativeerror & _
"<br>sqlstate:" & Errauthors.sqlstate & _
"<br>source:" & Errauthors.source & _
"<br>description:" & Errauthors.description & _
"<P>"
Next
ADO errors in 8.5.2 ASP pages
In chapters 4th and 7, we examine the ASP's errors and how to deal with them succinctly and thoroughly. A new feature of ASP 3.0 is the custom error page, but this is not really useful for ADO because variables in the script cannot pass in the custom error page. This means that we are unable to detect the Errors collection.
In the face of such a situation, you must provide your own error handling. If you use JScript as a server-side programming language, you will have new Try/catch features, but VBScript still has a lot to do with error handling. Currently, the best way to detect errors is to use the On Error Resume Next statement, and then check the Errors collection after each line of ADO code that might cause an error. Just like this:
<%
On Error Resume Next
Dim rsauthors
Dim strSQL
Set rsauthors = Server.CreateObject ("ADODB. Recordset ")
strSQL = "Select MissingColumn1, MissingColumn2, au_lname, au_fname" & _
"From authors"
Rsauthors.open strSQL, strconn, adOpenDynamic, adLockOptimistic, adCmdText
If checkforerrors (rsauthors.activeconnection) = False Then
While not rsauthors.eof
Response.Write rsauthors ("au_lname") & "," & _
Rsauthors ("au_fname") & "<BR>"
Rsauthors.movenext
Wend
End If
Rsauthors.close
Set rsauthors = Nothing
%>
Here you can use the Checkforerrors subroutine to detect if an error occurred:
Function checkforerrors (objconn)
Dim objerror ' Error object
' Errors means the count would be greater than 0
If objConn.Errors.Count > 0 Then
' Loop through the errors
For each objerror in Objconn.errors
' Eorros with number 0 are informational
If objerror.number <> 0 Then
Response.Write "<table border=1>" & _
"<tr><td>error property</td><td>contents</td>" & _
"</TR><TR><TD>Number</TD><TD>" & Objerror.number & _
"</TD></TR><TR><TD>NativeError</TD><TD>" & _
Objerror.nativeerror & "</TD></TR>" & _
"<TR><TD>SQLState</TD><TD>" & Objerror.sqlstate & _
"</TD></TR><TR><TD>Source</TD><TD>" & _
Objerror.source & "</TD></TR>" & _
"<TR><TD>Description</TD><TD>" & _
Objerror.description & "<
[1] [2] [3] Next page