Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 22-Apr-13 11:48am
Edited 22-Apr-13 12:23pm
v2
Comments
ryanb31 at 22-Apr-13 16:58pm
   
Silly question, did you mean to use datePicker1 both times in your BETWEEN clause?
Member 9611735 at 22-Apr-13 17:12pm
   
No, sorry I meant datepicker 1 and datepicker2
ryanb31 at 23-Apr-13 6:56am
   
So, what exactly is the issue?
Member 9611735 at 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?
ryanb31 at 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.
Member 9611735 at 24-Apr-13 23:16pm
   
it looks as if it isn't querying correctly due to the differing formats. Do you know how I can change datepicker format 13/04/2013 12:00:00 AM to 2013-04-13?
ryanb31 at 25-Apr-13 7:10am
   
Is the date in your database an actual DateTime field or a nvarchar? If it is a datetime it does not matter what "format" you send, it's a date.
Member 9611735 at 25-Apr-13 9:36am
   
It was originally a date field but I changed it to datetime but it still isn't working
ryanb31 at 25-Apr-13 9:42am
   
So, what happens if you run the sql statement in SMS?
Member 9611735 at 25-Apr-13 10:04am
   
I'm not using sms. I have a hosted mysql dbase
ryanb31 at 25-Apr-13 10:07am
   
Sorry, I don't know mysql. But doing a quick google search some people said they had to use > date and < date2 instead of between. Others said MySql does care about the format coming in (which is lame for a date) so you may want to try formatting the dates differently.
AlluvialDeposit at 22-Apr-13 17:03pm
   
Watch out for sql injections!
Member 9611735 at 22-Apr-13 17:22pm
   
I will take that on board. thank you!
AlluvialDeposit at 22-Apr-13 17:33pm
   
Parameters are easy to use :)
AlluvialDeposit at 22-Apr-13 17:34pm
   
And gives you cleaner code..

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
 
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?
  Permalink  
Comments
Member 9611735 at 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)

  Print Answers RSS
0 OriginalGriff 370
1 Sergey Alexandrovich Kryukov 329
2 BillWoodruff 235
3 Afzaal Ahmad Zeeshan 209
4 CPallini 195
0 OriginalGriff 5,560
1 DamithSL 4,476
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 22 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100