Click here to Skip to main content
13,044,428 members (131,450 online)
Rate this:
Please Sign up or sign in to vote.
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);

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);


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 10:48am
Updated 22-Apr-13 11:23am
ryanb31 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
ryanb31 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?
ryanb31 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 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 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 25-Apr-13 9:36am
It was originally a date field but I changed it to datetime but it still isn't working
ryanb31 25-Apr-13 9:42am
So, what happens if you run the sql statement in SMS?
Member 9611735 25-Apr-13 10:04am
I'm not using sms. I have a hosted mysql dbase
ryanb31 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 22-Apr-13 17:03pm
Watch out for sql injections!
Member 9611735 22-Apr-13 17:22pm
I will take that on board. thank you!
AlluvialDeposit 22-Apr-13 17:33pm
Parameters are easy to use :)
AlluvialDeposit 22-Apr-13 17:34pm
And gives you cleaner code..

1 solution

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

Solution 1

Have you tried setting the date as a formatted 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?
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 22 Apr 2013
Copyright © CodeProject, 1999-2017
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