12.6.3- The Errors Collection

by NT Community Manager.
Last Updated by Jim Minatel.

Public Categorized as 12. ASP and Data Store Access.

Not tagged.

The Errors Collection

The Connection object's Errors collection contains all errors that have been created in response to a single failure. You'll probably find that you use the Errors collection far more often than the Properties collection. The words single failure in this definition are quite important, because when an ADO operation generates an error, the Errors collection is cleared before the new error details are inserted. So, in other words, your code could contain two or more separate operations that failed, but the Errors collection might only contain one Error object, containing details of the error that occurred last. The previous ones will have been overwritten. In fact the only time multiple Error objects may be returned is if the same line of code generated the errors. Even then Access will only generate one, but you will find that MSDE/SQL Server generates more than one.

The Errors collection contains Error objects, in much the same way as the Properties collection contains Property objects. Each Error object contains several properties that you'll need when looking at errors:

Property	Description
Number	The number of the error
Description	The description for the error
Source	Identifies the object that raised the error
SQLState	Holds the SQL error code
NativeError	Holds the database-specific error code

Using these properties you'll be able to find out, in more detail, what error occurred so that this can be reported back to the user. Let's see how to do this.

Try It Out – The Errors Collection

1. Open up your editor, create a new file and add the following HTML and script:

Option Explicit

Dim strConnect

<HTML>

<HEAD>

<TITLE>ADO Errors</TITLE>

</HEAD>

<BODY>

On Error Resume Next

Dim objConn ' Connection object

Dim objProp ' Property object

Dim objError ' Error object

' create the connection object

Set objConn = Server.CreateObject ("ADODB.Connection")

' and open it

objConn.Open strConnect

' now we can execute some SQL

objConn.Execute "SELECT MissingColumn FROM MissingTable"

' Errors means the count will be greater than 0

If objConn.Errors.Count > 0 Then

' loop through the errors

For Each objError in objConn.Errors

Response.Write "<TABLE BORDER=1>" & _

"<TD>Error Property</TD>" & _

"<TD>Contents</TD>" & _

"<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 & "</TD></TR>" & _

"</TABLE><P>"

Else

' no errors

Response.Write "There were no errors."

End If

' now close and clean up

objConn.Close

Set objConn = Nothing

</BODY>

</HTML>

2. Save the code as ConnErrs.asp.

3. Ensure that the SSI file DataStore.asp, which we gave earlier in this chapter, is also in the same folder.

4. Type the URL into your browser, and view the page.

Chapter12_image026

This is exactly what we expect to happen, because there's a deliberate error in the code. Whilst the SQL statement we executed is syntactically correct, it's requesting a non-existent column in a non-existent table.

How It Works

Once again we start with the include file, and give the .asp file a header:

Option Explicit

Dim strConnect

<HTML>

<HEAD>

<TITLE>ADO Errors</TITLE>

</HEAD>

To ensure that the ASP script is not terminated when an error occurs, we need to use the On Error statement. This is similar to one that you may have seen used in Visual Basic and VBA, but has less flexibility. All we can do is Resume Next, to continue processing at the next statement:

<BODY>

On Error Resume Next

Next we declare the variables. This time we declare a variable that will represent an Error object. Then we create the connection and open it:

Dim objConn ' Connection object

Dim objProp ' Property object

Dim objError ' Error object

' create the connection object

Set objConn = Server.CreateObject ("ADODB.Connection")

' and open it

objConn.Open strConnect

Then we can execute our SQL statement, which contains the deliberate error. We haven't covered the Execute statement yet – this will be part of the next chapter . For now, we'll just use it to force the error:

' now we can execute some SQL

objConn.Execute "SELECT MissingColumn FROM MissingTable"

The Count property of the Errors collection tells us how many errors there have been. We can test this before going into our error display section of code:

' Errors means the count will be greater than 0

If objConn.Errors.Count > 0 Then

