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

Question

Basically the idea is I want the "cbRoomType" combo box to filter out the rooms with that chosen type (also the rooms that are available according to the database) in the "tbRoomNumber" text box.

https://i.stack.imgur.com/Bpnlf.png

At first it worked fine when I filtered out only the available room with the fillRoomcombo() method then I decided to combine it with the "Room Type" so I moved the whole method's code to the cbRoomType combobox's action and modified it a little bit to get what I want. But it just straight up gave me that error every time I chose something different from what I'd declared here cbRoomType.SelectedIndex = 1;

I checked and all my connections are closed but I still get this error.

Even tried the fixes I found on the internet but still couldn't fix it.

Here's the code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace HotelManagementSystem
{
    public partial class Reservations : Form
    {
        SqlConnection Con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\Lecture\Programming\ASM\ASM2\Hotel Management System\HotelManagementSystem\db\db_hotel.mdf;Integrated Security=True;Connect Timeout=30");
        public Reservations()
        {
            InitializeComponent();
        }
        private void timer1_Tick(object sender, EventArgs e)
        {
            lbDate.Text = DateTime.Now.ToLongTimeString();
        }
        public void Populate()
        {
            Con.Open();
            string MyQuery = "Select * from Reservation_tbl";
            SqlDataAdapter da = new SqlDataAdapter(MyQuery, Con);
            SqlCommandBuilder cbuilder = new SqlCommandBuilder(da);
            var ds = new DataSet();
            da.Fill(ds);
            ReservationGridView.DataSource = ds.Tables[0];
            Con.Close();
        }
        DateTime today;
        private void Reservations_Load(object sender, EventArgs e)
        {
            lbDate.Text = DateTime.Now.ToLongTimeString();
            timer1.Start();
            Populate();
            today = DateIn.Value;
            //fillRoomcombo();
            fillClientcombo();
        }
        public void UpdateRoomState()
        {
            string newstate = "Unavailable";
            Con.Open();
            string myquery = "UPDATE Room_tbl set RoomStatus = '"+ newstate + "' where RoomID = '"+Convert.ToInt32(cbRoomNumber.SelectedValue.ToString()) +"';";
            SqlCommand cmd = new SqlCommand(myquery, Con);
            cmd.ExecuteNonQuery();
            Con.Close();
            //fillRoomcombo();
        }
        public void UpdateRoomState2()
        {
            string newstate2 = "Available";
            int roomid = Convert.ToInt32(ReservationGridView.SelectedRows[0].Cells[2].Value.ToString());
            Con.Open();
            string myquery = "UPDATE Room_tbl set RoomStatus = '" + newstate2 + "' where RoomID = '" + roomid + "';";
            SqlCommand cmd = new SqlCommand(myquery, Con);
            cmd.ExecuteNonQuery();
            Con.Close();
            //fillRoomcombo();
        }
        private void bttnReservationAdd_Click(object sender, EventArgs e)
        {
            if (tbReservationID.Text == "")
            {
                MessageBox.Show("Reservation ID cannot be empty.");
            }
            else
            {
                Con.Open();
                SqlCommand cmd = new SqlCommand("Insert into Reservation_tbl values (" + tbReservationID.Text + ", '" + cbClient.Text + "', '" + cbRoomNumber.Text + "', '"+ cbRoomType.SelectedValue.ToString() + "', '" + DateIn.Value + "', '" + DateOut.Value + "')", Con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Reservation added successfully.");
                Con.Close();
                UpdateRoomState();
                Populate();
            }
        }

        private void DateIn_ValueChanged(object sender, EventArgs e)
        {
            int res = DateTime.Compare(DateIn.Value, today);
            if (res < 0)
            {
                MessageBox.Show("Invalid check-in day.");
            }
        }

        private void DateOut_ValueChanged(object sender, EventArgs e)
        {
            int res = DateTime.Compare(DateOut.Value, today);
            if (res < 0)
            {
                MessageBox.Show("Invalid check-out day.");
            }
        
        }
        public void fillClientcombo()
        {
            Con.Open();
            SqlCommand cmd = new SqlCommand("select ClientName from Client_tbl", Con);
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("ClientName", typeof(string));
            dt.Load(rdr);
            cbClient.ValueMember = "ClientName";
            cbClient.DataSource = dt;
            Con.Close();
        }
        /* public void fillRoomcombo()
        {
            Con.Open();
            string roomstate = "Available";
            SqlCommand cmd = new SqlCommand("select RoomID from Room_tbl where RoomStatus ='" + roomstate + "'", Con);
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("RoomID", typeof(int));
            dt.Load(rdr);
            cbRoomNumber.ValueMember = "RoomID";
            cbRoomNumber.DataSource = dt;
            Con.Close();
        }*/
        private void bttnReservationEdit_Click(object sender, EventArgs e)
        {
            if (tbReservationID.Text == "")
            {
                MessageBox.Show("Reservation ID cannot be empty.");
            }
            else
            {
                Con.Open();
                string myquery = "UPDATE Reservation_tbl set Client ='" + cbClient.Text + "', DateIn ='" + DateIn.Value + "', DateOut ='" + DateOut.Value + "' where ResID = '" + tbReservationID.Text + "';";
                SqlCommand cmd = new SqlCommand(myquery, Con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Reservation edited successfully.");
                Con.Close();
                Populate();
            }
        }

        private void ReservationGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            tbReservationID.Text = ReservationGridView.SelectedRows[0].Cells[0].Value.ToString();
            cbClient.Text = ReservationGridView.SelectedRows[0].Cells[1].Value.ToString();
            cbRoomNumber.Text = ReservationGridView.SelectedRows[0].Cells[2].Value.ToString();
        }

        private void bttnReservationRemove_Click(object sender, EventArgs e)
        {
            if (tbReservationID.Text == "")
            {
                MessageBox.Show("Reservation ID cannot be empty.");
            }
            else
            {
                Con.Open();
                string query = "delete from Reservation_tbl where ResID = " + tbReservationID.Text + "";
                SqlCommand cmd = new SqlCommand(query, Con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Reservation deleted successfully.");
                Con.Close();
                UpdateRoomState2();
                Populate();
            }
        }

        private void bttnRoomReset_Click(object sender, EventArgs e)
        {
            Populate();
        }
        private void bttnReservationSearch_Click(object sender, EventArgs e)
        {
            Con.Open();
            string MyQuery = "Select * from Reservation_tbl where ResID = '"+tbReservationSearch.Text+"';";
            SqlDataAdapter da = new SqlDataAdapter(MyQuery, Con);
            SqlCommandBuilder cbStaffuilder = new SqlCommandBuilder(da);
            var ds = new DataSet();
            da.Fill(ds);
            ReservationGridView.DataSource = ds.Tables[0];
            Con.Close();
        }


        private void bttnBack_Click(object sender, EventArgs e)
        {
            MainForm mainForm = new MainForm();
            mainForm.Show();
            this.Hide();
        }

        private void cbRoomType_SelectedIndexChanged(object sender, EventArgs e)
        {
            Con.Open();
            string roomstate = "Available";
            cbRoomType.SelectedIndex = 1;
            SqlCommand cmd = new SqlCommand("select RoomID from Room_tbl where RoomType ='" + cbRoomType.SelectedItem.ToString() + "' and RoomStatus ='" + roomstate + "'", Con);
            SqlDataReader rdr;
            rdr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("RoomID", typeof(int));
            dt.Load(rdr);
            cbRoomNumber.ValueMember = "RoomID";
            cbRoomNumber.DataSource = dt;
            Con.Close();
        }
    }
}

Answer 1

You should use Using statement for database queries because they prevents from errors of type connection not closed etc. Here is a sample of getting data from database using statement:

public static List<CalendarParts> GetParts_ByDate()
        {
            using (IDbConnection cnn = new SQLiteConnection(DBConnection.Conn()))
            {
                var output = cnn.Query<CalendarParts>("Select p.id,c.Name as Calendar,p.PartName," +
                    "p.MaintenanceDate from CalendarParts_Table p inner join Calendars_Table as c " +
                    "on c.id=p.Calendar order by case when p.MaintenanceDate='' or " +
                    "p.MaintenanceDate is null then 1 else 0 end,date(p.MaintenanceDate)",
                    new DynamicParameters() );
                return output.ToList();
            }
        }