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

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 Removed) <Remove word online. from address>

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




--------------------
>Content-Class: urn:content-classes:message
>From: "Ricardo Maciel" <(E-Mail Removed)>
>Sender: "Ricardo Maciel" <(E-Mail Removed)>
>References: <(E-Mail Removed)> <(E-Mail Removed)> <(E-Mail Removed)>
>Subject: Re: another "InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool" error
>Date: Sun, 27 Jul 2003 14:47:54 -0700
>Lines: 211
>Message-ID: <051b01c35488$bbfcd3e0$(E-Mail Removed)>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcNUiLv8OsjGcLBuQYekg4S/SrfvdA==
>Newsgroups: microsoft.public.dotnet.framework.adonet
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:56810
>NNTP-Posting-Host: TK2MSFTNGXA06 10.40.1.53
>X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
>
>Jeff, Scot,
>
>I'm getting the same exception when debugging a component
>for db access (typed dataset/dataadapter only) and
>business rules. I use a web app for testing.
>
>So far things come back to normal for some time only upon
>rebooting.
>
>All DA commands are surrounded by connection open/close.
>
>The problem seems to have started when the number of
>controls in the test app increased.
>
>hope to get some clue from you.
>
>Ricardo
>
>
>
>>-----Original Message-----
>>Scot,
>>
>>Yes, I've read all about the 100 connections in a pool.
>If you see my code
>>below, you will notice that I am doing everything you
>say: closing the
>>connection and setting objects to nothing.
>>
>>However, I'm still getting this error and cannot figure
>out if my code is
>>incorrect. in some fashion. I have try/finally blocks
>around all of my
>>SqlConnection stuff, and anytime I return a
>SqlDataReader, I make sure that
>>the receiving method does a dr.Close after setting the
>DataList.DataSource
>>and *.DataBind.
>>
>>Jeff
>>
>>"Scot Rose [MSFT]" <(E-Mail Removed)> wrote in
>message
>>news(E-Mail Removed)...
>>> By default the max pool size is 100. If the number of
>connections opened
>>in the pool are 100, you will get the above message.
>>> Once the connection is used, the connection needs to be
>closed, if not the
>>connection is not returned to the pool.
>>> It is true that if the object goes out-of-scope, the
>Garbage collector
>>takes care of it. Garbage collection is a very expensive
>process, therefore
>>it is does not
>>> execute very frequently. And therefore the connections
>that are
>>out-of-scope are not closed, and these connections are
>not available for
>>other requests.
>>> It is always safe to close the connection once you are
>done using it.
>>Also, make sure the DataReader objects are set to
>null/nothing. The
>>DataReader
>>> objects hold a live connection. If it is not destroyed,
>the connection is
>>not returned back to the pool.
>>>
>>>
>>> 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 Removed) <Remove word
>online. from address>
>>>
>>> This posting is provided "AS IS", with no warranties,
>and confers no
>>rights.
>>>
>>>
>>>
>>>
>>> --------------------
>>> >Wrom: FDULHPQQWOYIYZUNNYCGPKYLEJGDGVCJVTL
>>> >Subject: another "InvalidOperationException: Timeout
>expired. The
>>timeout period elapsed prior to obtaining a connection
>from the pool" error
>>> >Date: Fri, 25 Jul 2003 02:22:49 -0500
>>> >Lines: 69
>>> >X-Priority: 3
>>> >X-MSMail-Priority: Normal
>>> >X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>>> >X-MimeOLE: Produced By Microsoft MimeOLE
>V6.00.2800.1165
>>> >Message-ID: <(E-Mail Removed)>
>>> >Newsgroups: microsoft.public.dotnet.framework.adonet
>>> >NNTP-Posting-Host: cpe-65-27-116-129.mn.rr.com
>65.27.116.129
>>> >Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!
>TK2MSFTNGP12.phx.gbl
>>> >Xref: cpmsftngxa06.phx.gbl
>microsoft.public.dotnet.framework.adonet:56684
>>> >X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
>>> >
>>> >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
>>> >
>>> >
>>> >
>>>
>>>
>>
>>
>>.
>>
>