Checkboxlist if status is true in mysql the name must be checked

Question

I have a two columns the one is checkStatus and the other one is EmployeeName

i already get all the names but i dont know how to get the checkStatus if the status is true then it must be checked and if not it not checkes

        string Query = "Select Distinct EmployeeName  From tblschedule Where ScheduleFrom BETWEEN '"+clsSQLcon.DateFrom1+"' AND '"+ clsSQLcon.DateTo1 + "' "; ;
  
        using (MySqlCommand cmd  = new MySqlCommand(Query, conn))
        {
            try
            {
                conn.Open();
                
                using (MySqlDataReader myReader = cmd.ExecuteReader())
                {
                    while (myReader.Read())
                    {
                        string employeeName = (myReader.IsDBNull(myReader.GetOrdinal("EmployeeName")) ?
                                            string.Empty : myReader["EmployeeName"].ToString());
                        checkedListBox1.Items.Add(employeeName);
                    }

                }

            }

this is my code to get the names..

Answer 1

@ Jonjon Candare. You can try to refer to my code. I set checkStatus to BIT type in the database. Add checkStatus to the query statement and read checkStatus when reading data. Then make a judgment and set the SetItemChecked of checkedListBox1.

My cs code is as follows:

private void Form1_Load(object sender, EventArgs e)
        {
            string constr = @"Connstr";
            MySqlConnection con = new MySqlConnection(constr);
              string query = " Select Distinct EmployeeName,checkStatus From tblschedule Where ScheduleFrom BETWEEN '"+clsSQLcon.DateFrom1+"' AND '"+ clsSQLcon.DateTo1 + "' " ";
            con.Open();
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                try
                {
                    using (MySqlDataReader myReader = cmd.ExecuteReader())
                    {
                        while (myReader.Read())
                        {
                            string employeeName = (myReader.IsDBNull(myReader.GetOrdinal("EmployeeName")) ?
                                             string.Empty : myReader["EmployeeName"].ToString());
                            string checkStatus = (myReader.IsDBNull(myReader.GetOrdinal("checkStatus")) ?
                                            string.Empty : myReader["checkStatus"].ToString());
                            checkedListBox1.Items.Add(employeeName);
                            if (checkStatus == "True")
                            {
                                checkedListBox1.SetItemChecked(checkedListBox1.Items.Count - 1, true);
                            }
                            if (checkStatus == "False")
                            {
                                checkedListBox1.SetItemChecked(checkedListBox1.Items.Count - 1, false);
                            }
                        }
                    }
                }
                Catch(MySqlException)
                {
                }
            }
        }

The result is shown in the picture :

Answer 2

    string Query = "Select Distinct EmployeeName,checkStatus From tblschedule Where ScheduleFrom 
    BETWEEN '"+clsSQLcon.DateFrom1+"' AND '"+ clsSQLcon.DateTo1 + "' "; ;
  
        using (MySqlCommand cmd  = new MySqlCommand(Query, conn))
        {
            try
            {
                conn.Open();
                
                using (MySqlDataReader myReader = cmd.ExecuteReader())
                {
                    while (myReader.Read())
                    {
                        string employeeName = (myReader.IsDBNull(myReader.GetOrdinal("EmployeeName")) ?
                                            string.Empty : myReader["EmployeeName"].ToString());
    string checkStatus= (myReader.IsDBNull(myReader.GetOrdinal("checkStatus")) ?
                                            string.Empty : myReader["checkStatus"].ToString());
                        checkedListBox1.Items.Add(employeeName);
    if(checkStatus=="true")
      { checkedListBox1.SetItemChecked(checkedListBox1.Items.Count-1, true);   
}         
                    }

                }

            }

Answer 3

From what you've described you can just modify the query to include the status?
You should probably also parameterize your query.
An example of how your query could look:

       string Query = "SELECT EmployeeName, checkStatus FROM tblschedule WHERE ScheduleFrom BETWEEN @StartDate AND @EndDate ";

        using (MySqlCommand cmd = new MySqlCommand(Query, conn))
        {
            cmd.Parameters.AddWithValue("@StartDate", (MySqlDbType.DateTime)clsSQLcon.DateFrom1);
            cmd.Parameters.AddWithValue("@EndDate", (MySqlDbType.DateTime)clsSQLcon.DateTo1);
            conn.Open();
         try{
            using (MySqlDataReader myReader = cmd.ExecuteReader())
            {
                while (myReader.Read())
                {
                    string employeeName = myReader.IsDBNull(0) ? string.Empty : myReader.GetString(0);
                    bool cstatus = myReader.GetBoolean(1);
                    //do something with name and status and check distinct names in code instead
                }
            }
            }
          catch(MySqlException){//Exception handling}
        }

If you want only distinct names you can add a "Group by (EmployeeName)" in the query but if there are multiple entries in the timeframe provided you'll just get a random one.