How to display record using WHERE condition

Question

I am creating an application using C# and MySQL. MySQL table (tbl_sales) has four fields(sale_item, sale_qty, added_n and last_updated_on). The data type of last updated_on field is DateTime. I want to display the records in the DataGridView according to the following SQL. SELECT * FROM tbl_sales WHERE last_updated_on >=" + dateTimePicker1.Text;

I got the following error You have and error in your SQL syntax; check the manual that corrosponds to yout MySQL server version for the right syntax to use near " at line 1

I used the following C# code

private void button1_Click(object sender, EventArgs e)
    {
        string query;
        try
        {
            conLocal.Open();
            query = "SELECT * FROM tbl_sales WHERE last_updated_on >=" + textBox1.Text; // Convert.ToString(dateTimePicker1.Text);

            cmdLocal = new MySqlCommand();
            cmdLocal.Connection = conLocal;
            cmdLocal.CommandText = query;
            da = new MySqlDataAdapter();
            da.SelectCommand = cmdLocal;
            dt = new DataTable();
            da.Fill(dt);

            dataGridView1.DataSource = dt;

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

        finally
        {
            da.Dispose();
            conLocal.Close();
        }
}

MySQL table data Without WHERE condition With WHERE condition

First thing is you should use parameterized queries to avoid such errors. Use parameterized query and convert text from datetimepicker to datetime and set the parameter value using it. — Chetan, May 15 '18 at 00:26
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, May 15 '18 at 01:19

score 1 · Accepted Answer · answered May 15 '18 at 03:46

As Chetan Ranpariya said, you can set up a parameter with MySqlDbType.DateTime data type to pass date value into the query like this:

query = "SELECT * FROM tbl_sales WHERE last_updated_on >= @last_updated";

// conLocal is a MySqlConnection
using (var cmdLocal = new MySqlCommand(query, conLocal))
{
    cmdLocal.Parameters.Add("@last_updated", MySqlDbType.DateTime).Value = DateTime.Parse(dateTimePicker1.Text);

    // execute query & bind to DataGridView here
}

If passed string value from date picker has pre-specified formatting (e.g. yyyy-MM-dd HH:mm:ss ), use DateTime.ParseExact when assigning parameter value:

cmdLocal.Parameters.Add("@last_updated", MySqlDbType.DateTime).Value = DateTime.ParseExact(dateTimePicker1.Text, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);

I think this is slightly for inserting code, but mine is selecting query — S.Sakthybaalan, May 15 '18 at 04:38

Jaya · Answer 2 · 2018-05-15T00:31:04.197

0

Probably you should have the date time text value within single quotes. something along the lines of. The parse statement would throw an exception on invalid dates, so handle it accordingly.

var lastUpdatedValue = DateTime.Parse(textBox1.Text);
var query = $"SELECT * FROM tbl_sales WHERE last_updated_on >='{lastUpdatedValue}'"

Last but not least, the recommended approach would be to use parameterized queries instead of strings.

edited May 15 '18 at 00:31

answered May 15 '18 at 00:25

Jaya

3,721
4
32
48

I used the above suggestion it works but not display records. MySQL table data [link](https://flic.kr/p/2658N3y) Without WHERE condition [link](https://flic.kr/p/2658LxE) With WHERE condition [link](https://flic.kr/p/2658Mcf) – S.Sakthybaalan May 15 '18 at 01:15
the reason is you are asking for records `>= last_updated` --> check the date -> you are asking for records updated after May 15th, while you have records upated on or before May 13th. So you either have to change the query to `<=` or give an appropriate date such may 12th. – Jaya May 15 '18 at 16:42

How to display record using WHERE condition

2 Answers2