asked on

How to check whether database connection is dropped?With out having any dummy Query

I am using adodb as follows:

Public ConnectDatabase As New ADODB.Connection

Public Sub ConnectCurrent()
Dim ConnectionString As String
On Error GoTo errdesc

ConnectionString = "uid=sa;pwd=;dsn=NPIL"
ConnectDatabase.Open ConnectionString
ConnectDatabase.CursorLocation = adUseClient
Exit Sub
errdesc:
MsgBox Err.Description, vbInformation, "Information"
If ConnectDatabase.State = 1 Then ConnectDatabase.Close
End
End Sub
…….

While running my program NT/Database connection may break, so I would like to test whether database connection is dropped or not. If dropped then again connect it.
I have tried as follows

If ConnectDatabase.State =0 Then
ConnectDatabase.Close
Call ConnectCurrent
End If

But after successful connection it alwas shows 1,though it has been disconnected from database.
How to do that one, can any one help me…

MYLim

normally,we use error code to solve this problem...

if err.number = 12345 then 'number of error code
do something
end if

leonstryker

I use something like this to check for it:

Select Case True
Case cnConn Is Nothing
Set cnConn = New ADODB.Connection
GoTo OPEN_CONN
Case cnConn.State = adStateClosed
GoTo OPEN_CONN
End Select

Leon

muntasirrahman

ASKER

Hi leonstryker,
It Does not work

muntasirrahman

ASKER

Hi leonstryker,
It Does not work

leonstryker

What is the problem?

Leon

muntasirrahman

ASKER

though Nt is dropped/Db is disconnected
'cnConn is nothing ' is false

leonstryker