If there are errors, we can start looping through the Errors collection, using the same technique as we used on the Properties collection. We build up an HTML table containing all of the error information that we need to see:

' loop through the errors

For Each objError in objConn.Errors

Response.Write "<TABLE BORDER=1>" & _

"<TD>Error Property</TD>" & _

"<TD>Contents</TD>" & _

"<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 & "</TD></TR>" & _

"</TABLE><P>"

If there are no errors, we can display a message that says so:

Else

' no errors

Response.Write "There were no errors."

End If

This won't happen, because we know that there's a deliberate error in there! But we include this for completeness.

Finally, we clean up and close the connection:

' now close and clean up

objConn.Close

Set objConn = Nothing

</BODY>

</HTML>

A Generic Error Routine

You could turn this error display into a separate SSI file of its own, which could then be #included into any .asp file. It's a good practice to generalize and modularize code that you're likely to use repeatedly – this makes it easier to maintain and debug larger applications. Anything that makes your life easier has got to be worth a go!

Try It Out – A Generic Error Routine

1. Start up your editor, and type in the code for the following include file:

Sub CheckForErrors (objConn)

' Errors means the count will be greater than 0

If objConn.Errors.Count > 0 Then

' loop through the errors

For Each objError in objConn.Errors

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 & "</TD></TR>" & _

"</TABLE><P>"

End If

End Sub

2. Save the code, with the name Errors.asp, into the \inetpub\wwwroot\BegASPFiles folder.

We're going to use this as an SSI in other ASP pages. Note that, once again, we're using the .asp suffix for the SSI's filename. This ensures that anyone stumbling on this file via a browser will only see the version parsed by the ASP engine – so our ASP source code is safe.

3. Ensure that the SSI file DataStore.asp, which we gave earlier in this chapter, is also in the same folder.

4. Now open up a new file, and type in the following code (it's the same as ConnErrs.asp, except at the shaded lines):

Option Explicit

Dim strCOnnect

<HTML>

<HEAD>

<TITLE>ADO Errors, Part 2</TITLE>

</HEAD>

<BODY>

On Error Resume Next

Dim objConn ' Connection object

Dim objProp ' Property object

Dim objError ' Error object

' create the connection object

Set objConn = Server.CreateObject ("ADODB.Connection")

' and open it

objConn.Open strConnect

' now we can execute some SQL

objConn.Execute "SELECT MissingColumn FROM MissingTable"

' now check for errors

CheckForErrors (objConn)

' now close and clean up

objConn.Close

Set objConn = Nothing

</BODY>

</HTML>

5. Save this in the same directory, with the name UseErrors.asp.

6. Call up the page UseErrors.asp from your browser. You'll see that this has the same result as before. Let's see how it works.

How It Works

Let's first look at Errors.asp. Here, we define a sub-procedure called CheckForErrors(), which has one argument – objConn. This will be used to hold the Connection object:

Sub CheckForErrors (objConn)

Now we can check how many errors are in the collection:

' Errors means the count will be greater than 0

If objConn.Errors.Count > 0 Then

If there are errors, create a table as before:

' loop through the errors

For Each objError in objConn.Errors

...

That's all there is to it. Notice that the SSI file doesn't open the connection, and so it doesn't close the connection either: responsibility for this is taken by the calling ASP page.

The UseErrors.asp file is familiar too. The first thing you notice is that we include two files – DataStore.asp for the connection details, and Error.asp for the error routine.

Much of the rest of the code is the same as that in ConnErrs.asp. The only other difference is at the point where we call the new error routine, passing in our open connection:

' now check for errors

CheckForErrors (objConn)

This gives us the same result as before. Easy, huh? Moreover, you now have a simple generic error routine that you can use from any .asp file to display information about your ADO errors. This saves having to write it each time, and makes it easier to update.

<< 12.6.2- The Properties Collection

Chapter12

12.7.0- Summary >>