how to search data between specified dates

Question

I have a query

OleDbCommand com = _
    new OleDbCommand("select * from techs where actd0v between '" + _
                     TextBox1.Text + "' and '" + TextBox2.Text +  "'" , con);

where textbox 1 and 2 are the specified dates in which I want to retrieve some date related to those dates. But when I'm trying to find the data between the given dates it gives me some dates which are not included in between them. Is there any pattern I need to specify to get all the dates which occur in between the specified dates.

I'm using ms access database for my project.

Answer 1

I'm guessing that the text in the textboxes are not in a recognized format for OleDB dates. Try using the format, "yyyy-MM-dd". Example: 2010-03-31. Can you run the app in debug mode and post the full query?

Answer 2

The formatting of your query looks correct. But:

Beware of SQL injection here! You need to make sure that the input coming is indeed a date and not something like:

'; drop table users; --

One thing you could do is:

DateTime.Parse(TextBox1.Text)

and use one of the .To* methods that Access will find an agreeable format. Or create an ODBC canonical format from the DateTime object which usually works:

ODBC Canonical: yyyy-mm-dd hh:mm:ss.sss

Here's a nice overview of Sql Injection you should check out. It's not Access-specific but it does communicate the concept nicely.

Answer 3

SQL Injections warnings aside... I'll presume you trust your users.

Put number signs (#) on either side of the date

OleDbCommand com = _
    new OleDbCommand("select * from techs where actd0v between #" + _
                    TextBox1.Text + "# and #" + TextBox2.Text + "#" , con);

MS Access should figure it out.

It may be that the "between" was being done on a the string representation of the date rather than as a date type; which would explain the out of bound results.