SQL connection state and setting timeout

[dlscott56]

I am using ADODB code to connect to a SQL Server 2005 database from my VB6 program. The connection is opened in the following :

    

Public g_SQL_Con As New ADODB.Connection

Public Function OpenSQLConnection() As Boolean
'**************************************************************************
' Purpose   :   Initializes the SQL connection
'
'**************************************************************************

    g_SQL_Con.ConnectionString = _
        "Provider=sqloledb;" & _
        "Data Source=" & g_strSQLServerIP & ";" & _
        "Initial Catalog=catalog;" & _
        "User ID= user;" & _
        "Password=password;" & _
        "Connection Timeout=10"
        
    g_SQL_Con.Open

End Function

When this is completed the g_SQL_Con.State = 1. Throughout the program if I need to execute a command I build the command string and then test g_SQL_Con.State to see if it is 1. Then the command is executed. If not 1 then a call is made to the function OpenSQLConnection.

This all works fine as long as the server is available after the initial g_SQL_Con.Open. If, for some reason, the network is down, the server has stopped, etc., my g_SQL_Con.State doesn't seem to change. It stays at 1. An attempt to execute the command is made, the system hangs for a good 30-40 seconds and an error occurs. I trap the error and execute g_SQL_Con.Close to correct the state. Next time a call is requested it sees the need to open the connection and calls OpenSQLConnection.

My questions are :

1. If the server isn't available when executing g_SQL_Con.Open the system hangs for 30-40 seconds no matter what I set Connection Timeout to.
2. If the connection state is incorrectly reported as 1 my command execution hangs for 30-40 seconds no matter what I set Command Timeout to.
3. Should I be opening and closing the connection each time I need to access the server or continuing to open it and leave it open as long as the program is running?

[GaryMazzone]

I open and close connections each call

[techgnome]

1 - are you SURE it's the CONNECTIONTimeout you're setting? It's a setting on the Connection object... set it directly, not through the connection string.
2 - the COMMANDTimeout only affects how long it will wait for a command to execute before bailing... it has nothing to do with the connection and if there is no connection, the command can't be executed so the CommandTimeout is moot.
3 - Yes - open the connection, do what you need to do, which could be one query or a series of commands, then close the connection and get out. This way you should be in a known state each time. You don't (shouldn't) have to guess what the connection state is.

-tg

[dlscott56]

I started by using g_SQL_Con.ConnectionTimeout = 10 first. Still hanging 30-40 seconds before error. Then tried putting it in the connection string as shown. Seemed to be same results either way.