How to filter database with datetimepicker

Question

0.00/5 (No votes)

See more:

I have a table in database with colomn Date (datetime format) that has format "4/2/2015 1:12:15 PM". I select data by two datetimepickes with such statement:

SqlDataAdapter ErrorListTableAdapter = new SqlDataAdapter("SELECT * FROM  ErrorList WHERE (Date BETWEEN '" + Convert.ToDateTime(FromDateTimePicker.Value.ToString("d/M/yyyy h:mm:ss tt")) + "' AND ' " + Convert.ToDateTime(ToDateTimePicker.Value.ToString("d/M/yyyy h:mm:ss tt")) + "')", conn);

But there is no result. What is wrong here?

Posted 4-Feb-15 23:07pm

Member 11246570

Updated 5-Feb-15 0:49am

v4

Add a Solution

Comments

Thanks7872 5-Feb-15 5:11am

First of all,forget about datetime picker. It has nothing to do with your problem.

Further, its very simple to resolve such issue. Make sure you have field datatype as DateTime in your database. Pass only datetime from code to database. Now, your task is only to convert the value of selected date in datepicker to DateTime which can easily be find using Google.com

Member 11246570 5-Feb-15 6:49am

Rohan Leuva, yes you are right, my data type was not datetime. I changed it, but again I got only empty table without any error? Can you show me the mistake?

Thanks7872 5-Feb-15 6:51am

4/2/2015 is 4th feb or 2nd apr?

Member 11246570 5-Feb-15 6:55am

4th of February

Thanks7872 5-Feb-15 7:05am

Try to convert like this

DateTime From_Time= DateTime.ParseExact("4/2/2015 1:12:15 PM", "d/M/yyyy h:m:s tt", CultureInfo.InvariantCulture);

Now, pass this in query. Note:Always use parameterized queries to be safe from sql injection.

Nathan Minier 5-Feb-15 7:05am

Just so that you know, SQL Servers don't understand the DateTime structure and it is not automatically converted for you.

Use SqlDateTime:
https://msdn.microsoft.com/en-us/library/System.Data.SqlTypes.SqlDateTime(v=vs.110).aspx

CHill60 5-Feb-15 7:15am

If you use parameterised queries it should be fine

Nathan Minier 5-Feb-15 7:29am

Cool. I recall it being an issue, though, when I worked with SqlConnection/SqlCommand paradigms.

1 solution

Add a Solution

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

CHill60 · Accepted Answer · 2015-02-05T01:30:00

As Rohan Leuva said - use parameterized queries. Not just to be safe from SQL injection, but because they give easy solutions to problems like this.

For example:

SQL

SqlDataAdapter ErrorListTableAdapter = new SqlDataAdapter();
ErrorListTableAdapter.SelectCommand = 
   new SqlCommand( "SELECT * FROM  ErrorList WHERE (Date BETWEEN @Date1 AND @Date2)", 
   conn);
ErrorListTableAdapter.SelectCommand.Parameters.Add("@Date1", SqlDbType.Date).Value 
    = FromDateTimePicker.Value.Date;
ErrorListTableAdapter.SelectCommand.Parameters.Add("@Date2", SqlDbType.Date).Value 
    = ToDateTimePicker.Value.Date;

How to filter database with datetimepicker

1 solution

Solution 1

Add your solution here

Preview 0

How to filter database with datetimepicker

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us