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

G

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
 
M

Marina

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

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
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top