asked on

Sql Connection error handling

I'm moving a project from ASP Classic to vb.net and need some help transitioning some error trapping code. In the Classic version I had an include page that had a subroutine that included code like:
If objConnection.Errors.count > 0 Then
Dim myError As String = ("The following Connection Errors occured from " & Request.ServerVariables("URL") & " ")

For iCounter = 0 To objConnection.Errors.count - 1

myError = myError & ("Error Number: " & objConnection.errors(iCounter).number & " ")
myError = myError & ("Error Description: " & objConnection.errors(iCounter).description & " ")
myError = myError & ("SQL State: " & objConnection.errors(iCounter).SQLState & " ")
myError = myError & ("Native Error: " & objConnection.errors(iCounter).NativeError & " ")
Next

Each page would call that sub passing the objConnection = ADODB.Command.ActiveConnection. How do I go about coding this for a .net (1.1) project?

thx
--steve...

ASKER CERTIFIED SOLUTION

existenz2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron Jabamani

Hope below helps

Public Sub DisplaySqlErrors(myException As SqlException)
Dim i As Integer
For i = 0 To myException.Errors.Count - 1
MessageBox.Show("Index #" & i & ControlChars.NewLine & _
"Error: " & myException.Errors(i).ToString() & ControlChars.NewLine)
Dim myError As String = ("The following Connection Errors occured from " & Request.ServerVariables("URL") & " ")

For iCounter = 0 To objConnection.Errors.count - 1

myError = myError & ("Error Number: " & myException.Errors(i).Number & " ")
myError = myError & ("Error Description: " & myException.Errors(i).Message & " ")
myError = myError & ("SQL State: " & myException.Errors(i).State & " ")
Next i
End Sub

-Aaron

juststeve

ASKER

Makes sense...this followup is a bit off topic of SqlConnection handling toward the Err. object...

In the Classic version i just called the handler at the bottom of the page and checked: 'If Err.Number <> 0 Then '...Am I expected to put that at the bottom of each of my Subs now? Or where can I put it to trap any error just prior to rendering?

Aaron Jabamani

Where ever u have sqlconnection to connect to database, put the SqlConnection command inside the TRY CATCH Block like below

public sub Getconnection ()
Try
....
....sqlconnection.Open() ' if sqlconneciton throws error then it goes to catch block
Catch err as SqlException
DisplaySqlErrors(err) ' Else u can put all the code here itself, which is therein my previous msg
End sub

-Aaron