|
| |
|
|
jwpenuel
Posts: 22
From: Fort Myers FL USA
Status: offline
 |
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
%>
_____________________________
James Penuel
jpenuel@digestivehealth.com
|
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Date Range Variable Input from Form--SQL Syntax - 5/22/2003 22:18:55
quote:
' objRec.open " select * from OnCall WHERE Date1 >
" &Request.Form(" DateStart" )&" and Date < " &Request.Form(" DateEnd" )&"
The above probably didn' t work because your db field is titled " Date1" , but in the second half of your SQL you put Date, not Date1. Also, if you want the date entered in the form field to be included in the criteria you should change your SQL to:
' objRec.open " select * from OnCall WHERE Date1 > = " &Request.Form(" DateStart" )&" and Date <= " &Request.Form(" DateEnd" )&"
| |
|
|
|
jwpenuel
Posts: 22
From: Fort Myers FL USA
Status: offline
|
RE: Date Range Variable Input from Form--SQL Syntax - 5/24/2003 20:38:22
Thanks for your comments and proofreading. Look at the code too many times and one tends to become blind to obvious errors. Have tried several more variations
Tried again using this statement:
objRec.open " select * from OnCall WHERE Date1 BETWEEN " &Request.Form(" DateStart" )&" and " &Request.Form(" DateEnd" )&" "
Again received an error message.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression ' Date1 BETWEEN and' .
Tried this statement:
objRec.open " select * from OnCall WHERE (Date1 >= " &Request.Form(" DateStart" )&" ) and (Date1 <= " &Request.Form(" DateEnd" )&" )"
/On_Call/TestConn3.asp, line 29
Received this error message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Excel Driver] Extra ) in query expression ' (Date1 >= ) and (Date1 <= )' .
/On_Call/TestConn3.asp, line 28
( I counted carefully and can' t see parenthetical mismatch)
Once again....
objRec.open " select * from OnCall WHERE Date1 >= " &Request.Form(" DateStart" )&" and Date1 <= " &Request.Form(" DateEnd" )&" "
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression ' Date1 >= and Date1 <=' .
/On_Call/TestConn3.asp, line 28
What is the syntax for setting default values for the form input variables? I have had this issue when setting up DRW queries in FP.
I added these lines above the code for the form:
DateStart=#5/1/2003#
DateEnd=#5/31/2003#
and with this SQL query
objRec.open " select * from OnCall WHERE Date1 BETWEEN " &Request.Form(" DateStart" )&" and " &Request.Form(" DateEnd" )&" "
I received the following error message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression ' Date1 BETWEEN and' .
/On_Call/TestConn3.asp, line 31
Any more ideas? I feel like I am missing something quite fundamental here.
Thanks,
_____________________________
James Penuel
jpenuel@digestivehealth.com
| |
|
|
|
jwpenuel
Posts: 22
From: Fort Myers FL USA
Status: offline
|
RE: Date Range Variable Input from Form--SQL Syntax - 6/2/2003 0:03:15
I gave up on the asp scripting and went back to the DRW. I entered the following custom query and was able to do what I wanted.
SELECT * FROM " OnCall" WHERE (Date1 Between #::DateStart::# AND #::DateEnd::#)
The DRW has an interesting bug. The DRW will not verify the query as written. You have to get rid of the # signs, finish the DRW and then go back into HTML view and find the line that begins:
<!--webbot bot=" DatabaseRegionStart"
and correct the format of the SQL query (the # signs signify date format.)
(This bug is described in MSKB Article ID: Q296653)
Don' t forget to set default values for DateStart and DateEnd, or you will also get an error message. The DRW automatically sets up text boxes for both DateStart and DateEnd.
ASP is obviously more elegant, but I needed to get the job done.
Thanks for being here!
_____________________________
James Penuel
jpenuel@digestivehealth.com
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 