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

---

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

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?

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.

Perhaps the passing by ref doesn't work?

I still don't know why I'm timing out, should I not use the subs?

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

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