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.

[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.Configur ation.Configura tionSettings.Ap pSettings("strC onnect")

Dim myConnection As SqlConnection = New SqlConnection(s trConnection)

Dim myCommand As New SqlCommand("MyP roc_XXXX", myConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure

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

myCommand.Param eters.Add(objPa ra1)

objPara1.Direct ion = ParameterDirect ion.Input

objPara1.Value = MsgId

' Open the connection.

myConnection.Op en()

objReader = myCommand.Execu teReader()

MsgFull.DataSou rce = objReader

MsgFull.DataBin d()

objReader.Close ()

objReader = Nothing

myConnection.Cl ose()

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" <[e-mail]> wrote in message
news:Oe******** ******@tk2msftn gp13.phx.gbl...

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.Configur ation.Configura tionSettings.Ap pSettings("strC onnect")

Dim myConnection As SqlConnection = New SqlConnection(s trConnection)

Dim myCommand As New SqlCommand("MyP roc_XXXX", myConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure

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

myCommand.Param eters.Add(objPa ra1)

objPara1.Direct ion = ParameterDirect ion.Input

objPara1.Value = MsgId

' Open the connection.

myConnection.Op en()

objReader = myCommand.Execu teReader()

MsgFull.DataSou rce = objReader

MsgFull.DataBin d()

objReader.Close ()

objReader = Nothing

myConnection.Cl ose()

myConnection = Nothing
--
Kind regards

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

[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(By Val fGroupId As Integer, ByVal fPageSize As
Integer) As Integer

Dim ReturnInt As Integer

Dim strConnection As String =
System.Configur ation.Configura tionSettings.Ap pSettings("strC onnect")

Dim myConnection As SqlConnection = New SqlConnection(s trConnection)

Dim myCommand As New SqlCommand("MyP roc_TotalPages" , myConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure

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

myCommand.Param eters.Add(objPa ra1)

objPara1.Direct ion = ParameterDirect ion.Input

objPara1.Value = fGroupId

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

myCommand.Param eters.Add(objPa ra2)

objPara2.Direct ion = ParameterDirect ion.Input

objPara2.Value = fPageSize

' for output parameters.

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

myCommand.Param eters.Add(objOu tputPara)

objOutputPara.D irection = ParameterDirect ion.Output

' Open the connection.

myConnection.Op en()

myCommand.Execu teReader()

ReturnInt = objOutputPara.V alue

Return ReturnInt

myConnection.Cl ose()

myConnection = Nothing

End Function
--
Kind regards

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

"Marina" <so*****@nospam .com> wrote in message
news:e6******** ******@TK2MSFTN GP10.phx.gbl...

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

[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" <[e-mail]> wrote in message
news:uo******** ******@tk2msftn gp13.phx.gbl...

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(By Val fGroupId As Integer, ByVal fPageSize As Integer) As Integer

Dim ReturnInt As Integer

Dim strConnection As String =
System.Configur ation.Configura tionSettings.Ap pSettings("strC onnect")

Dim myConnection As SqlConnection = New SqlConnection(s trConnection)

Dim myCommand As New SqlCommand("MyP roc_TotalPages" , myConnection)

myCommand.Comma ndType = CommandType.Sto redProcedure

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

myCommand.Param eters.Add(objPa ra1)

objPara1.Direct ion = ParameterDirect ion.Input

objPara1.Value = fGroupId

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

myCommand.Param eters.Add(objPa ra2)

objPara2.Direct ion = ParameterDirect ion.Input

objPara2.Value = fPageSize

' for output parameters.

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

myCommand.Param eters.Add(objOu tputPara)

objOutputPara.D irection = ParameterDirect ion.Output

' Open the connection.

myConnection.Op en()

myCommand.Execu teReader()

ReturnInt = objOutputPara.V alue

Return ReturnInt

myConnection.Cl ose()

myConnection = Nothing

End Function
--
Kind regards

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

"Marina" <so*****@nospam .com> wrote in message
news:e6******** ******@TK2MSFTN GP10.phx.gbl...

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 quick reply, will try it..
--
Kind regards

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

"Marina" <so*****@nospam .com> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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.