By Michael McIntyre

mikemc@getdotnetcode.com

The .Net Framework namespaces “SqlDB” and “OleDB” provide two specific classes that represent exceptions that may be raised when a .Net program attempts to communicate with a database. Details are returned for failed connections, SELECTS, INSERTS, UPDATES, DELETES and other failed database commands.

The two classes are the SqlException and OleDbException classes.  By handling these exceptions with a Try…Catch block you can get specific details about the database exceptions that occur.

Both classes return a collection of errors.  To expose all database errors returned in a SqlException or an OleDbException your code must loop through the error collection.  Shown below is an example for handling an OleDbException and another for handling a SqlException.  For the purposes of this article both examples create bad database connection code to force database exceptions to occur.

Example:  OleDbException Handler

' Create a query string.

Dim aQuery As String = "SELECT CustomerName FROM Customers"

' Instantiate an invalid OleDbConnection

' and assign it to the myConnection variable.

Dim aConnection As New OleDbConnection _

 ("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=")

Dim aCommand As New OleDbCommand(aQuery, aConnection)

' Wrap an OleDb method (Open) in an exception handler.

Try

     ' Because an invalid OleDbConnection is used

     ' this statement will raise an OleDbException.

     aCommand.Connection.Open()

Catch ex As OleDbException

     ' Show a general message about the error.

     MessageBox.Show(ex.ToString)

     ' Loop through all OleDbException errors

     ' returned by the database.

     Dim i As Integer

     For i = 0 To ex.Errors.Count - 1

           ' Report the error details.

           Console.WriteLine("Message: " & ex.Errors(i).Message)

           Console.WriteLine("NativeError: " & ex.Errors(i).NativeError)

           Console.WriteLine("Source: " + ex.Errors(i).Source)

           Console.WriteLine("SQLState: " + ex.Errors(i).SQLState)

      Next i

End Try

Result:

Message: Could not find installable ISAM.

NativeError: -69141536

Source: Microsoft JET Database Engine

SQLState: 3170

Example: SqlException Handler

' Declare a variable of type ConnectionString named aConnectionString.

' Assign a connection string to a non-existent server to

' the aConnectionString to force a SqlException.

Dim aConnectionString As String = "user id=sa;password=;database=northwind;server=badserver"

Dim aConnection As New SqlConnection(aConnectionString)

Me.Cursor = Cursors.WaitCursor

' Wrap a SqlDb method (Open) in an exception handler.

Try

     aConnection.Open()

Catch ex As System.Data.SqlClient.SqlException

     Console.WriteLine(ex.Message)

     ' Loop through all errors returned.

     Dim i As Integer

       For i = 0 To ex.Errors.Count - 1

            ' Report error details.

            Console.WriteLine("Class: ", ex.Errors.Item(i).Class.ToString)

            Console.WriteLine("Error #: " & ex.Errors.Item(i).Number.ToString)

            Console.WriteLine("Error Message: " & ex.Errors.Item(i).Message)

            Console.WriteLine("Line Number: " & ex.Errors.Item(i).LineNumber.ToString)

            Console.WriteLine("Connected To: " & ex.Errors.Item(i).Source)

            Console.WriteLine("Server: " & ex.Server)

        Next i

End Try

Result:

Class:

Error #: 17

Error Message: SQL Server does not exist or access denied.

Line Number: 0

Connected To: .Net SqlClient Data Provider

Server: BADSERVER

This article has provided you with a way to use the OleDbException and SqlException classes to debug database communication issues.  These classes can also be used in exception handlers that catch the exception, determine the problem, and take remedial action at runtime e.g. reverse a failed update attempt.

Learn more about the OleDbException and SqlException classes by visiting the links below.

SqlException

OleDbException

Copyright © 2001-2003 aZ Software Developers. All rights reserved.