'The connection was not closed. Current state of the connection: open.'

Question

I want to add a category for products in the program, when I click the add button, I get an error System.InvalidOperationException: 'The connection was not closed. Current state of the connection: open.' how can I solve it?

private void btnKategoriEkle_Click(object sender, EventArgs e)
    {
        kategorikontrol();
        if (durum == true)
        {
            baglanti.Open();
            SqlCommand komut = new SqlCommand("insert into kategoribilgileri(kategori) values('"+ txtKategori.Text+"')", baglanti);
            komut.ExecuteNonQuery();
            baglanti.Close();
            MessageBox.Show("Kategori Eklendi");
        }
        else
        {
            MessageBox.Show("Böyle bir kategori var", "Uyarı");
        }
        txtKategori.Text = "";
    }

check if the connection is already open, before trying to open it — nbk, May 08 '22 at 16:13
please use ***parameterised queries*** - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for ***[SQL Injection attacks](https://bobby-tables.com/)***. — Franz Gleichmann, May 08 '22 at 16:21
So you have created a global object for your connection. This is a very bad practice that leads to this kind of errors. Use a local connection object inside a using statement. There is a thing called _connection pooling_ that should remove your thoughts about performances. (And yes as @FranzGleichmann says, never use string concatenation to build a sql statement. It is a recipe for a disaster) — Steve, May 08 '22 at 16:26

score 1 · Answer 1 · answered May 08 '22 at 16:37

You're better off localizing your connection and as mentioned use parameters for the where condition e.g.

public static (bool success, Exception exception) Insert(string category)
{
    using (var cn = new SqlConnection("TODO"))
    {
        using (var cmd = new SqlCommand { Connection = cn })
        {
            cmd.CommandText = "INSERT INTO kategoribilgileri(kategori) VALUES (@category);";
            cmd.Parameters.Add("@category", SqlDbType.NChar).Value = category;
            try
            {
                cn.Open();
                return (cmd.ExecuteNonQuery() == 1, null);
            }
            catch (Exception ex)
            {
                return (false, ex);
            }
        }
    }
}

Adding to this (in case op is concerned): connection pooling is used in the background so don't worry about opening and disposing connections. — ProgrammingLlama, May 08 '22 at 16:44

'The connection was not closed. Current state of the connection: open.'

1 Answers1