CODEWITHSUNDEEP
vb6adodbrecordset
toolman87
toolman87

Reputation: 23

VB6 ADODB.Connection Execute() Retry until successful

I'm trying to fix a little utility which seems to be losing connection to the database after some idle time. I already set the timeout to 0 but that didn't seem to work.

Instead of simply crashing and displaying a couple of error messages I would like to try to re-establish the connection and execute the query until successful (I realize this is probably a bad use of resources) but even then that's what I'm trying to accomplish here. Or if possible display a Message Box saying that connection was lost which will then be closed once connection is established.

Any suggestions would be greatly appreciated. 

Public connMain As ADODB.Connection
Public rsMain As ADODB.Recordset 

......

Function Picture_Exists() As Boolean
On Error Resume Next 
sqlstr = "select * .... "
Set rsMain = connMain.Execute(sqlstr)

Upvotes: 0

Views: 1180

Answers (2)

Amit Raj
Amit Raj

Reputation: 1378

You can try the following:

On Error Resume  ''''instead of On Error Resume Next

Dim rsMain As New ADODB.Recordset
sqlstr = "select * .... "
If rsMain.State = adStateOpen Then rsMain.Close
rsMain.Open sqlstr, sProvider, adOpenKeyset, adLockOptimistic

Upvotes: 0

BobRodes
BobRodes

Reputation: 6165

Your connection is probably being dropped at the database end due to non-use. It isn't good practice to maintain a connection when it isn't being used; connections are expensive in terms of resources so any such practice wouldn't scale well. Dbadmins aren't likely to leave unused connections open for very long.

Your potential solution says to try to connect and if you can't ignore the error. We don't say "never" in this business very often, but you should never use "On Error Resume Next" without also evaluating Err.Number to see whether it equals 0 (if it does, there's no error). This is called "inline error handling."

In any case, I wouldn't use this method. I would evaluate the Connection object's State Property, and if it's closed (cn.State = adoStateClosed) then I would reopen it.

Upvotes: 1

Related Questions