Date query in ASP

[Horhayson]

ASP/Scripting newbie question here. I have an access database that I am
writing ASP pages to query and return the results. The end user can put
in text, a beginning date, and an ending date. The script goes and
returns the results based upon the information. I have the text portion
OK, just trying to get the date thing going. Here is what I have so far
(cosmetically it isn't much)

*form method="post" action="time.asp"*
Enter the beginning date for the query in the form mm/dd/yyyy:*br*
*input type="text" name="begdate" size="10"**br**br*
Enter the ending date for the query in the form mm/dd/yyyy:*br*
*input type="text" name="enddate" size="10"*
*input type=submit value="Submit Request"*

time.asp file
<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" &
"DBQ=\\fileserver\network\timeclock.mdb"

objConn.Open
Dim strSQL

Dim rEmployee, rBegDate, rEndDate
Set rEmployee = Request("Employee")
Set rBegDate = Date Value(Request("begdate"))
Set rEndDate = Date Value(Request("enddate"))

strSQL="SELECT * FROM Timeclock WHERE UserName = '" & rEmployee & "'
AND Date BETWEEN '" & rBegDate & "' AND '" & rEndDate & "'"

Dim objRS
Set objRS=Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn

Do While Not objRS.EOF

Response.Write "<b>" & objRS("UserName") & "</b> &nbsp &nbsp" &
objRS("Event") & "&nbsp &nbsp" & objRS("Date") & "&nbsp &nbsp" &
objRS("Time") & "<br>"

objRS.MoveNext
Loop
objRS.Close
Set objRS=Nothing
objConn.Close
Set objConn=Nothing
%>

I get the following:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement

on time.asp where I set rBegDate and rEndDate to the variables inputed
from the form.

Any help is appreciated.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Nicole Calinoiu]

VBScript function names don't contain spaces. Try using "DateValue" rather
than "Date Value" (i.e.: no space between the "Date" and the "Value") for
your date conversion.

HTH,
Nicole


"Horhayson" <horhayson@nospamplease.net> wrote in message
news:eAEOMJgqDHA.1680@TK2MSFTNGP10.phx.gbl...

>
>
> ASP/Scripting newbie question here. I have an access database that I am
> writing ASP pages to query and return the results. The end user can put
> in text, a beginning date, and an ending date. The script goes and
> returns the results based upon the information. I have the text portion
> OK, just trying to get the date thing going. Here is what I have so far
> (cosmetically it isn't much)
>
> *form method="post" action="time.asp"*
> Enter the beginning date for the query in the form mm/dd/yyyy:*br*
> *input type="text" name="begdate" size="10"**br**br*
> Enter the ending date for the query in the form mm/dd/yyyy:*br*
> *input type="text" name="enddate" size="10"*
> *input type=submit value="Submit Request"*
>
> time.asp file
> <%
> Dim objConn
> Set objConn=Server.CreateObject("ADODB.Connection")
> objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" &
> "DBQ=\\fileserver\network\timeclock.mdb"
>
> objConn.Open
> Dim strSQL
>
> Dim rEmployee, rBegDate, rEndDate
> Set rEmployee = Request("Employee")
> Set rBegDate = Date Value(Request("begdate"))
> Set rEndDate = Date Value(Request("enddate"))
>
> strSQL="SELECT * FROM Timeclock WHERE UserName = '" & rEmployee & "'
> AND Date BETWEEN '" & rBegDate & "' AND '" & rEndDate & "'"
>
> Dim objRS
> Set objRS=Server.CreateObject("ADODB.Recordset")
> objRS.Open strSQL, objConn
>
> Do While Not objRS.EOF
>
> Response.Write "<b>" & objRS("UserName") & "</b> &nbsp &nbsp" &
> objRS("Event") & "&nbsp &nbsp" & objRS("Date") & "&nbsp &nbsp" &
> objRS("Time") & "<br>"
>
> objRS.MoveNext
> Loop
> objRS.Close
> Set objRS=Nothing
> objConn.Close
> Set objConn=Nothing
> %>
>
> I get the following:
> Microsoft VBScript compilation (0x800A0401)
> Expected end of statement
>
> on time.asp where I set rBegDate and rEndDate to the variables inputed
> from the form.
>
> Any help is appreciated.
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Ray at]

