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

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.

'
cfedexSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'Fedex'
AND Courier.Date = " & strDate & ""

Re: Date format for ASP and SQL String Doesn"t Produce Results

Billy wrote:
> 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.
>
> '
> 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-time-routines-manipulation /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.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl



--
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"

Re: Date format for ASP and SQL String Doesn"t Produce Results

On Jan 29, 9:12 pm, "Bob Barrows [MVP]"
wrote:
> Billy wrote:
> > 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.
>
> > '
> > 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-time-routines-manip ulation/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-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.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&sel m=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.