another "InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining

[Jeff Braun]

I've looked at all of the posts and on the web and found many people running
into this same issue.

I've take an look at my code (posted below) and it looks fine, but this is
my first attemp at ADO.NET with Stored Procedures on SQL Server.

I currently don't have any maxpool setting configured, so it's using what
ever the default is (100 I believe). I've tried to use SQL Profiler to find
out how many active connections there are as well as PerfMon and SQL Ent.
Mgr., but I can't seem to find anything that shows any bad connections.

Any guidance would be appreciated.

Here's my connection string from the web.config file:
<appSettings>
<add key="ConnectionString"
value="server=X.X.X.X;database=demo;uid=demo;pwd=12345;" />
</appSettings>

where X.X.X.X is the IP of my remote Microsoft SQL Server 2000 - 8.00.760
on Windows NT 5.0 (Build 2195: Service Pack 4)

---------------------- code to get data into a DataList
object -------------------------

Private Function getJobs(ByVal state As Integer) As DataSet
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlDataAdapter("demo_GetJobs", myConnection)

' Mark the Command as a SPROC
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterUserID As New SqlParameter("@UserID", SqlDbType.Int, 4)
parameterUserID.Value = 1
myCommand.SelectCommand.Parameters.Add(parameterUserID)

' Add Parameters to SPROC
Dim parameterState As New SqlParameter("@State", SqlDbType.Int, 4)
parameterState.Value = state
myCommand.SelectCommand.Parameters.Add(parameterState)

' Create and Fill the DataSet
Dim myDataSet As New DataSet
Try
myCommand.Fill(myDataSet)
Finally
' Close the Connection
If myConnection.State = ConnectionState.Open Then
myConnection.Close()
End If
'the commands below were added to try to make sure the connection
was closed and released
myCommand.Dispose()
myCommand = Nothing
myConnection.Dispose()
myConnection = Nothing
End Try

Return myDataSet
End Function
-------------------------------- end code ----------------------------------

Sincerely,
Jeff Braun

 

[Scot Rose [MSFT]]

Ricardo, You may need to try and call garbage collector yourself rather than wait for the system to do it. Do you have a sample project that reproduces teh behavior that I could
take a look at?

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------