End Sub

Function GetRecords() As DataSet Try

Dim connection as New OleDBConnection(txtConnStr.Text)

Dim command as New OleDBDataAdapter(txtSQLStr.Text, connection) Dim dataset As New DataSet()

command.Fill(dataset, “dataset”) Return dataset

Catch myException as OLEDBException OLEDBErrorHandler(myException)

End Try End Function

The GetRecords() function pulls the connection string from a textbox txtConnStr and the SQL statement from a textbox txtSQLstr. Next it attempts to fill the dataset and then return it. Whenever an error occurs in the ADO.NET Adapter, for instance if the password or server is invalid, or an incorrect statement is encountered in the SQL statement, then an exception is thrown. Using the Try . . . Catch statement, you can catch the OLEDBException and then begin the process of handling and evaluating the errors collection that it contains. This is a much more convenient solution than we had in ASP for capturing and passing a collection of errors. The ability for the OLEDBException class to act as a wrapper for the OLEDBErrors collection, makes passing the collection to a generic error handler much more simple.

In the next section, we will expand on the above code example and build an OLEDBErrorHandler() function in order to examine the ways that we can evaluate the errors collection.

OLEDBError Object Description

You can sort through the OLEDBException object to work with each of the OLEDBError objects it wraps. The OLEDBError class is created by the OleDBDataAdapter whenever an error occurs in connecting to or executing against a datasource. For each error encountered by the OleDBDataAdapter, a new OLEDBError object is instantiated and added to the OLEDBErrors collection, which is then wrapped by the OLEDBException class and instantiated as an object.

Listing 26-2, extends Listing 26-1 to illustrate how the OLEDBException object is passed to a generic error handler function OLEDBErrorHandler. The function can then process the errors collection. First, we dynamically add a label to the bottom of the page so that we can display the list of error messages. We will use the label to hold the text of our

OLEDBException and OLEDBError properties.

Listing 26-2 Passing an OLEDBException to an error handler

Function OLEDBErrorHandler(ByVal myException as OLEDBException)

Dim sMess as String

Dim oLabel as new Label()

Dim eItem as OLEDBError

‘Add a label to the page dynamically to show errors

Continued

oLabel.Text = “” oLabel.Id=”OleDBLabel”

Page.Controls.Add(new LiteralControl(“<hr>”)) Page.Controls.Add(oLabel)

‘Loop throught the Errors in OLEDBException

sMess=sMess & “<p><b>Database Error” & myException.ErrorCode.ToString() &” Occurred: “ & myException.Message &”</b></p>”

sMess=sMess & “<p>StackTrace: “ & myException.StackTrace.ToString()

&”</p>”

sMess=sMess & “<p>TargetSite: “ & myException.TargetSite.ToString()

&”</p>”

For each eItem in myException.Errors sMess=sMess & “<ul>”

sMess = sMess &”<li>Error Message: “ & eItem.Message & “</li>” sMess = sMess &”<li>Source of Error: “ & eItem.Source & “</li>” sMess = sMess &”<li>Native Error Id: “ &

eItem.NativeError.ToString() & “</li>”

sMess = sMess &”<li>SQL State: “ & eItem.SQLState & “</li>” sMess=sMess & “</ul>”

oLabel.Text = oLabel.Text & sMess

Next WriteEvent(myException)

End Function

This function works by defining a variable, eItem as an OLEDBError object. You can then use a For . . . Each . . . Next loop to iterate through the collection, and process each error respectively. The results of this process can be seen in Figure 26-1.

Figure 26-1 Example of handling the OLEDBErrors collection

Now the OLEDBError object properties can be accessed and displayed. We will cover these properties in the next section.

OLEDBError Object Properties

In Listing 26-1, we displayed each of the OLEDBError properties on the label by adding them consecutively to the sMess string variable. We then displayed the results on the runtime generated label control. Let’s take a quick review of each of these properties and see what type of information that they provide:

The Message property provides a short description of the error that was generated. In Figure 26-1 you can see that we attempted to include an invalid column create_error in the SQL Statement. The Message property generates a clear description of the error, Invalid column name ‘create_error’.

The NativeError property enables you to obtain the database-specific error information typically in the form of a code that you can utilize to reference the database technical manuals. Only as a last resort do you want to be utilizing these codes for troubleshooting!

The Source property retrieves the name of the object that generated the error. In Figure 26-1 you can determine that we were using the OLEDB provider and connecting to a SQL Server Database.

The SQLState property gets the five-character error code following the ANSI SQL standard for the database. The character string value returned for an SQLSTATE consists of a two-character class value followed by a three-character subclass value. A class value of 01 indicates a warning and is accompanied by a return code of SQL_SUCCESS_WITH_INFO. Class values other than 01, except for the class IM, indicate an error and are accompanied by a return value of SQL_ERROR. The class IM is specific to warnings and errors that derive from the implementation of ODBC itself. The subclass value 000 in any class indicates that there is no subclass for that SQLSTATE. The assignment of class and subclass values is defined by SQL-92. The specific references and descriptions for these values can be identified by referencing the provider documentation. In Figure 26-1, we were provided a SQLState code of 42S22, which maps to an error description of Column not found.

OLEDBError Object Methods

The methods of the OLEDBError object are the same as the other ASP.NET equivalents.

The Equals() method (inherited from object) determines whether the specified object is the same instance as the current object.

The GetHashCode() method (inherited from object) serves as a hash function for a particular type, suitable for use in hashing algorithms and data structures like a hash table.

The GetType() method (inherited from object) gets the type of the object.

The ToString() method provides a conversion to string method.

The Finalize() method (inherited from object) enables the object to attempt to free resources and perform other cleanup operations before the object is reclaimed by the Garbage Collector (GC). This method may be ignored by the Common Language Runtime; therefore, necessary cleanup operations should be done elsewhere.

MemberwiseClone (inherited from object) creates a shallow copy of the current object.

OLEDBException Properties

The OLEDBException class wraps the OLEDBErrors collection, and as such draws much of the values for its properties from the underlying collection of OLEDBError objects. For instance, the values stored in the Source and Message properties are exactly the same as that of the first OLEDBError object stored in the OLEDBErrors collection. Let’s look at each property to evaluate how it can be used to garner additional information about the error.

The ErrorCode property (inherited from ExternalException) provides the error identification number (HResult) of the error generated by the first OLEDBError object, the property is read-only, and can be useful for researching the cause of the error.

The Errors property contains the OLEDBErrors collection and captures all errors generated by the OleDB data adapter. This is the most frequently used property, and the one that allows you to cycle through all the errors generated by the OleDB data adapter.

The HelpLink property (inherited from Exception) indicates a link to the help file associated with this exception, and is read-only. Typically in the case of the OLEDBErrors collection, this property will be emptied; it is more commonly

used with the generic Exception object when errors are raised specifically by the application.

The InnerException property (inherited from Exception) retrieves a reference to an inner (that may be nested) exception.

The Message property is simply a copy of the message contained in the first OLEDBError of the Errors collection.

The Source property is simply a copy of the source property contained in the first OLEDBError of the Errors collection.

The StackTrace property (inherited from Exception) indicates the stack trace as a text string, which is valuable for determining where the error occurred. This property allows you to follow from the lowest child in the hierarchy to the originating function, which initiated the call that established the error. Figure 26-1 shows that by reviewing the StackTrace property you can identify that the original call that created the error was in ASP.default_aspx.GetRecords(), indicating the default.aspx page and the GetRecords() function.

The TargetSite property (inherited from Exception) indicates the method that threw this exception. This property is read-only.