Reputation: 1
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
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