|
| |
|
|
mar0364
Posts: 3030
Joined: 4/5/2002
From: Florida, US
Status: offline
 |
SQL Date Range Search. - 10/2/2002 9:53:06
I am using SQL database for the first time. I have a field name " date_sent" it is a datetime field. I am trying to creat a page where someone can type in a start date and an end date and see all the items created in that time period.
I' ve been trying for a while with no luck. Hope someone can point me in the correct direction.
SELECT tracker_number, first_name, last_name, company_name, date_sent
FROM Results
WHERE date_sent BETWEEN <%=Request(" date_sent" )%> and <%=Request(" date_sent" )%>
IT is the WHERE statement that gets me.
Thanks!
Rich
_____________________________
Easy Web
“Stay committed to your decisions, but stay flexible in your approach.”
|
|
|
|
daveh42
Posts: 83
From: Maine USA
Status: offline
|
RE: SQL Date Range Search. - 10/2/2002 10:33:13
I think SQL Server uses a single quote as the date delimiter. So your " where clause" should look something like:
WHERE date_sent BETWEEN ' <%=Request(" date_sent" )%>' and ' <%=Request(" date_sent" )%>'
Hope this helps,
Dave
| |
|
|
|
mar0364
Posts: 3030
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: SQL Date Range Search. - 10/2/2002 13:46:53
This seems like it should work but I get the following error:
Microsoft VBScript compilation error ' 800a0409'
Unterminated string constant
/data_fp/tracker1.asp, line 43
fp_sQry=" SELECT tracker_number, first_name, last_name, company_name, date_sent FROM Results WHERE date_sent BETWEEN ' <%=Request(" " date_sent" " )
---------------------------------------------------------------------------------------------------------------------------------------------------^
here is the code from the drw:
fp_sQry=" SELECT tracker_number, first_name, last_name, company_name, date_sent
FROM Results
WHERE date_sent BETWEEN ' <%=Request(" " date_sent" " )%>' AND ' <%=Request(" " date_sent" " )%>' ;"
Thanks!
Rich
| |
|
|
|
daveh42
Posts: 83
From: Maine USA
Status: offline
|
RE: RE: SQL Date Range Search. - 10/2/2002 15:07:21
Maybe something like this would work:
fp_sQry=" SELECT tracker_number, first_name, last_name, company_name, date_sent FROM Results WHERE date_sent BETWEEN ' " & <%=Request(" date_sent" )%> & " ' AND ' " & <%=Request(" date_sent" )%> & " ' ;"
< Message edited by daveh42 -- 10/1/2002 3:07:50 PM >
| |
|
|
|
mar0364
Posts: 3030
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: SQL Date Range Search. - 10/3/2002 11:15:34
I got the following article from MS Knowledge Base:
FP2000: Error Message: Microsoft VBScript Error ' 800a0409' Unterminated String Constant
The information in this article applies to:
Microsoft FrontPage 2000
Symptoms
When you are using a Microsoft FrontPage Database connection and you submit a query from one page to another, you may receive an error message similar to the following before the data is returned:
Microsoft VBScript error ' 800a0409' Unterminated string constant
Cause
This behavior can occur if you are using a reserved character in the name of a field or table in the database.
Reserved characters that may cause the error message include the following:
!
@
#
$
%
^
&
*
( )
/ \
{ }
[ ]
'
Resolution
To work around this issue, make sure that none of the reserved characters listed in the " Cause" section are used in the names of fields or tables in the database.
First Published: Jun 13 2000 9:34AM
Keywords: kbdta kbprb ocsso front page
Based on the article the only thing I can think of is the / in the date sent field.
I looked at the option for field settings in SQL it is datetime or timestamp. I don' t see just a date. I understand the DATEPART function but don' t know how to apply it to a search field.
Thanks!
Rich
| |
|
|
|
mar0364
Posts: 3030
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: SQL Date Range Search. - 10/4/2002 8:35:21
Yes I' ve resorted to posting all the code. I am getting the same error.
FP2000: Error Message: Microsoft VBScript Error ' 800a0409' Unterminated String Constant
<html>
<head>
<meta name=" GENERATOR" content=" Microsoft FrontPage 5.0" >
<meta name=" ProgId" content=" FrontPage.Editor.Document" >
<meta http-equiv=" Content-Type" content=" text/html; charset=windows-1252" >
<title>date</title>
</head>
<body>
<form BOTID=" 0" METHOD=" POST" ACTION=" date.asp" >
<table BORDER=" 0" width=" 252" >
<tr>
<td width=" 65" ><b>date_sent</b></td>
<td width=" 177" >
<input NAME=" start_date" VALUE=" <%=Request(" start_date" )%>" size=" 20" ><p>
<input NAME=" end_date" VALUE=" <%=Request(" end_date" )%>" size=" 20" ></td>
</tr>
</table>
<p><br>
<input TYPE=" Submit" ><input TYPE=" Reset" ><!--webbot bot=" SaveAsASP" clientside suggestedext=" asp" preview=" " --></p>
<p> </p>
</form>
<table width=" 100%" border=" 1" >
<thead>
<tr>
<td><b>first_name</b></td>
<td><b>last_name</b></td>
<td><b>company_name</b></td>
</tr>
</thead>
<tbody>
<!--webbot bot=" DatabaseRegionStart" s-columnnames=" first_name,last_name,company_name" s-columntypes=" 202,202,202" s-dataconnection=" Database1" b-tableformat=" TRUE" b-menuformat=" FALSE" s-menuchoice s-menuvalue b-tableborder=" TRUE" b-tableexpand=" TRUE" b-tableheader=" TRUE" b-listlabels=" TRUE" b-listseparator=" TRUE" i-listformat=" 0" b-makeform=" TRUE" s-recordsource s-displaycolumns=" first_name,last_name,company_name" s-criteria s-order s-sql=" SELECT first_name,last_name, company_name <br>FROM Results<br>WHERE date_sent BETWEEN ' <%=Request(" start_date" )%>' AND ' <%=Request(" end_date" )%>' ;<br>" b-procedure=" FALSE" clientside suggestedext=" asp" s-defaultfields s-norecordsfound=" No records returned." i-maxrecords=" 256" i-groupsize=" 0" botid=" 0" u-dblib=" _fpclass/fpdblib.inc" u-dbrgn1=" _fpclass/fpdbrgn1.inc" u-dbrgn2=" _fpclass/fpdbrgn2.inc" tag=" TBODY" preview=" <tr><td colspan=64 bgcolor=" #FFFF00" align=" left" width=" 100%" ><font color=" #000000" >This is the start of a Database Results region.</font></td></tr>" startspan --><!--#include file=" _fpclass/fpdblib.inc" -->
<% if 0 then %>
<SCRIPT Language=" JavaScript" >
document.write(" <div style=' background: yellow; color: black;' >The Database Results component on this page is unable to display database content. The page must have a filename ending in ' .asp' , and the web must be hosted on a server that supports Active Server Pages.</div>" );
</SCRIPT>
<% end if %>
<%
fp_sQry=" SELECT first_name,last_name, company_name FROM Results WHERE date_sent BETWEEN ' <%=Request(" " start_date" " )%>' AND ' <%=Request(" " end_date" " )%>' ; "
fp_sDefault=" "
fp_sNoRecords=" <tr><td colspan=3 align=left width=" " 100%" " >No records returned.</td></tr>"
fp_sDataConn=" Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=" "
fp_sMenuValue=" "
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<!--webbot bot=" DatabaseRegionStart" endspan i-checksum=" 38126" --><tr>
<td>
<!--webbot bot=" DatabaseResultColumn" s-columnnames=" first_name,last_name,company_name" s-column=" first_name" b-tableformat=" TRUE" b-hashtml=" FALSE" b-makelink=" FALSE" clientside b-MenuFormat preview=" <font size=" -1" ><<</font>first_name<font size=" -1" >>></font>" startspan --><%=FP_FieldVal(fp_rs," first_name" )%><!--webbot bot=" DatabaseResultColumn" endspan i-checksum=" 30205" --></td>
<td>
<!--webbot bot=" DatabaseResultColumn" s-columnnames=" first_name,last_name,company_name" s-column=" last_name" b-tableformat=" TRUE" b-hashtml=" FALSE" b-makelink=" FALSE" clientside b-MenuFormat preview=" <font size=" -1" ><<</font>last_name<font size=" -1" >>></font>" startspan --><%=FP_FieldVal(fp_rs," last_name" )%><!--webbot bot=" DatabaseResultColumn" endspan i-checksum=" 20295" --></td>
<td>
<!--webbot bot=" DatabaseResultColumn" s-columnnames=" first_name,last_name,company_name" s-column=" company_name" b-tableformat=" TRUE" b-hashtml=" FALSE" b-makelink=" FALSE" clientside b-MenuFormat preview=" <font size=" -1" ><<</font>company_name<font size=" -1" >>></font>" startspan --><%=FP_FieldVal(fp_rs," company_name" )%><!--webbot bot=" DatabaseResultColumn" endspan i-checksum=" 33069" --></td>
</tr>
<!--webbot bot=" DatabaseRegionEnd" b-tableformat=" TRUE" b-menuformat=" FALSE" u-dbrgn2=" _fpclass/fpdbrgn2.inc" i-groupsize=" 0" clientside tag=" TBODY" preview=" <tr><td colspan=64 bgcolor=" #FFFF00" align=" left" width=" 100%" ><font color=" #000000" >This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file=" _fpclass/fpdbrgn2.inc" -->
<!--webbot bot=" DatabaseRegionEnd" endspan i-checksum=" 62730" --></tbody>
</table>
</body>
</html>
Thanks!
Rich
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: SQL Date Range Search. - 10/4/2002 8:44:52
This section is what I was talking about:
<%
fp_sQry=" SELECT first_name,last_name, company_name FROM Results WHERE date_sent BETWEEN ' <%=Request(" " start_date" " )%>' AND ' <%=Request(" " end_date" " )%>' ; "
fp_sDefault=" "
.......
See, you' ve got nested ' <%' and ' %>' . (You' ve already started the VBScript at the beginning of the fp_sQry section.) I' ve put them in bold. That section should look like:
<%
fp_sQry=" SELECT first_name,last_name, company_name FROM Results WHERE date_sent BETWEEN ' " & Request(" start_date" ) & " ' AND ' " & Request(" end_date" ) & " ' "
fp_sDefault=" "
Try that and see if you now get errors. There may be other issues, but that definitely needs to be fixed.
| |
|
|
|
mar0364
Posts: 3030
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: SQL Date Range Search. - 10/4/2002 8:58:00
Yes that did help thanks!
Now the other issue is:
Database Results Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers
Know anything about this?
| |
|
|
|
mar0364
Posts: 3030
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: SQL Date Range Search. - 10/4/2002 10:55:42
This is my latest code.
<html>
<head>
<title>date</title>
</head>
<body>
<form BOTID=" 0" METHOD=" POST" ACTION=" date.asp" >
<table BORDER=" 0" width=" 252" >
<tr>
<td width=" 65" > </td>
<td width=" 177" >
<input NAME=" start_date" VALUE=" <%=Request(" start_date" )%>" size=" 20" ><p>
<input NAME=" end_date" VALUE=" <%=Request(" end_date" )%>" size=" 20" ></td>
</tr>
</table>
<p><br>
<input TYPE=" Submit" ><input TYPE=" Reset" ><!--webbot bot=" SaveAsASP" clientside suggestedext=" asp" preview=" " --></p>
<p> </p>
</form>
<table width=" 100%" border=" 1" >
<thead>
<tr>
<td><b>first_name</b></td>
<td><b>last_name</b></td>
<td><b>company_name</b></td>
</tr>
</thead>
<tbody>
<!--#include file=" _fpclass/fpdblib.inc" -->
<% if 0 then %>
<SCRIPT Language=" JavaScript" >
document.write(" <div style=' background: yellow; color: black;' >The Database Results component on this page is unable to display database content. The page must have a filename ending in ' .asp' , and the web must be hosted on a server that supports Active Server Pages.</div>" );
</SCRIPT>
<% end if %>
<%
fp_sQry=" SELECT first_name,last_name, company_name FROM Results WHERE date_sent BETWEEN ' & Request(" " start_date" " )' AND ' & Request(" " end_date" " )' ; "
fp_sDefault=" "
fp_sNoRecords=" <tr><td colspan=3 align=left width=" " 100%" " >No records returned.</td></tr>"
fp_sDataConn=" Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=" "
fp_sMenuValue=" "
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<tr>
<td>
<%=FP_FieldVal(fp_rs," first_name" )%> </td>
<td>
<%=FP_FieldVal(fp_rs," last_name" )%> </td>
<td>
<%=FP_FieldVal(fp_rs," company_name" )%> </td>
</tr>
<!--#include file=" _fpclass/fpdbrgn2.inc" -->
</tbody>
</table>
</body>
</html>
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: SQL Date Range Search. - 10/4/2002 11:27:37
It looks like you still have double ' double quotes' in your SQL (and other problems). Look at the line below closely - this is how it should look:
fp_sQry=" SELECT first_name, last_name, company_name FROM Results WHERE date_sent BETWEEN ' " & Request(" start_date" ) & " ' AND ' " & Request(" end_date" ) & " ' "
Make them single ' double qoutes' (not single quotes). You also have an extra semicolon and some other syntax problems (you are missing &' s and such). Take the line I posted just above, copy and paste it into notepad (to remove any formatting), then paste it into your page in HTML view (replacing the original line). If you want, you can comment out the original just so you still have it.
Let us know if that works....
| |
|
|
|
mar0364
Posts: 3030
Joined: 4/5/2002
From: Florida, US
Status: offline
|
RE: SQL Date Range Search. - 10/4/2002 13:55:52
It works!!!!!!!!!!!!!!!!!!!!!!
Thanks! A whole lot!
| |
|
|
|
Buffcode18
Posts: 1
Joined: 10/11/2005
Status: offline
|
RE: SQL Date Range Search. - 10/11/2005 12:54:19
I'm having a related problem. Using this, is there any way to add something to the WHERE clause in order to return a 0 or NULL in dates where there are no entries? Right now it just returns no row
| |
|
 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 
Wouldn' t it be something like start_date to end_date or something like that?? 