Solved

How to check whether database connection is dropped?

Posted on 2004-04-05
7
320 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…

808 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