A Treeview is used to select the record to be displayed. On clicking a node a record is displayed. It is possible to click 9 nodes & get the record displayed but the 10th bombs out with a TimeOut message.
"Error. System.Data Timeout expired....."
On restarting VB (2003) clicking the record causing the bomb is displayed correctly so it is not an issue with that record.
I have VERY carefully checked that every "cnn.open" is correctly matched with a "cnn.close"
I have also stepped through the code line by line in the debugger and the "Close"s are executed correctly.
A record consists of 5 segments each of which calls a procedure to get its data. Each procedure opens a connection, gets its data & closes the connection. I have also tried eliminating 4 of the five parts (varied, so it is not one particular part) and it just increases the number of records that can be returned before the bomb.
A typical code segment is:
Public Function GetOutcomeRecord(ByVal RequestID As Long)Dim cnn As New SqlConnection
Dim cmd As SqlCommand
Dim Rdr As SqlDataReader Try
cnn =
New SqlConnection(gProgramSettings.SQLConnectionString)cmd = New SqlClient.SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "tdGetRequestOutcomeID"
cmd.Parameters.Add("@RequestID", RequestID)
cmd.Connection = cnn
cnn.Open()
Rdr = cmd.ExecuteReader
If Rdr.HasRows Then
While Rdr.Read
With gOutcomeRecord
.RequestID = RequestID
'Outcome
If IsDBNull(Rdr(0)) Then
.OutcomeID = 1 'Active
Else
.OutcomeID = Rdr("OutcomeID")
End If
End With
End While
Else
ClearOutcomeRecord()
End If
cnn.Close()
GetOutcomeRecord = True
Catch ex As Exception
MsgBox("Error: " & ex.Source & " - " & ex.Message, MsgBoxStyle.OKOnly, "GetBPCS Record")
If Not (cnn Is Nothing) Then
cnn.Close()
End If
GetOutcomeRecord = False
Finally
cnn.Close()
End Try
End Function
Thanks
Timeouts
Pav
Look up connection pooling in the Visual Studio help ... there are ways to control the Connection Pool in the ConnectionString. See the section titled
"Controlling Connection Pooling with Connection String Keywords"
iso3200
Thank you for your rsuggestion Alazela
SQLProfiler seems to indicate that the connections are being closed. No mention is made of cleanup. The problem occurs on a form where a tree display can take a large number of hits as the user arrows down.
We have got around the problem in a way of which I am not keen, by opening a connection on form_open and closing the connection on form_close and passing the connection to sub routines. This is ok provided the user does not leave the form open indefinitely.
After watching SQL Profiler I am convinced that the problem is back on the VB .Net machine (mayby I am wrong)
Again, thanks for your suggestion
Bonzo
LuigiGallo
Thanks for you comments Plankton.
I have since tried rdr.close to no avail. I will try the extra. (The return type has also been addressed). I have also tried putting the segments into one connection which defeats the side functionality I was after. This increases significantly the number of records which can be viewed from 10 to 70+ but does not solve the problem.
How many connections are you allowed
Does VB .Net (2003) have some built in smarts which may be working against me Does it, for instance, put off housekeeping to try to keep up with the input
I have also thought of disabling the tree while the processing is happening. This may stop the user from using the arrow keys to simply run down the tree very fast perhaps giving some respite
Some articles refer to increasing the timeout but this is already at 30 seconds, so I do not think this is the issue. I cannot help but think that this may be a false error message but cannot see where as a record which bombs on one occasion is fine the next.
Thanks for your input
Bonzo
rootschopf
Scott Diehl
You might consider a little more housekeeping.
For example:
rdr.close
cmd = nothing
rdr = nothing
cnn = nothing
I also noticed there is no return type set for your function
Philip Falkenberg
SqlConnection.Close() closes all active readers, so open readers are not your issue.
I'd suggest using SqlProfiler to see what's actually executing against the server, and determine what is timing out. Watch RPC & BATCH events, both start and end variations. This should give you some clue as to which resource is not being cleaned up.
mvinod
Yes BonnieB!
Connection pool is mentioned in the error message. I believe connections in the pool (I think the VB end as SQL Profiler is showing no errors & connections appear to be released at that end). The connection before the timeout appears to have been released and SQL Profiler shows no activity on the connection which times out, suggesting it has occurred at the client end ( ). Does VB/C# have its own pool of connections ) & could it be those which are not being released in a timely manner
Bonzo