How to put datetimepicker control in query in C#

Question

Hi all,

Iam creating one report in c# where having one combobox which is contain Daily,Weekly

Monthly,HalfYearly,Yearly items if user select in combobox any of them item then in datagridview show respective data as per selection in combobox, as of now its working fine for me but my project manager want dateTime picker option in front end , so if user selected combobox item as weekly and startdate(from datepicker):11/03/2016 and enddate(fromdatetimepicker) as 15/03/2016 so he can get that releted data into datagridview im using access database for this and having only one column Date from using that column only i have to show details i was tried my end but not happening iam putting a code here just tell me how i use datetimepicker option in that

if (comboBox1.SelectedItem.ToString() == "Weekly")

{

string connetionString = null;

connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

cnn.ConnectionString = connetionString;

string sql = "SELECT * FROM Billing where Date Between Date() and Date()-6";

OleDbConnection connection = new OleDbConnection(connetionString);

OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);

DataSet ds = new DataSet();

connection.Open();

dataadapter.Fill(ds, "Billing");

connection.Close();

dataGridView1.DataSource = ds;

dataGridView1.DataMember = "Billing";

}

else if (comboBox1.SelectedItem.ToString() == "Monthly")

{

string connetionString = null;

connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

cnn.ConnectionString = connetionString;

string sql = "SELECT * FROM Billing where Year([Date]) = Year(Now()) And Month([Date]) = Month(Now())";

OleDbConnection connection = new OleDbConnection(connetionString);

OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);

DataSet ds = new DataSet();

connection.Open();

dataadapter.Fill(ds, "Billing");

connection.Close();

dataGridView1.DataSource = ds;

dataGridView1.DataMember = "Billing";

}

else if (comboBox1.SelectedItem.ToString() == "HalfYearly")

{

string connetionString = null;

connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

cnn.ConnectionString = connetionString;

string sql = "SELECT * FROM Billing where Year([Date]) = Year(Now()) And DatePart('h', [Date]) = DatePart('h', Now())";

OleDbConnection connection = new OleDbConnection(connetionString);

OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);

DataSet ds = new DataSet();

connection.Open();

dataadapter.Fill(ds, "Billing");

connection.Close();

dataGridView1.DataSource = ds;

dataGridView1.DataMember = "Billing";

}

else if (comboBox1.SelectedItem.ToString() == "Yearly")

{

string connetionString = null;

connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

cnn.ConnectionString = connetionString;

string sql = "SELECT * FROM Billing where Year([Date]) = Year(Date())";

OleDbConnection connection = new OleDbConnection(connetionString);

OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);

DataSet ds = new DataSet();

connection.Open();

dataadapter.Fill(ds, "Billing");

connection.Close();

dataGridView1.DataSource = ds;

dataGridView1.DataMember = "Billing";

}

Actually i want only how i will add Start_date(datetimepicker1) and end_date(datetimePicker2) in below query

SELECT * FROM Billing where Date Between Date() and Date()-6 --Weekly

SELECT * FROM Billing where Year([Date]) = Year(Now()) And Month([Date]) = Month(Now()) --monthly

SELECT * FROM Billing where Year([Date]) = Year(Now()) And DatePart('h', [Date]) = DatePart('h', Now()) --"HalfYearly"

SELECT * FROM Billing where Year([Date]) = Year(Date())-- "Yearly"

Answer 1

Hi Dear

Try to alter the query... use

Date>=@startDate and Date <=@enddate

// now pass the parameter as u r doing..

Hope it helps

Answer 2

sir i implemented your code like this below

 

string connetionString = null;

connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

cnn.ConnectionString = connetionString;

cmd = new OleDbCommand("SELECT * FROM Billing " + "WHERE Date between ? AND ?");

cmd.Parameters.AddWithValue("?", dateTimePicker1.Value);

cmd.Parameters.AddWithValue("?", dateTimePicker2.Value);

OleDbConnection connection = new OleDbConnection(connetionString);

OleDbDataAdapter dataadapter=new OleDbDataAdapter ();

DataSet ds = new DataSet();

connection.Open();

dataadapter.SelectCommand = cmd;

connection.Close();

dataGridView1.DataSource = ds;

 

but now what happen if i select start_date suppose 1/02/2016 and end_date is 22/03/2016 and click show Report nothing is coming into datagridview but there is a one entry of 17/02/2016 date for refernce i given two image

 

 

 

 

Answer 3

you welcome Atul well helpful for you mark as correct answer

Answer 4

you welcome Atul well helpful for you mark as correct answer

Answer 5

thank you krishna sir for reply i will check whatever links you given , thank you once again :)

Answer 6

thank you sir for reply but m not understnd how i put datetimepicker in query all query which is given

Answer 7

Hello Atul Rokade thanks foe writing below links i hope helpful for you...:)

 

http://stackoverflow.com/questions/541225/how-can-i-set-a-datetimepicker-control-to-a-specific-date

 

http://stackoverflow.com/questions/19934793/set-datetimepicker-control-to-format-yyyymmdd

 

https://msdn.microsoft.com/en-us/library/ms229631(v=vs.110).aspx 

Answer 8

Am not sure what issue you are facing... try this...

cmd.CommandText =

"SELECT * FROM Billing " +

"WHERE Date Between ? And ?";

cmd.Parameters.AddWithValue("?", dateControl1.value/text);

cmd.Parameters.AddWithValue("?", dateControl2.value/text);

or

OleDbCommand command = new OleDbCommand(

"SELECT * FROM Billing " +

"WHERE Date between @DateStart AND @DateEnd");

command.Parameters.AddWithValue("@DateStart",dateTimePicker1.Value.Date);

command.Parameters.AddWithValue("@DateEnd",dateTimePicker2.Value.Date);