Close connection in sql - Timeout

Question

I have a big issue that one of my sites is failing to load most times. Unfortunately i cannot upload pictures yet. The problem states the following

The time out period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections where in use and max pool size was reached.

And i get a highlighted Line 14: upcmd.Connection.Open(). The error continues further.

I am not an expert at all so what i did so far from researching around what to add the Max Pool Size in <add name="ConnectionString" connectionString="Data Source=yianniscy.db.7015338.hostedresource.com;initial catalog=xxxxxxx;User id=xxxxxx;Password=xxxxxxx;**Max Pool Size=100**" /> in webconfig

The problem still remains although this application is used by a single person and never had this issue before. This started as soon as my hosting provider did an upgrade. At the moment i understand that connections remain open and need to be closed. The problem is that i have no idea what to put and where to put it. If someone can guide me where exactly and what exactly to put i would appreciate it.

Partial Class Default2
Inherits System.Web.UI.Page

Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
    Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

    Using upcmd As New SqlCommand("SELECT login FROM Xuser_tab WHERE name=@logname", conn)

        upcmd.Parameters.Add(New SqlParameter("@Login_name", TextBox1.Text))
        upcmd.Connection.Open()
        Dim password_temp As String

        Try
            Dim dr As SqlDataReader = upcmd.ExecuteReader()

            If dr.Read Then
                password_temp = dr.Item(0)

                If password_temp = TextBox2.Text Then
                    Response.Redirect("~/Controlpanel.aspx")

                Else


                End If
                Image2.Visible = True
                Label1.Visible = True

            End If

Could you show some code where you are using the connection? — Mads, Jul 09 '13 at 15:59
Hi @Molt Line 13 - upcmd.Parameters.Add(New SQL Parameter("@Login_name", Textbox1.Text)) Line 14 - upcmd.Connection.Open() Line 15 Dim password_temp as String. The error happens right after the user login in — Vivere est Vincere, Jul 09 '13 at 16:01
do you have a `upcmd.Connection.Close()` when you are done with the query? If you don't close the connection your pool gets empty fast. — Mads, Jul 09 '13 at 16:08
Need to show all of Protected Sub ImageButton1_Click. Are you closing the connection? — paparazzo, Jul 09 '13 at 18:19

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Using disposes the object after End Using so the connection will be closed when leaving this construct.

Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
      Using upcmd As New SqlCommand("SELECT login FROM Xuser_tab WHERE name=@logname", conn)

        // @logname != @Login_name..
        upcmd.Parameters.Add(New SqlParameter("@Login_name", TextBox1.Text)) 
        upcmd.Connection.Open()
        Dim password_temp As String

        Try
           Dim dr As SqlDataReader = upcmd.ExecuteReader()

           If dr.Read Then

           // code omitted

        End Using
conn.Close() // Optional
End Using
// Here will conn be disposed and connection closed

An explanation of the differences between Close() and Dispose(). Dispose() is the method being called behind the scenes at End Using.

sorry please see above for the code. where should i close the connection exactly? — Vivere est Vincere, Jul 09 '13 at 16:42
i got it . it was simple. good thing i checked because i had to close the connection in many places. thank you — Vivere est Vincere, Jul 09 '13 at 19:27

score 0 · Answer 2 · answered Jul 09 '13 at 16:01

0

I'm sure the problem is in your code, check the following:

are connection closed?
are you using usings around database connection objects?
how is your error handling and connection closing?

For a single user website you do not need a large connection pool, you normally use only one connection at once. With some code we can be more specific.

answered Jul 09 '13 at 16:01

Peter

27,590
8
64
84

Regarding your 1st point i think i am not closing the connections. This is based on my research and the error code. the problem is that i dont know how to close the connection or where to put the code. The above code i mentioned is not helpful? thank you – Vivere est Vincere Jul 09 '13 at 16:07
should i be using both conn.close() and upcmd.connection.close() – Vivere est Vincere Jul 09 '13 at 17:09
@VivereestVincere Just disposing the connection is good enough, you only need to call `Close()` if you plan on calling other methods after closing the object, if you are done just let it be disposed by wrapping it in a using block. – Scott Chamberlain Jul 09 '13 at 18:15

Close connection in sql - Timeout

2 Answers2