CODEWITHSUNDEEP
c#mysql
AspirantForProgramming
AspirantForProgramming

Reputation: 1

How to search between two dates?

I'm really having a bad time when my code doesn't work. Can anybody help me on how to search between two dates using datetimepicker? I have a source code that retrieve the data from database but when I add the "between" in where clause, the data that I want to search, it won't display in datagridview. Also, I tried already putting "MM/dd/yyyy" in the tostring().

Code to retrieve data:

    public void showData()
    {
        string constring = "datasource = localhost;port = 3307; username = root; password =root; database = dbpetsales";
        MySqlConnection conDataBase = new MySqlConnection(constring);
        MySqlCommand cmdDataBase = new MySqlCommand("SELECT transaction_ID as 'Transaction ID',  ProdName as 'Product Name',price as 'Price',subtotal as 'Subtotal', Date FROM dbpetsales.pos", conDataBase);
        try
        {

            MySqlDataAdapter sda = new MySqlDataAdapter();
            sda.SelectCommand = cmdDataBase;
            dbdataset = new DataTable();
            sda.Fill(dbdataset);
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dbdataset;
            dataGridView1.DataSource = bSource;
            sda.Update(dbdataset);

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

Code to retrieve data in searching between two dates:

    public void showData()
    {
        string constring = "datasource = localhost;port = 3307; username = root; password =root; database = dbpetsales";
        MySqlConnection conDataBase = new MySqlConnection(constring);
        MySqlCommand cmdDataBase = new MySqlCommand("SELECT transaction_ID as 'Transaction ID',  ProdName as 'Product Name',price as 'Price',subtotal as 'Subtotal', Date FROM dbpetsales.pos where Date between '"+this.dateTimePicker1.Value.ToString()+"' and '"+this.dateTimePicker2.Value.ToString()+"' ", conDataBase);
        try
        {

            MySqlDataAdapter sda = new MySqlDataAdapter();
            sda.SelectCommand = cmdDataBase;
            dbdataset = new DataTable();
            sda.Fill(dbdataset);
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dbdataset;
            dataGridView1.DataSource = bSource;
            sda.Update(dbdataset);

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

Upvotes: 0

Views: 197

Answers (1)

Ali Bahrami
Ali Bahrami

Reputation: 6073

First, please use parameters instead of string concat in your query (to prevent sql-injection and send date as dates, no string ), just do like below:

MySqlConnection conDataBase = new MySqlConnection(constring);
MySqlCommand cmdDataBase = new MySqlCommand("SELECT transaction_ID as 'Transaction ID',  ProdName as 'Product Name',price as 'Price',subtotal as 'Subtotal', Date FROM dbpetsales.pos where Date >= @date1 and Date <= @date2, conDataBase);
cmdDataBase.Parameters.AddWithValue("@date1", dateTimePicker1.Value);
cmdDataBase.Parameters.AddWithValue("@date1", dateTimePicker2.Value);

Upvotes: 4

Related Questions