pool

[farshad]

Hi,

in a class module, this is how I am retrieving data.

 

Public Function GetAllCollectionsForParent(ByVal intParentID As Integer) As SqlDataReader
       Dim strSQL As String = "CSA_SP_GetAllStrategiesParentCollections"
       Dim oCon As SqlConnection
       Dim oCmd As SqlCommand

       Try
           oCon = New SqlConnection(ConnectToDB)
           oCmd = New SqlCommand(strSQL, oCon)

           With oCmd
               .CommandType = CommandType.StoredProcedure

               .Parameters.Add("@StrategyParentID", SqlDbType.Int, 4)
               .Parameters("@StrategyParentID").Value = intParentID

               oCon.Open()
               Return .ExecuteReader(CommandBehavior.CloseConnection)
           End With

       Catch ex As Exception
           Throw ex
       End Try

   End Function

 

In the form module I then use a datareader to loop through the above records...

After about 300 records i receive an error message saying something about the connection pool may have reached its limit.

Any thoughts?

Thanks

Edited August 26, 2003 by divil

[divil]

Are you running this method repeatedly? The way you have implemented it could be done better - the code to open (and close, which you don't have) the connection should surround any code that uses the data reader.

 

It is probably trying to create a new connection every time because the existing one is busy with the datareader, maybe you're not closing that either? You should always call .Close() and .Dispose() on objects that support them when you're done with them.

[farshad]

Hi,

Yes, I am running it repeatedly.

I can not close it in the above code because then I will not be able to loop through that sqldatareader function

[divil]

Which is why you shouldn't be opening it in that function either. The Open and Close of it should really be in the same scope, you could always pass the connection object to that function which could return the datareader from it.

[yvesvlb]

Heh..

 

CommandBehaviour.Closeconnection!

*sigh*