|
jwpenuel -> Date Range Variable Input from Form--SQL Syntax (5/22/2003 21:25:59)
|
The code below is an asp page where I wish to use a form to define a
date range by inputting data for a SQL query from an Excel
spreadsheet within a Front Page 2002 web. I have read the various
posts and am still confused as to the proper syntax for inserting the
date variables into the SQL query. The database is a call schedule
for a group of physicians, and each record has a unique date (Date1).
I want to use the asp page to create reports. Start and End dates are
submitted from this form back to this same page. The database connection and record display seems to function properly. The only difficulty I am having is with the date query.
I began with the following statement, but received a syntax error.
When I substitute WHERE Date1 = Date(), it works and delivers the
record corresponding to today' s date.
' objRec.open " select * from OnCall WHERE Date1 >
" &Request.Form(" DateStart" )&" and Date < " &Request.Form(" DateEnd" )&"
I then added two variables, DateS and DateE and set their value to
that of the corresponding form variables, still with no success.
objRec.open " SELECT * FROM OnCall WHERE [Date1] between (#DateS# AND
#DateE#)"
I have been unable to find specific documentation for the format used
to enter date variables from a form into a SQl query. It appears that
the general format for a variable is:
" &Request.Form(" variable" )&"
I have tried substituting # symbols, and other variations, but have
not hit on the right combination.
Any idea as to what I am doing wrong?
Thanks,
<%
' TestConn1.asp
' Testing database connection for OnCall database
Set conn = server.createobject(" ADODB.Connection" )
Set objRec = server.createobject(" ADODB.Recordset" )
Conn.Open Application(" OnCall_ConnectionString" )
objRec.ActiveConnection = conn
Dim DateStart
Dim DateEnd
Dim Date1, DateS, DateE
%>
<FORM NAME=Form1 ACTION = " TestConn3.asp" METHOD = POST>
Enter Start date for Report: <INPUT TYPE= " TEXT" NAME=#DateStart#
size=" 20" ><br>
Enter End Date for Report: <INPUT TYPE=" TEXT" NAME=#DateEnd#
size=" 20" ><br>
<INPUT TYPE=" SUBMIT" VALUE=" Get Report" >
</FORM>
<TABLE BORDER=1 width=" 671" >
<TR>
<TD width=" 146" ><B>Date</B></TD>
<TD width=" 509" ><B>Weekday</B></TD>
</TR>
<%
DateS=Request.Form(" DateStart" )
DateE=Request.Form(" DateEnd" )
objRec.open " SELECT * FROM OnCall WHERE [Date1] between (#DateS# AND
#DateE#)"
' objRec.open " select * from OnCall WHERE Date >
" &Request.Form(" DateStart" )&" and Date < " &Request.Form(" DateEnd" )&"
do while not objRec.EOF
Response.Write (" <TR>" )
Response.Write " <TD>"
Response.write objRec(" Date1" )
Response.Write " </TD>"
Response.Write " <TD>"
Response.Write objRec(" Weekday" )
Response.Write " </TD>"
Response.Write " </TR>"
ObjRec.MoveNext
loop
%>
|
|
|
|
