Timeout expired the timeout period elapsed prior to obtaining a connection from the pool vb.net

Question

When I run my code I get the following exception:

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

Does anyone know what is going on with this error and how I can solve it?

        Dim cmd As New SqlCommand

        Dim myreader As SqlClient.SqlDataReader
        Dim objconnection As New SqlConnection()

        objconnection = New SqlConnection("Data Source=.;Initial Catalog=AP6;Integrated Security=True")
        objconnection.Open()

        Dim query As String
        query = "select * from students where username='" + txt_user.Text + "'  AND password='" + txt_password.Text + "'"
        Dim objcommand As New SqlCommand(query, objconnection)

        myreader = objcommand.ExecuteReader()
        If myreader.Read() Then

            txt_user.Text = myreader("username")
            txt_password.Text = myreader("password")
            lbl_st_id.Text = myreader("st_id").ToString()
            Response.Redirect("students_tasks.aspx?id=" + myreader("st_id").ToString())

        Else
            lbl_error3.Visible = True

        End If
        myreader.Close()

        objconnection.Close()

    End If

The following may be helpful: https://stackoverflow.com/questions/70317980/display-varbinary-image-in-gridview/70321177#70321177 - it shows how to connect to SQL Server from IIS using Windows authentication. [This one](https://stackoverflow.com/questions/70276565/i-have-written-a-vb-net-code-to-display-image-in-the-image-control-but-not-able/70307947#70307947) shows how to connect to SQL Server using SQL Server authentication. — Tu deschizi eu inchid, Jan 09 '22 at 04:27
My experience, that type of error is due to miss-handling of DB connections "Somewhere" in your application. I've seen it making large number of DB connections in a very short space of time. Wrapping your connection handling with Using clauses throughout your app may be the fix — Hursey, Jan 09 '22 at 19:35
Aside from that, their are a couple other issues for you to look at with this code block. Use SQL parameters rather than string concatenations when constructing sql commands. And you should probably be using "&" rather than "+" for string concatenations in general — Hursey, Jan 09 '22 at 19:38
Dear God: no Using block, SQL Injection issues, and plain-text passwords? It's the tri-fecta! All top three things not to do. — Joel Coehoorn, Jan 10 '22 at 20:08
Also, when you declare an object with the `New` keyword on one line, and then on the line immediately following assign a different value to that same variable, as you did with `objconnection`, it makes me think you don't really understand the core of how object references work in the language. — Joel Coehoorn, Jan 10 '22 at 20:10

Joel Coehoorn · Answer 1 · 2022-01-14T18:18:34.207

It's not entirely clear from the code you have, but one possibility is you need to wrap your connections in Using blocks.

Databases have a limited number of connections that can be active at a time. If you don't close your connections properly, they can hang open, using up valuable connection "slots".

Now I can hear you say, "But I do close my connections." I can also see the code at the end of the question:

objconnection.Close()

However, consider what happens if an exception is thrown. This line of code will never run! Also consider what will happen if you make it to the Response.Redirect() call. Again, this code will never run.

In both those cases, you are now leaving connection objects hanging open, which can eventually lock you out of your database.

Therefore, what you need to do is wrap your connections in a Using block. A Using block will guarantee the object is disposed, even if an exception is thrown or the method exits early. And for SqlConnection objects, disposing the object also properly closes the connection.

Dim query As String "select * from students where username=@UserName AND password=@password"
Using conn As new SqlConnection("Data Source=.;Initial Catalog=AP6;Integrated Security=True"), _
      cmd As New SqlCommand(query, conn)

    'ALWAYS USE PARAMETERIZED QUERIES FOR USER DATA!!!!
    ' Also use the actual column types and lengths here
    cmd.Parameters.Add("@username", SqlDbType.NVarChar, 64).Value txt_user.Text
    'ALWAYS SALT AND HASH YOUR PASSWORDS!!!!
    cmd.Parameters.Add("@password", SqlDbType.NChar, 64).Value = BCrypt.HashPassword(txt_password.Text)

    conn.Open()
    Using myreader As SqlDataReader = objcommand.ExecuteReader()
        If myreader.Read() Then
            'Don't bother setting a textbox... 
            'You will redirect away anyway. No one would see it.
            Response.Redirect("students_tasks.aspx?id=" & myreader("st_id").ToString())
        Else
            lbl_error3.Visible = True
        End If
    End Using
End Using ' The connection WILL be closed here

Please note it's enough to change this code by itself. You must do this everywhere you talk to the database.

The three major changes in this code: proper connection handling, parameterized queries, and hashing passwords — are too important to overlook anywhere... even practice and proof of concept code should do them correctly. Every time.

Timeout expired the timeout period elapsed prior to obtaining a connection from the pool vb.net

1 Answers1