Date format for ASP and SQL String Doesn't Produce Results

[Billy]

This string is supposed to provide all records from an MDB database
that match the courier and date specified in the query. I
Response.Write the query and I get a date as 1/27/2007. The date
format style is exactly the field specification as I see in the MDB
Date field in the Courier table. The data for this query exists - both
the courier and date that I'm selecting.

However, when the file continues processing and the results are
displayed - no data!

What am I doing wrong here? Why would an MDB not understand a date
format of mm/dd/yyyy if that is what I'm explicitly seeing in the
field of the table? There are no constraints on the field design.

'<snip of the code>
cfedexSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'Fedex'
AND Courier.Date = " & strDate & ""

 

[Bob Barrows [MVP]]

This string is supposed to provide all records from an MDB database
that match the courier and date specified in the query. I
Response.Write the query and I get a date as 1/27/2007. The date
format style is exactly the field specification as I see in the MDB
Date field in the Courier table. The data for this query exists - both
the courier and date that I'm selecting.

However, when the file continues processing and the results are
displayed - no data!

What am I doing wrong here? Why would an MDB not understand a date
format of mm/dd/yyyy if that is what I'm explicitly seeing in the
field of the table? There are no constraints on the field design.

'<snip of the code>
cfedexSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'Fedex'
AND Courier.Date = " & strDate & ""

Date literals need to be delimited with octothorps (#). When you
response.write your sql statement, the date needs to be surrounded with #s

You have other problems:
Using selstar: http://www.aspfaq.com/show.asp?id=2096
Using an ambiguous date format:
http://classicasp.aspfaq.com/date-t...ion/could-i-get-a-little-help-with-dates.html
Using a reserved keyword for a field name (Date):
http://www.aspfaq.com/show.asp?id=2080

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Billy]

Date literals need to be delimited with octothorps (#). When you
response.write your sql statement, the date needs to be surrounded with #s

You have other problems:
Using selstar:http://www.aspfaq.com/show.asp?id=2096
Using an ambiguous date format:http://classicasp.aspfaq.com/date-time-routines-manipulation/could-i-...
Using a reserved keyword for a field name (Date):http://www.aspfaq.com/show.asp?id=2080

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:http://mvp.unixwiz.net/techtips/sql....sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:http://groups-beta.google.com/group/microsoft.public.inetserver.asp.d...

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvO...

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYx...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"- Hide quoted text -

- Show quoted text -

Thanks, the # signs worked.