CODEWITHSUNDEEP
c#sqlvisual-studio-2010ms-access-2007
Peter Roche
Peter Roche

Reputation: 345

Problems when executing SQL Where statement with a date

When I hardcode the values and execute the SQL statement within Access 2010, the command is executed and the values required are returned.

SELECT [Event ID], [Crowd Size], [Event Name] FROM Event WHERE ([Event Date]=#8/11/2012#) AND ([Event Name]="Peter")

However when I use text boxes in place of the hardcoded values

("SELECT [Event ID], [Crowd Size], [Event Name] FROM [Event] WHERE ([Event Name]='" + textBoxEventName.Text + "') AND ([Event Date]=#" + textBoxEventDate.Text + "#)");

When trying to read from the datareader, it is unable to do so as there doesn't seem to be any values in there.

I have gone through the code in debug mode and the values in the text boxes are correct, but still doesn't happen.

Upvotes: 3

Views: 262

Answers (3)

Peter Roche
Peter Roche

Reputation: 345

After many hours of fiddling around with this problem,

The solution is that Access SQL likes dates in the format

#YYYY/MM/DD#

Upvotes: 0

Armaan
Armaan

Reputation: 190

Try this: see this page: http://www.tizag.com/sqlTutorial/sqlselect.php

Upvotes: 0

NicoTek
NicoTek

Reputation: 1167

Access is a bit tricky with the SQL Queries that it runs...

Make sure that the date you are grabbing is in the correct format, or assign it a format your-self.

http://msdn.microsoft.com/en-us/library/az4se3k1.aspx (Standard Date and Time Format Strings)

also make sure that there are all the quotation marks necessary and that you are not using any of the "reserved words"

http://support.microsoft.com/kb/286335 (List of reserved words in Access)

Upvotes: 1

Related Questions