• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

How to check whether database connection is dropped?

I am using adodb (to connect my program with SQL server DB )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
    MsgBox Err.Description, vbInformation, "Information"
    If ConnectDatabase.State = 1 Then ConnectDatabase.Close
End Sub

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

                  If ConnectDatabase.State = 1 Then
                    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…
  • 3
  • 3
1 Solution
Hi muntasirrahman,

This doesn't quite work as you have found out. The only way you can truly test for this is to actually run something across the connection and see whether it works. This can be something as trivial as selecting a value such as ConnectDatabase.Execute "Select @@Version" then testing the .Errors collection of your connection object. The .State property doesn't actually require the ADO layer to request this from the database server, it is purely an indication of the state of the object locally.

Tim Cottee
Brainbench MVP for Visual Basic
State should not show 1 if when you close your database you close it like this

Set ConnectDatabase = Nothing
muntasirrahmanAuthor Commented:
you are right but When it is disconnected from Db then it also shows 1
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

jmwheeler, as I said, that is an indication of the state locally. You have changed the state by closing the database.  As the questioner rightly noted this is not the case if the connection is broken as the .state property is only refreshed when an action is taken on the client and not otherwise. There are a number of approaches to this problem, some people like to think that it should be the server's responsibility to announce that it has terminated the connection - of course that is a nonsense for practical purposes especially as we are talking about a database access layer here (ADO) which can be using everything from oracle or sql server to a flat text file as the database. So logically there is no consistent event that can be raised back to each client to inform them that a break has occured from the server's perspective. Equally the break in connection maybe a physical network layer issue that neither the client or server will know about at that time. The only way to know for certain whether a connection is valid (not just open) is to use it and trap any error that occurs.
muntasirrahmanAuthor Commented:
hi TimCottee,
               You are absolutely right, I have been using your technique … .  In my own point of view, passing a query to test the connectivity is a bad practice, as I have to test it several times in each second.  
If there is no improve way to do this … I have to continue with this concept ….
Thanks a lot
The only alternative is to have a "heartbeat" process that runs lets say once every ten seconds. This can be used to test connectivity in much the same way as a query would (in fact you would do exactly the same sort of thing) but as it runs only once every few seconds you can then trap the error and close the connection. This would allow you to use the .State with a little more chance of it being adStateClosed when a connection is not valid. This is of course a compromise and cannot be used as a substitute for adequate error handling in all your calls to the database.
muntasirrahmanAuthor Commented:
Thx! would you mind give this idea to my open question


so that i could give you credit ...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now