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

Issue

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;
            }
        }

Solution

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;
            }
        }

Answered By – Arsal Hussain

Answer Checked By – Marilyn (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.