How to reconnect to a db automatically?

  • Thread starter Thread starter Robert Dufour
  • Start date Start date
R

Robert Dufour

My functions or subs that connect to my databases are always using the
following code pattern (Vs2003)

Private Mysub ()
Dim MyConn as new Odbc.connection
MyConn.ConnectionString = g_ODBCConnStr 'a global variable that was
tested at application launch to make sure it was valid and could open the
connection, if not the application would not launch and the app.config value
would need to be changed.
Dim MyComd as new Odbc.Command
MyComd.Connection = MyConn
MyCmd.CommandText = "select whatever from whateverTable"
Try
MyConn.open
If Myconn.State = ConnectionState.open Then
'Do whatever needs to be done

else
'The connection could not be opened

end if


Catch ex as exception



End try
MyConn.close

end sub

My question is as follows. If my connection could not be opened in this sub,
it means that something unexpected happened, like a server down, a network
error because as stated above, at application start , the connection was
tested and app would not launch were it not available.
How can I detect precisely what caused the attempt at opening a connection
to fail?
For instance if the sql server is just rebooting after an update, how could
I tell?
I think I would try to differentiate between serious hardware problems like
network down or server stopped and other problems like time out. In some
cases it might be worth trying a number of times to reconnect in other cases
just let it go and have tech support trouble shoot.

Does anyone have any opnions on this? My app is one that should run 24-
7 -365 with no user interaction. It sits on a server in a backroom, but it
is business critical and so I put some code in to advise me in case of
exceptions and important errors, but if the connection is out temporarely
only I don't want to have to spend time trouble shooting it.

Any insights would be greatly appreciated.
Bob
 
Robert Dufour said:
My functions or subs that connect to my databases are always using
the following code pattern (Vs2003)

Private Mysub ()
Dim MyConn as new Odbc.connection
MyConn.ConnectionString = g_ODBCConnStr 'a global variable that
was tested at application launch to make sure it was valid and could
open the connection, if not the application would not launch and the
app.config value would need to be changed.
Dim MyComd as new Odbc.Command
MyComd.Connection = MyConn
MyCmd.CommandText = "select whatever from whateverTable"
Try
MyConn.open
If Myconn.State = ConnectionState.open Then
'Do whatever needs to be done

else
'The connection could not be opened

end if


Catch ex as exception



End try
MyConn.close

end sub
Click to expand...


Is it really necessary to check myconn.state after opening the connection,
and also catch an exception? I think if it can't be opened you get an
exception and don't need an additional check.

My question is as follows. If my connection could not be opened in
this sub, it means that something unexpected happened, like a server
down, a network error because as stated above, at application start
, the connection was tested and app would not launch were it not
available.
How can I detect precisely what caused the attempt at opening a
connection to fail?
For instance if the sql server is just rebooting after an update,
how could I tell?
I think I would try to differentiate between serious hardware
problems like network down or server stopped and other problems like
time out. In some cases it might be worth trying a number of times
to reconnect in other cases just let it go and have tech support
trouble shoot.

Does anyone have any opnions on this? My app is one that should run
24- 7 -365 with no user interaction. It sits on a server in a
backroom, but it is business critical and so I put some code in to
advise me in case of exceptions and important errors, but if the
connection is out temporarely only I don't want to have to spend
time trouble shooting it.

Any insights would be greatly appreciated.
Bob
Click to expand...


The documentation on the Open method lists the possible Exceptions. One is
the OdbcException. It has an Errors property - a collection - and each item
in the collection (class OdbcError) has a nativeError property (and others).
You can evaluate this property to get detailled information and perform the
required actions.


Armin
 
Thanks Armin
Armin Zingler said:
Is it really necessary to check myconn.state after opening the connection,
and also catch an exception? I think if it can't be opened you get an
exception and don't need an additional check.




The documentation on the Open method lists the possible Exceptions. One is
the OdbcException. It has an Errors property - a collection - and each
item
in the collection (class OdbcError) has a nativeError property (and
others).
You can evaluate this property to get detailled information and perform
the
required actions.


Armin
Click to expand...
 
Back
Top