CODEWITHSUNDEEP
c#ms-access
AndyDB
AndyDB

Reputation: 431

C# and MS Access queries with date range issue

I am writing a query to transfer data between two MS Access tables (in different databases). The transfer side of things is fine. However, I'm getting real problems with the to/from date selection (which is set on the fly).

My PC is setup for the UK, but I believe the MS-SQL query converts the query to US. As you can imagine, certain dates, such as 1/11/2013 or 11/1/2013 - produce incorrect results (for me).

I have been trying different things with strange results, so could do with a little guidance as to the way it should be done. Here is an example of the current query (along with the SELECT part created during the run). As mentioned, I have used other formats (not just .ToLongDateString):

cmd.CommandText = @"INSERT INTO [Report data] IN '" + Directory.GetCurrentDirectory() + "\\Reporting.mdb" + "' SELECT tblMain.* FROM tblMain WHERE [Start Date-Time] Between #" + monthCalendar1.SelectionStart.ToLongDateString() + " 00:00:00# AND #" + monthCalendar2.SelectionStart.ToLongDateString() + " 23:59:59#;";

"SELECT tblMain.* FROM tblMain WHERE [Start Date-Time] Between #01 November 2013 00:00:00# AND #01 November 2013 23:59:59#;"

Any help would be greatly appreciated - thank you.

Upvotes: 1

Views: 1566

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

Not only will using parameterized queries protect your code from SQL Injection vulnerabilities, it will also remove the burden of having to fuss around with delimiting values and worrying about locale issues (i.e., "'US' vs. 'UK' queries"). Do yourself a favour and convert your code to something that looks more like this:

cmd.CommandText =
    "SELECT MemberID, DonationDate FROM MemberDonations " +
    "WHERE DonationDate Between ? And ?";
cmd.Parameters.AddWithValue("?", new DateTime(2013, 1, 2));
cmd.Parameters.AddWithValue("?", new DateTime(2013, 1, 3));

Upvotes: 2

Related Questions