Trapping ADO Connection Errors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DavidM - 28 Dec 2004 22:11 GMT

I have a VB6 application that uses ADO to communicate with SQL Server 2000.
The application works fine, however I seem to have issues with trapping
connection errors with ADO.

I have a basic Error Handler in all my functions and display both VB and ADO
errors when they occur -- however, the problem I seem to be having is this:

1) I create ADO Connection object and successfully log into SQL Server. No
problems.

2) I can create Recordset objects using Connection objects to perform
various operations on my database.

3) However, if I disconnect my network cable or disconnect my VPN connection
to work while my application is idle doing nothing, and then attempt to
perform a database operation... I normally get a slew of errors... some of
which seem to get hung in a loop.

Some of the errors are from when I try to open a recordset connection within
a With block or whatever other commands try and use an ADO command after the
connection is lost.

What is the best way to handle this?

Also, I currently display on the status bar "Connected" after a user
successfully logins in an my ADO Connection object is created. I would like
to dynamically change this to "Disconnected" when I can no longer
communicate with database.

I assume, if this happens, I will have to have a way for the user to attempt
to log back into the application and re-create the connection object atain.

Anyone know the best way to handle this?

Reply to this Message

Veign - 29 Dec 2004 01:07 GMT

You really haven't explain how you code is structured. Are you using
classes for the database function, common methods, one long procedure. Do
you have error handling in the functions / subs?

Are you aware of the Error collection used by ADO:
http://www.devguru.com/Technologies/ado/quickref/connection_errorscollection.html

Signature

Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--

> I have a VB6 application that uses ADO to communicate with SQL Server 2000.
> The application works fine, however I seem to have issues with trapping
[quoted text clipped - 29 lines]
>
> Anyone know the best way to handle this?

Reply to this Message

DavidM - 29 Dec 2004 03:49 GMT

Thanks for the reply. Yes, I'm aware of it. I do not use classes -- I'm
simply calling various functions that query the database and populate
controls or collections.

Basically, each function has an error handler. If an error occurs, I simply
have my error handler call the following function:

' This routine displays ADO and VB errors
Public Sub DisplayErrorMessage(strTitle As String)
If gmobjConn.State = adStateOpen Then
If gmobjConn.Errors.Count > 0 Then
MsgBox "Error Code: " & gmobjConn.Errors(0).Number _
& vbCrLf & "Description: " & gmobjConn.Errors(0).Description _
& vbCrLf & "Source: " & gmobjConn.Errors(0).Source, _
vbOKOnly + vbCritical, strTitle
Else
MsgBox "Error Code: " & Err.Number _
& vbCrLf & "Description: " & Err.Description _
& vbCrLf & "Source: " & Err.Source, _
vbOKOnly + vbCritical, strTitle
End If
Else
MsgBox "Error Code: " & Err.Number _
& vbCrLf & "Description: " & Err.Description _
& vbCrLf & "Source: " & Err.Source, _
vbOKOnly + vbCritical, strTitle

End If

End Sub

After this function returns, my error hanlder usually jumps back up a few
lines to an "exit" label within my function, to reset various controls to
disable and then exit function.

In the original example that I gave, when my VPN connection is dropped while
I have a connection open... and I'm in one of my routines reading a 10 row
table or whatever... I appear to get hung in an endless loop.

I'm just trying to figure out the best way to handle connection errors and
so forth within my database code so users won't be stuck when something
externally happens to the connection.

> You really haven't explain how you code is structured. Are you using
> classes for the database function, common methods, one long procedure. Do
[quoted text clipped - 47 lines]
>>
>> Anyone know the best way to handle this?

Reply to this Message

Ralph - 29 Dec 2004 21:35 GMT

> Thanks for the reply. Yes, I'm aware of it. I do not use classes -- I'm
> simply calling various functions that query the database and populate
[quoted text clipped - 38 lines]
> so forth within my database code so users won't be stuck when something
> externally happens to the connection.

Setting an exit flag should do the trick.

You might try using ADO Connection events.

-ralph

Reply to this Message

VB Forum / Database Access / December 2004

Trapping ADO Connection Errors