Solved

How to check whether database connection is dropped?

Posted on 2004-04-05
7
321 Views
Last Modified: 2010-05-02
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
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 where database connection  is dropped or not. If dropped then again connect it.
 I have tried as follows

                  If ConnectDatabase.State = 1 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…
0
Comment
Question by:muntasirrahman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 30 total points
ID: 10756163
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
http://www.brainbench.com
0
 
LVL 11

Expert Comment

by:jmwheeler
ID: 10756918
State should not show 1 if when you close your database you close it like this

ConnectDatabase.Close
Set ConnectDatabase = Nothing
0
 

Author Comment

by:muntasirrahman
ID: 10762882
jmwheeler,
you are right but When it is disconnected from Db then it also shows 1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:TimCottee
ID: 10763736
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.
0
 

Author Comment

by:muntasirrahman
ID: 10763919
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
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 10763939
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.
0
 

Author Comment

by:muntasirrahman
ID: 10852212
Thx! would you mind give this idea to my open question

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20944813.html#10774027


so that i could give you credit ...
 
 
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question