Connection string error when multiple requests at the same time

Question

I am having a problem with my web application in ASP.NET.

My error is:

The connection was not closed the connection's current state is open

when both clients submit a request in the connection string.

This is my code:

Public Sub populateGrid(ByVal sql As String, ByVal grd As GridView)
    Try
        cmd = New SqlCommand(sql)
        da = New SqlDataAdapter(sql, conn)
        conn.Open()
        ds = New DataSet
        da.Fill(ds)
        grd.DataSource = ds
        grd.DataBind()
        conn.Close()
    Catch ex As Exception
        Throw ex
    Finally
        conn.Close()
    End Try
End Sub

How can I hand multiple requests that my connection string can handle?

Please help!

Thank you!

Why are you closing the connection in exception block as well as in the finally code block? Only one close would be enough which would be finally. — Jamshaid K., May 03 '20 at 16:00
My mistake. But in that try catch it still does not close the connect at the finally part. Error is still The connection was not closed the connection's current state is open — crocscoc, May 03 '20 at 16:03
Does this answer your question? [The connection was not closed the connection's current state is open](https://stackoverflow.com/questions/13343236/the-connection-was-not-closed-the-connections-current-state-is-open) — Suraj Kumar, May 03 '20 at 16:55

score 1 · Answer 1 · answered May 03 '20 at 16:01

1

You can check whether the connection is already open or not before opening the connection as shown below.

if(con.State == ConnectionState.Closed)
{
   conn.Open()
)

answered May 03 '20 at 16:01

Suraj Kumar

5,547
8
20
42

I think this is not the issue, going through the exception, it seems he is closing the connection and it was not being closed. – Jamshaid K. May 03 '20 at 16:03
Can this handle multiple request from different users? – crocscoc May 03 '20 at 16:04
@JamshaidKamran If the connection is already open and you try to open then this type of error occurs. You can check this [SO answer post.](https://stackoverflow.com/a/41898443/10532500) – Suraj Kumar May 03 '20 at 16:05
@JamshaidKamran Yes that is it. I tested this and open 2 browsers. Clicked the submit button at the same time. That's when the error show. But if I click the submit button and delay the other one. The process proceeds. My only problem is to handle multiple submit request. – crocscoc May 03 '20 at 16:06
1

I agree with you Suraj Kumar, checking the connection state would be a good idea, but I doubt it will help him in the longer run as he is somewhere not closing the connection properly which could be a headache to manage. – Jamshaid K. May 03 '20 at 16:08

score 0 · Answer 2 · answered May 03 '20 at 16:58

0

I have found a solution in this. I added thead.sleep if the connection is open.

Here is the code:

Public Sub populateGrid(ByVal sql As String, ByVal grd As GridView)
        Try

            cmd = New SqlCommand(sql)
            da = New SqlDataAdapter(sql, conn)
            If conn.State = ConnectionState.Open Then
                System.Threading.Thread.Sleep(3000)
            End If
            conn.Open()
            ds = New DataSet
            da.Fill(ds)
            grd.DataSource = ds
            grd.DataBind()
            conn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            conn.Close()
        End Try
    End Sub

I added 3 seconds so the 1st process can continue and when its finished the other one will proceed

answered May 03 '20 at 16:58

crocscoc

1
3

This can't be a good solution. It is not at all clear why multiple users can't hit the site without any issue. Each user and request and running of your code is for the most part stateless. You don't show where and how the conn object is setup here. I would assume that the conn object reads the connection string, creates the conn object, and THEN returns the connection object. I don't see why this would cause a issue with 1 or 20 users. Each user loading a web page will be using + calling that code in their own context. IIS should be threading this for you. – Albert D. Kallal May 04 '20 at 02:49
I would check the sql server table - ensure that it has correct indexing on it to reduce full table scan locks. It should not matter if 10 or 20 people load + run that web page. Something else here is wrong, and a delay in the actual code is a massive band aid solution and not a real fix. The conn object you have is created from scratch each time and for each request? Something is persisting the conn object when it should not be. So, ensure that no static declares etc. exist here. – Albert D. Kallal May 04 '20 at 02:54

Connection string error when multiple requests at the same time

2 Answers2