Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may

[Guoqi Zheng]

Dear sir,

I keep getting the following errors on one of my sites after clicking for
many times.

Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached.

Below is my code. Any help will be appreciated.

Dim objReader As SqlDataReader

Dim strConnection As String =
System.Configuration.ConfigurationSettings.AppSettings("strConnect")

Dim myConnection As SqlConnection = New SqlConnection(strConnection)

Dim myCommand As New SqlCommand("MyProc_XXXX", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

Dim objPara1 As New SqlParameter("@msgId", SqlDbType.Int, 4)

myCommand.Parameters.Add(objPara1)

objPara1.Direction = ParameterDirection.Input

objPara1.Value = MsgId

' Open the connection.

myConnection.Open()

objReader = myCommand.ExecuteReader()



MsgFull.DataSource = objReader

MsgFull.DataBind()

objReader.Close()

objReader = Nothing

myConnection.Close()

myConnection = Nothing


--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

 

[Marina]

This typically happens when connections are not closed after they are used.
Perhaps you have other pieces of code running that do this.

 

[Guoqi Zheng]

Thanks for your reply,

I actually really can not find out where I didn't close the connection.

The only Other piece of code which used the connection is below. Do you know
what should I do with it?


Private Function GetMaxPageNr(ByVal fGroupId As Integer, ByVal fPageSize As
Integer) As Integer

Dim ReturnInt As Integer

Dim strConnection As String =
System.Configuration.ConfigurationSettings.AppSettings("strConnect")

Dim myConnection As SqlConnection = New SqlConnection(strConnection)

Dim myCommand As New SqlCommand("MyProc_TotalPages", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

Dim objPara1 As New SqlParameter("@GroupId", SqlDbType.Int, 4)

myCommand.Parameters.Add(objPara1)

objPara1.Direction = ParameterDirection.Input

objPara1.Value = fGroupId

Dim objPara2 As New SqlParameter("@PageSize", SqlDbType.Int, 4)

myCommand.Parameters.Add(objPara2)

objPara2.Direction = ParameterDirection.Input

objPara2.Value = fPageSize

' for output parameters.

Dim objOutputPara As New SqlParameter("@r", SqlDbType.Int, 4)

myCommand.Parameters.Add(objOutputPara)

objOutputPara.Direction = ParameterDirection.Output

' Open the connection.

myConnection.Open()

myCommand.ExecuteReader()

ReturnInt = objOutputPara.Value

Return ReturnInt

myConnection.Close()

myConnection = Nothing

End Function


--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

 

[Marina]

Well, there is your problem:

You have a return statement to return ReturnInt, before you close the
connection. The function exits before the connection is closed - hence the
connection leak.

You should put everything in a try/catch/finally, with the connection being
closed in the Finally to ensure that it always gets closed no matter what.

 

[Guoqi Zheng]

Thanks for your quick reply, will try it..


--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com