pool

[fmardani]

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

[nemaroller]

I can only guess your form code is opening 300 connections to the database, instead of reusing the same one. Re-organize your code. You need to bring the opening and closing of connections outside of your for-loop.

[fmardani]

The above code that I posted earlier is in a class module. I am looping through this datareader in a form module, therefore how can I close the connection in the form when the connection object is in the class module?
Thanks

[nemaroller]

Make a function in the class that closes the connection, (it can be opened when the class is constructed, or make a seperate functino for that too), and declare the connection at the class level.

[Edneeis]

A DataReader requires exclusive use of a connection. That means that if you have a DataReader open and are using it you can not use that same connection object for anything else until you close the DataReader.