SQL Date Range Search. (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

mar0364 -> 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



daveh42 -> 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 -> 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 -> 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" )%> & " ' ;"




mar0364 -> 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



rdouglass -> RE: SQL Date Range Search. (10/3/2002 12:38:05)

EDIT: But now that I' m looking at it, why would you have a range of date_sent to date_sent? [:)] Wouldn' t it be something like start_date to end_date or something like that??

WHERE date_sent BETWEEN <%=Request(" start_date" )%> and <%=Request(" end_date" )%>

Also, you don' t need the <% and %> (since you' re probably already inside a VBScript)[:)]

Try:

" SELECT tracker_number, first_name, last_name, company_name, date_sent
FROM Results
WHERE date_sent BETWEEN " & Request(" start_date" ) &" and " & Request(" end_date" )






mar0364 -> 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" >&lt;&lt;</font>first_name<font size=" -1" >&gt;&gt;</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" >&lt;&lt;</font>last_name<font size=" -1" >&gt;&gt;</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" >&lt;&lt;</font>company_name<font size=" -1" >&gt;&gt;</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 -> 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 -> 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 -> 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 -> 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 -> RE: SQL Date Range Search. (10/4/2002 13:55:52)

It works!!!!!!!!!!!!!!!!!!!!!!

Thanks! A whole lot!



Buffcode18 -> 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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1103516