Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Please help me, I have been stuck on this all day!!

I am wanting to filter results of wpf datagrid using two datepickers. My query i have so far is as follows


MySqlConnection cs = new MySqlConnection(connectionSQL);
cs.Open();

DataSet ds = new DataSet();

MySqlDataAdapter da = new MySqlDataAdapter("Select * from Transactions WHERE date BETWEEN '"+datePicker1.Text +"' AND '"+datePicker2.Text +"' ", cs);

MySqlCommandBuilder cmd = new MySqlCommandBuilder(da);

da.Fill(ds);

this.dataGrid1.ItemsSource = ds.Tables[0].DefaultView;

Does datepicker require any binding in xaml? DBase date field has type of date.
Posted
Updated 22-Apr-13 11:23am
v2
Comments
ZurdoDev 22-Apr-13 16:58pm    
Silly question, did you mean to use datePicker1 both times in your BETWEEN clause?
Member 9611735 22-Apr-13 17:12pm    
No, sorry I meant datepicker 1 and datepicker2
ZurdoDev 23-Apr-13 6:56am    
So, what exactly is the issue?
Member 9611735 23-Apr-13 23:48pm    
I am trying to filter my datagrid using datepickers but it's not matching with any dates stored in dbase. Am I quering my dbase correctly? Should I be using datepicker1.text or something else?
ZurdoDev 24-Apr-13 6:57am    
The easy thing to do is to put a breakpoint and see what the actual SQL text is. Then what happens if you run that SQL in SQL Management Studio.

1 solution

Have you tried setting the date as a formatted date?

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function%5Fstr-to-date[^]

C#
MySqlDataAdapter da = new MySqlDataAdapter("Select * from Transactions WHERE date BETWEEN STR_TO_DATE('"+datePicker1.Text +"','%d,%m,%Y') AND STR_TO_DATE('"+datePicker2.Text +"','%d,%m,%Y') ", cs);


This is what you would probably do in normal SQL.

I also noticed that both between dates are from DatePicker1.Text... was that intentional?
 
Share this answer
 
Comments
Member 9611735 22-Apr-13 17:21pm    
Thank you for replying. Unfortunately it doesn't work. Dates in my database are saved in format 2013-04-11. Would i need to change the format of date field? My datepickers are named datepicker1 and datepicker2.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900