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

Question

I am facing this problem since yesterday I tried so many solutions but nothing worked

It keeps telling me the the connection stat is open while I did con.close();

 private void cashier_Load(object sender, EventArgs e)
    {
        int a;
        con.Open();
        string query = "Select Max (invno) From Invoicesdata";
        cmd = new SqlCommand(query, con);
        SqlDataReader reader;
        reader = cmd.ExecuteReader();
        if (reader.Read())
        {
            string val = reader[0].ToString();
            if (val == "")
            {
                Invoicenm.Text = "1";
            }
            else
            {
                a = Convert.ToInt32(reader[0].ToString());
                a = a + 1;
                Invoicenm.Text = a.ToString();
            }
        }
        con.Close(); //I did connection close but nope 
        receiptgrid.Rows.Clear();
    }

    private void textBox1_KeyDown(object sender, KeyEventArgs e)
    {
        cmd = new SqlCommand("SELECT * FROM Products WHERE Item_Code = @BarCode", con);
        cmd.Parameters.Add(new SqlParameter("@BarCode", Productstxt.Text));
        con.Open();      //here I am facing the problem
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {

            foreach (DataGridViewRow row in receiptgrid.Rows)

This is my connection string SqlConnection con =new SqlConnection("Data Source=strongspider.ddns.net;Initial Catalog=POS;Persist Security Info=True;User ID=sa;Password=****Password**"); // <== this is the (POS) Database.

Can you post the rest of the code for textBox1_keydown? I see you open a connection there but the code is cut off before you close the connection — Ken Tucker, Feb 10 '18 at 12:57
At least use `try finally` to guarantee it being closed, because with your current if an exception get thrown (and looking at your code there are multiple places where this can happen) the connection will stay open. — ckuri, Feb 10 '18 at 12:59
All you have to do is search using the error message to be rewarded with 25,600 hits *on this site alone*! — Ňɏssa Pøngjǣrdenlarp, Feb 10 '18 at 15:59

score 2 · Answer 1 · edited Feb 10 '18 at 13:40

2

I suggest you follow an "open late, close early" pattern rather than share the same connection and wrap in a using block. This will ensure the connection is closed and avoid the need for you to explicitly invoke Close and leverage connection pooling.

using(var con = new SqlConnection(connectionString))
using(var cmd = new SqlCommand(query, con))
{
    //do command stuff here
}

edited Feb 10 '18 at 13:40

Michał Turczyn

32,028
14
47
69

answered Feb 10 '18 at 12:59

Dan Guzman

43,250
3
46
71

It grinds my gears when I see `IDisposable` outside `using` with no good reason... +1 :) It would prevent this problem from happening... – Michał Turczyn Feb 10 '18 at 13:41

score 0 · Answer 2 · answered Feb 10 '18 at 12:56

0

Run out of all the un-logic sequance, and be in safe by using SqlConnection.State

so instead of using

 con.Open();

use:-

if (con.State == con.Closed)
{
    con.Open();
}

answered Feb 10 '18 at 12:56

ahmed abdelqader

3,409
17
36

I did that, when I do so the reader gives me error that it requires an open connection ... – HaMoOoOd 25 Feb 10 '18 at 12:59

SSD · Answer 3 · 2018-02-10T13:11:32.550

Since you are using global connection variable, you are having trouble managing connection state.

Try to move declaration and usage of connection object where it is required. And use using{} block for connection and SqlDataReader objects.

This will relieve you from the explicit requirement of closing connection

private void cashier_Load(object sender, EventArgs e)
{
    int a;
    using(var conn = new SqlConnection(YOURCINNECTIONSTRING))
    {
        con.Open();
        string query = "Select Max (invno) From Invoicesdata";
        var cmd = new SqlCommand(query, con);
        using(SqlDataReader reader = cmd.ExecuteReader())
         {
             if (reader.Read())
             {


             }
         }
      }
}

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

3 Answers3