CODEWITHSUNDEEP
c#mysqldatefilterdatagridview
Yagiz
Yagiz

Reputation: 43

Filter DataGridView based on Date values from MySQL in C#

Greetings from a rookie.

I'm writing a time shift calendar (shown below) for my geology crew.

DataGridView

I searched a lot but couldn't find a proper solution. I would like to limit the records shown in DataGridView based on the current month (not for an interval). For example, team members should be able to see the records that belong to August (if the month is August). Next month, it should be fresh.

I got a "Retrieve" button that populates the DataGridView and a Date column getting from MySQL. The program should make a search in the "Date" column (named as dateStart in DB) in DataGridView (or MySQL table), and show only the records belongs to August 2020. My date format is YYYY-MM-DD.

//RETRIEVE FROM DB
    private void retrieve()
    {
        dtgList.Rows.Clear();

        dtpDate.Format = DateTimePickerFormat.Custom;


        //SQL STMT
        string sql = $"SELECT * FROM {tbl_staffCode} ";
        cmd = new MySqlCommand(sql, con);

        //OPEN CON,RETRIEVE,FILL DGVIEW
        try
        {
            con.Open();

            adapter = new MySqlDataAdapter(cmd);

            adapter.Fill(dt);

            //LOOP THRU DT
            foreach (DataRow row in dt.Rows)
            {
                populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString());
            }



            con.Close();

            //CLEAR DT
            dt.Rows.Clear();
        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.Message);
            con.Close();
        }
    }

Thank you so much in advance.

Upvotes: 1

Views: 317

Answers (1)

Yagiz
Yagiz

Reputation: 43

Thanks to Chetan Ranpariya for broadening my horizons. Solution is here;

DateTime dtime = DateTime.Now;
        int month = dtime.Month;
        int year = dtime.Year;

        //SQL STMT
        string sql = $"SELECT * FROM {tbl_staffCode} WHERE MONTH(DateStart) = {month} AND YEAR(DateStart) = {year} ";
        cmd = new MySqlCommand(sql, con);

Upvotes: 2

Related Questions