Solved

How to check whether database connection is dropped?

Posted on 2004-04-05
7
315 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
  • 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now