How to solve the "Connection Was Not Closed, The connections current state is open" in c#?

Question

So I'm trying to practice c# and stumbled with the connection error, I already stated that my connection will be closed but it tells me that my connection is still open. I really have no idea what's wrong with this.

public void getdept()
{
   con.Open();

   string query = "SELECT * FROM positions where PositionName=" + cbxposname.SelectedValue.ToString() + "";

   SqlCommand cmd = new SqlCommand(query, con);

   DataTable dt = new DataTable();

   SqlDataAdapter sda = new SqlDataAdapter(query, con);

   sda.Fill(dt);
       
   foreach (DataRow dr in dt.Rows)
   {
       txtdeptname.Text = dr["Department"].ToString();
   }
        
   con.Close();
}

Any tips is welcomed!

Answer 1

You are unnecessary opening and closing connections in your case. It's not needed here.

Your code should look like this.

using (SqlConnection con = new SqlConnection(connetionString))
{
    using (DataTable dt = new DataTable())
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(sql, con))
        {
            sda.Fill(dt);

            foreach (DataRow dr in dt.Rows)
            {
                txtdeptname.Text = dr["Department"].ToString();
            }
        }

    }

}

A few suggestions also, Please don't use * in the query, instead, use column names, Please use NOLOCK in the Query if it's required in your case and use the parameterized query.