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

Discussion in 'ASP .Net' started by Guoqi Zheng, Jun 3, 2004.

  1. Guoqi Zheng

    Guoqi Zheng Guest

    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
    Guoqi Zheng, Jun 3, 2004
    #1
    1. Advertising

  2. Guoqi Zheng

    Marina Guest

    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" <> wrote in message
    news:...
    > 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, Jun 3, 2004
    #2
    1. Advertising

  3. Guoqi Zheng

    Guoqi Zheng Guest

    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" <> wrote in message
    news:...
    > 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, Jun 3, 2004
    #3
  4. Guoqi Zheng

    Marina Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > This typically happens when connections are not closed after they are

    > used.
    > > Perhaps you have other pieces of code running that do this.
    > >

    >
    >
    Marina, Jun 3, 2004
    #4
  5. Guoqi Zheng

    Guoqi Zheng Guest

    Thanks for your quick reply, will try it..


    --
    Kind regards

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

    "Marina" <> wrote in message
    news:%...
    > 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, Jun 3, 2004
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page