'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.' - C#

Question

The code:

        private void btnSave_Click(object sender, EventArgs e)
        {
            
            using (con = new SqlConnection(connectionString))
            {
                SqlCommand cmd;
                switch (action)
                {
                    case 'a':
                        cmd = new SqlCommand("INSERT INTO tbl_User (userName, userPass) Values (@username, @userpass)", con);
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@username", txtUN.Text.Trim());
                        cmd.Parameters.AddWithValue("@userpass", txtPW.Text.Trim());
                        cmd.ExecuteNonQuery();
                        cmd.Connection.Close();

                        MessageBox.Show("Added new User Successfully!", "User Maintenance", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        break;
                    case 'u':
                        break;
                    case 'd':
                        break;
                }
            }
            LoadData();
        }

This error shows up when I click the save button:

System.InvalidOperationException: 'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.'

I am using the using keyword, if I'm correct doesn't using automatically opens and closes the sqlConnection? If so, why does it returns an error that I need an open and available connection for the ExecuteNonQuery()? How can I fix this? Should I just simply add the Open and Close syntax? or is there a better way to do this

The LoadData() method:

public void LoadData()
        {
            //keyword using automatically closes the connection to the sql
            using (con = new SqlConnection(connectionString))
            {
                SqlDataAdapter sda = new SqlDataAdapter("Select * from tbl_User", con);
                DataTable dt = new DataTable();
                sda.Fill(dt);

                DGUserData.DataSource = dt;
            }
        }

No, using doesn't open the connection. Exiting from the using block _disposes_ the connection and in the Dispose method the connection closes itself but there is not an automatic open of the connection — Steve, Jan 12 '22 at 10:45
`using` takes care of cleanup (close), but you have to Open explicitely. — Klaus Gütter, Jan 12 '22 at 10:47
Yes or create a method that returns an opened connection and use it in the assignement inside the using — Steve, Jan 12 '22 at 10:49
@Steve I added the `LoadData()` method in the question. When I run the form and save it it works fine, but the method doesn't have `con.Open()` and it works just fine. Why is that? — Richard Johnson, Jan 12 '22 at 10:55
The SqlDataAdapter opens automatically the connection if it is closed (or better the Fill method does it) — Steve, Jan 12 '22 at 10:56
Looks like you're storing the `SqlConnection` object in a class-level field. That's a bad idea, and can lead to problems. It's better to use a local variable, declared directly within the `using` block, and lose the field. — Richard Deeming, Jan 13 '22 at 15:46

score 1 · Accepted Answer · answered Jan 13 '22 at 15:42

You can simply use con.Open() so connection will just get establish in case of closed state or you can add a check before opening connection (snippet below).

public void LoadData()
        {
            //keyword using automatically closes the connection to the sql
            using (con = new SqlConnection(connectionString))
            {
                  if (con.State == ConnectionState.Closed)
                  con.Open();

                SqlDataAdapter sda = new SqlDataAdapter("Select * from tbl_User", con);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                DGUserData.DataSource = dt;
            }
        }

'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.' - C#

1 Answers1