Datetimepicker returns empty rows upon search in C#

Question

Trying to make a Query to Query a Database and return the rows using the DatetimePicker in C# Winform. It returns a very empty row when i click the date.

My code looks like this

    private void dateTimePicker_ValueChanged(object sender, EventArgs e)
    {
        string constring = ConfigurationManager.ConnectionStrings["MySQLDatabaseConnection"].ConnectionString;
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            string sql = "SELECT CAST(visit_date AS DATE) AS VISIT_DATE WHERE visit_date='" + dateTimePicker.Value.Date + "'";

            try
            {
                con.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(sql, con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }

Am i missing something?

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Oct 12 '17 at 11:53
I humbly suggest you need to read it. Because once you fix that issue your query will likely work. And you will no longer be open to SQL injection. — mjwills, Oct 12 '17 at 11:54

score 2 · Accepted Answer · edited Oct 12 '17 at 12:11

I assume the DateTimePicker selects a Date(without time) but in the database you store the appointments with time. If you want all appointments at that date you have to use vistidate >= @datestart and visitdate < DATE_ADD(@datestart, INTERVAL 1 DAY).

Note that you should also always use sql parameters to prevent sql injection and conversion issues.

string sql = @"SELECT * FROM vms_db.waiting_appointments 
               WHERE vistidate >= @datestart
                 AND visitdate < DATE_ADD(@datestart, INTERVAL 1 DAY)";
MySqlDataAdapter da = new MySqlDataAdapter(sql,con);
da.SelectCommand.Parameters.Add("@datestart",  MySqlDbType.DateTime).Value = dateTimePicker.Value.Date;

Works 100% Thank you so much! – Markaz Oct 12 '17 at 12:16 — Markaz, Oct 12 '17 at 12:16

Datetimepicker returns empty rows upon search in C#

1 Answers1