dates

I am building a where condition on an asp page. Here is the code.


if IsDate(request.Form("dteStart")) then
dteStart=FormatDateTime(request.Form("dteStart"),1)
strWhere=strWhere & " PODate >= #" & dteStart & "# AND "
Else
dteStart=#2001-11-01#
end if

if IsDate(request.Form("dteEnd")) then
dteEnd=FormatDateTime(request.Form("dteEnd"),1)
strWhere=strWhere & " PODate <= #" & dteEnd & "# AND "
Else
dteEnd=#2010-11-30#
end if

Lower=0
if (request.Form("lngLower")<> "") then
Lower = Cdbl(request.Form("lngLower"))
strWhere=strWhere & " POCost >= " & Lower & " AND "
end if

Upper=0
if (request.Form("lngUpper")<> "") then
Upper = Cdbl(request.Form("lngUpper"))
strWhere=strWhere & " POCost <= " & Upper & " AND "
end if

if right(strWhere,5)=" AND " then
strWhere=Left(strWhere,Len(strWhere)-5)
end if
if strWhere="WHERE" then strWhere = ""
%>


If I search with blank dates, I get a recordset back from the database.
However, if I enter a date on the search page, this page doesn't load, I
just get "this page can't be displayed".

Anyone offer an idea why the page just doesn't open?

Ray Keattch.

--
MrBitsy

Re: dates

MrBitsy wrote:
> I am building a where condition on an asp page. Here is the code.
>

>
> If I search with blank dates, I get a recordset back from the
> database. However, if I enter a date on the search page, this page
> doesn't load, I just get "this page can't be displayed".
>
> Anyone offer an idea why the page just doesn't open?
>
First you need to determine the actual error:
http://www.aspfaq.com/show.asp?id=2109

Secondly, you need to stop trying to use dynamic sql and use parameters
instead. Given the use of # to delimit your dates, I guess you are using
Access (please don't make us guess). I strongly suggest you switch to using
saved parameter queries. See:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

If you'd rather not use saved parameter queries, at least give this
technique a try:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Lastly, if you decide to continue using dynamic sql (leaving your site open
to hackers using sql injection), then you need to pass dates in the proper
format (stop using FormatDateTime):
http://www.aspfaq.com/show.asp?id=2040
http://www.aspfaq.com/show.asp?id=2023

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: dates

"Bob Barrows [MVP]" wrote in
news:ODauRPHiFHA.2484@TK2MSFTNGP15.phx.gbl:

> MrBitsy wrote:
>> I am building a where condition on an asp page. Here is the code.
>>
>
>>

oops, Ok!

>> If I search with blank dates, I get a recordset back from the
>> database. However, if I enter a date on the search page, this page
>> doesn't load, I just get "this page can't be displayed".
>>
>> Anyone offer an idea why the page just doesn't open?
>>
> First you need to determine the actual error:
> http://www.aspfaq.com/show.asp?id=2109
>
> Secondly, you need to stop trying to use dynamic sql and use
> parameters instead. Given the use of # to delimit your dates, I guess
> you are using Access (please don't make us guess).

Yes, it is Access - why would I make anyone guess?

> I strongly suggest
> you switch to using saved parameter queries. See:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%24
> 6S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

Thanks very much for the link, I will have a read. I built where
conditions on Access forms so used that on the web page.

> If you'd rather not use saved parameter queries, at least give this
> technique a try:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/
> msg/72e36562fee7804e
>
> Lastly, if you decide to continue using dynamic sql (leaving your site
> open to hackers using sql injection), then you need to pass dates in
> the proper format (stop using FormatDateTime):
> http://www.aspfaq.com/show.asp?id=2040
> http://www.aspfaq.com/show.asp?id=2023
>
> Bob Barrows

Thanks very much bob. All links very much appreciated and I will
certainly read up on parameters.

Ray Keattch.



--
MrBitsy

Re: dates

MrBitsy wrote:
> "Bob Barrows [MVP]" wrote in
> Given the use of # to delimit your dates, I guess
>> you are using Access (please don't make us guess).
>
> Yes, it is Access - why would I make anyone guess?

:-)
I don't know. Why did you make me guess? I don't see any mention of Access
in your original post. Did I miss something?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: dates

"Bob Barrows [MVP]" wrote in
news:#aoHEQIiFHA.3656@TK2MSFTNGP09.phx.gbl:

> MrBitsy wrote:
>> "Bob Barrows [MVP]" wrote in
>> Given the use of # to delimit your dates, I guess
>>> you are using Access (please don't make us guess).
>>
>> Yes, it is Access - why would I make anyone guess?
>
>:-)
> I don't know. Why did you make me guess? I don't see any mention of
> Access in your original post. Did I miss something?
>
> Bob Barrows

Sorry Bob, just after I replied I realised what you meant :-)

Anyway, thanks very much again concerning the dates. My search page is now
working fine after the excellent links you posted (dynamic SQL though). I
had to get that page up quickly, but I will read up on the other links you
suggested concerning parameters.

I thought I had been really clever building that where condition, but just
shows what you don't know :-)

thanks again.

--
MrBitsy