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.
– Jamshaid K., Commented May 3, 2020 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
– crocscoc, Commented May 3, 2020 at 16:03
Does this answer your question? The connection was not closed the connection's current state is open — Suraj Kumar
– Suraj Kumar, Commented May 3, 2020 at 16:55

Suraj Kumar · Accepted Answer · 2020-05-03 16:01:01Z

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 3, 2020 at 16:01

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jamshaid K. Over a year ago

I think this is not the issue, going through the exception, it seems he is closing the connection and it was not being closed.

crocscoc Over a year ago

Can this handle multiple request from different users?

Suraj Kumar Over a year ago

@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.

crocscoc Over a year ago

@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.

Jamshaid K. Over a year ago

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.

crocscoc · Accepted Answer · 2020-05-03 16:58:06Z

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 3, 2020 at 16:58

crocscoc

13 bronze badges

2 Comments

Albert D. Kallal Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

Connection string error when multiple requests at the same time

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related