Frequent Timeout Expired Problem
Hi. Im using the code below but everytime somebody runs a huge script on query analyzer or the server is having a processing a huge load, my program experiences timeout. Im using functions to ease coding. Is there any problem with my code? How can I improve it so that I wouldn't receive any timeouts or timing out is normal? Thank you very much....
Code:
Public Sub EstablishConnection(ByRef pCon As Connection)
Set pCon = New Connection
pCon.ConnectionTimeout = 0
pCon.CommandTimeout = 0
pCon.ConnectionString = gConnectionString
'gConnectionString = "Provider='SQLOLEDB.1';Persist Security Info='False';Data Source='" & txtServerName + "'; Initial Catalog='" & txtDbaseName.Text & "'; User ID='" & txtUserName & "'; password='" & txtPassword & "
End Sub
Public Sub EstablishRecordsource(ByRef pRst As Recordset, ByVal pSource As String, ByRef pActiveCon As Connection, ByVal pCursorType As CursorTypeEnum, ByVal pOptions As Long)
Set pRst = New Recordset
pRst.Open pSource, pActiveCon.ConnectionString, pCursorType, adLockOptimistic, pOptions
End Sub
Re: Frequent Timeout Expired Problem
You should never get a Timeout error with a CommandTimeout = 0.
The problem is that the EstablishRecordsource procedure is not using an existing connection but is opening a new connection. Thus the default CommandTimeout of 30 seconds is being used.
Not sure why you are using the ConnectionString property of the pActiveCon variable when it seems you could just use
pRst.Open pSource, pActiveCon, pCursorType, adLockOptimistic, pOptions
Re: Frequent Timeout Expired Problem
Quote:
The problem is that the EstablishRecordsource procedure is not using an existing connection but is opening a new connection. Thus the default CommandTimeout of 30 seconds is being used.
So meaning I have to set the CommandTimeout property again to 0? Does this mean that if you pass a connection object ByRef its doesn't capture all the other attributes of the original connection object?
Quote:
Not sure why you are using the ConnectionString property of the pActiveCon variable when it seems you could just use
I too am not sure why because everytime I omit the ConnectionString property of pActiveCon I get an error message saying:
"The connection cannot be used to perform this operation. It is either closed or invalid in this context"
Perhaps it has something to do with the pass by ref that Im trying to do? Should I just combine the two procedures?
Re: Frequent Timeout Expired Problem
Quote:
I too am not sure why because everytime I omit the ConnectionString property of pActiveCon I get an error message saying:
"The connection cannot be used to perform this operation. It is either closed or invalid in this context"
Well, you do need to open the Connection before you can use it to open a Recordset. I assumed the calling process was opening it.
Re: Frequent Timeout Expired Problem
Perhaps the passing by ref doesn't work?
I still don't know why I'm timing out, should I not use the subs?
Re: Frequent Timeout Expired Problem
Try this
VB Code:
Public Sub EstablishRecordsource(ByRef pRst As Recordset, ByVal pSource As String, ByRef pActiveCon As Connection, ByVal pCursorType As CursorTypeEnum, ByVal pOptions As Long)
Set pRst = New Recordset
If pActiveCon.State = adStateClosed Then
pActiveCon.Open
End If
pRst.Open pSource, pActiveCon, pCursorType, adLockOptimistic, pOptions
End Sub
Re: Frequent Timeout Expired Problem
I replaced my code with your suggestion. It did solve the my problem of still using the ConnectionString property of pActiveCon.
I haven't tested it with the timeout problem yet. Should I add the pActiveCon.ConnectionTimeout = 0?
Thanks