Search (Filter) by date using DateTimePicker in DataGridView in C# and VB.Net

Hi ernestpauld,

Refer below sample.

Database

I have made use of table Employees of Northwind Database.

You can download the database table SQL by clicking the download link below.

Download SQL file

For more details on installing Northwind database in MySql, please refer Download and Install Microsoft Northwind Sample database in MySql.

Form Design

The Form consists of a DateTimePicker, a Button and a DataGridView control.

DateTimePicker - For selecting Date.

Button - For filtering the records.

DataGridView - For displaying the records.

Namespaces

You will need to import the following namespaces.

C#

using System.Data;
using MySql.Data.MySqlClient;

VB.Net

Imports System.Data
Imports MySql.Data.MySqlClient

Code

Inside the Form Load event handler, the DataGridView control is populated by calling the BindGrid method.

For more details on populating DataGridView, please refer DataGridView using Bind (Populate) DataGridView with MySql Database in Windows Forms (WinForms) Application using C# and VB.Net.

Inside the BindGrid method the records are fetched from the MySql database using a DataTable without passing the DateTime.

Inside the Search button click event, the selected DateTime is passed to the BindGrid method and the filtered record are displayed in DataGrdiView.

C#

private void Form1_Load(object sender, EventArgs e)
{
    this.BindGrid();
}

private void BindGrid(string dateTime = "")
{
    string conString = @"Data Source=localhost;port=3306;Initial Catalog=Northwind;User Id=root;password=pass@123";
    using (MySqlConnection con = new MySqlConnection(conString))
    {
        string query = "SELECT EmployeeId, CONCAT(FirstName, ' ' , LastName) 'Name', Country FROM Employees WHERE DATE(BirthDate) = @BirthDate OR @BirthDate IS NULL";
        using (MySqlCommand cmd = new MySqlCommand(query, con))
        {
            if (!string.IsNullOrEmpty(dateTime))
            {
                cmd.Parameters.AddWithValue("@BirthDate", dateTime);
            }
            else
            {
                cmd.Parameters.AddWithValue("@BirthDate", DBNull.Value);
            }
            using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
            }
        }
    }
}

private void btnSearch_Click(object sender, EventArgs e)
{
    this.BindGrid(dateTimePicker1.Value.ToString("yyyy-MM-dd"));
}

VB.Net

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Me.BindGrid()
End Sub

Private Sub BindGrid(Optional dateTime As String = "")
    Dim conString As String = "Data Source=localhost;port=3306;Initial Catalog=Northwind;User Id=root;password=pass@123"
    Using con As New MySqlConnection(conString)
        Dim query As String = "SELECT EmployeeId, CONCAT(FirstName, ' ' , LastName) 'Name', Country FROM Employees WHERE DATE(BirthDate) = @BirthDate OR @BirthDate IS NULL"
        Using cmd As New MySqlCommand(query, con)
            If Not String.IsNullOrEmpty(dateTime) Then
                cmd.Parameters.AddWithValue("@BirthDate", dateTime)
            Else
                cmd.Parameters.AddWithValue("@BirthDate", DBNull.Value)
            End If
            Using sda As New MySqlDataAdapter(cmd)
                Using dt As New DataTable()
                    sda.Fill(dt)
                    dataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Using
End Sub

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    Me.BindGrid(dateTimePicker1.Value.ToString("yyyy-MM-dd"))
End Sub

Screenshots

DataGridView without passing DateTieme

DataGridView with DateTieme