Change
Date Value
To
DateValue


Aside from that, let me suggest a few things so that as a newbie, maybe you
can consider it now before you develop habits.

1. Instead of using that connection string you're using, use an OLE DB
equivalent connection string. [url]www.connectionstrings.com[/url]. See the OLEDB
strings for Access.

2. I'm going to assume that this page is running on a domain controller.
If so, if at all possible, don't use IIS on a DC.

3. When pulling values from the request, specify the collection you mean to
pull from. Like, instead of Request("begdate"), use
Request.Form("begdate"). If you don't specify, ASP will loop through the
five different collections and pick the first match.
[url]http://www.sloppycode.net/asp/?o=4[/url] If you have a querystring name with the
same name as a form element, you'll get the querystring value.

4. Don't pre-create an ADODB.Recordset object. See here.
[url]http://www.aspfaq.com/2191[/url]

5. Instead of using SELECT *, use SELECT
[UserName],[Event],[Date],[Time]... [url]http://www.aspfaq.com/2096[/url]

6. Beware of reserved SQL words. [url]http://www.aspfaq.com/2080[/url]

7. Isn't it &nbsp;, not &nbsp? :]

8. And for the perfectionist, instead of rs("name"), use
rs.Fields.Item(0).Value (where 0 is the first column selected, 1 is the
second, and so on).


Ray at work




"Horhayson" <horhayson@nospamplease.net> wrote in message
news:eAEOMJgqDHA.1680@TK2MSFTNGP10.phx.gbl...

>
>
> ASP/Scripting newbie question here. I have an access database that I am
> writing ASP pages to query and return the results. The end user can put
> in text, a beginning date, and an ending date. The script goes and
> returns the results based upon the information. I have the text portion
> OK, just trying to get the date thing going. Here is what I have so far
> (cosmetically it isn't much)
>
> *form method="post" action="time.asp"*
> Enter the beginning date for the query in the form mm/dd/yyyy:*br*
> *input type="text" name="begdate" size="10"**br**br*
> Enter the ending date for the query in the form mm/dd/yyyy:*br*
> *input type="text" name="enddate" size="10"*
> *input type=submit value="Submit Request"*
>
> time.asp file
> <%
> Dim objConn
> Set objConn=Server.CreateObject("ADODB.Connection")
> objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" &
> "DBQ=\\fileserver\network\timeclock.mdb"
>
> objConn.Open
> Dim strSQL
>
> Dim rEmployee, rBegDate, rEndDate
> Set rEmployee = Request("Employee")
> Set rBegDate = Date Value(Request("begdate"))
> Set rEndDate = Date Value(Request("enddate"))
>
> strSQL="SELECT * FROM Timeclock WHERE UserName = '" & rEmployee & "'
> AND Date BETWEEN '" & rBegDate & "' AND '" & rEndDate & "'"
>
> Dim objRS
> Set objRS=Server.CreateObject("ADODB.Recordset")
> objRS.Open strSQL, objConn
>
> Do While Not objRS.EOF
>
> Response.Write "<b>" & objRS("UserName") & "</b> &nbsp &nbsp" &
> objRS("Event") & "&nbsp &nbsp" & objRS("Date") & "&nbsp &nbsp" &
> objRS("Time") & "<br>"
>
> objRS.MoveNext
> Loop
> objRS.Close
> Set objRS=Nothing
> objConn.Close
> Set objConn=Nothing
> %>
>
> I get the following:
> Microsoft VBScript compilation (0x800A0401)
> Expected end of statement
>
> on time.asp where I set rBegDate and rEndDate to the variables inputed
> from the form.
>
> Any help is appreciated.
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!