|
 |
asp_databases thread: Re: Paging recordsets - problem with form inputs built into SQL string
Message #1 by "Ken Schaefer" <ken@a...> on Mon, 13 May 2002 16:37:46 +1000
|
|
So build the dates into the URL that the user clicks:
<a
href="results.asp?Start=<%=Server.URLEncode(dteStartDate)%>&End=<%=Server.UR
LEncode(dteEndDate)%>">Next</a>
and get the values from the Request.QueryString collection
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <constancebeckett@h...>
Subject: [asp_databases] Paging recordsets - problem with form inputs built
into SQL string
: Hi
: I'm having problems when I click on the link to the next page of records,
: reason being that when I click the hyperlink it is not posting it is
: referencing the page URL instead, therefore I have no values in startdate
: and enddate and therefore the incorrect SLQ string is used. Any ideas??
: If I don't use form inputs the page works fine, however I require the
: user to input dates.
: Thanks
: Connie
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #2 by "Imar Spaanjaars" <Imar@S...> on Sun, 12 May 2002 15:38:34
|
|
Hi Connie,
Can you please limit the amount of code you post here to relevant stuff
for the problem at hand? This is just way too much code to scan. People
are more likely to answer a question when the problem is presented short
and clear.
Anyway, somehwere at line 4 million three hundred thousand and eighty
four, I see this:
a href="DisplayDetail.asp?<%="Customerfeedbackreferencenumber=" &
rs.Fields.Item
("Customerfeedbackreferencenumber").Value %>
What I can see from this is that you are passing the value of a recordset
column to another page through the querystring.
However, you are not actually specifying the name of the querystring, but
a variable (Customerfeedbackreferencenumber). I think it should be
something like this:
a href="DisplayDetail.asp?Customerfeedbackreferencenumber=<%
=rs.Fields.Item ("Customerfeedbackreferencenumber").Value %>
In this case, Customerfeedbackreferencenumber is the name of the param,
and rs.Fields.Item ("Customerfeedbackreferencenumber").Value is the value
you assign to it.
I am surprised you didn't get an error, as ASP should find that
Customerfeedbackreferencenumber is not defined.
HtH
Imar
> Hi
I> 'm having problems when I click on the link to the next page of
records,
r> eason being that when I click the hyperlink it is not posting it is
r> eferencing the page URL instead, therefore I have no values in
startdate
a> nd enddate and therefore the incorrect SLQ string is used. Any ideas??
I> f I don't use form inputs the page works fine, however I require the
u> ser to input dates.
T> hanks
C> onnie
>
<> % @ LANGUAGE="VBScript" %>
> <%
> Option Explicit
R> esponse.Buffer = True
> Const adOpenForwardOnly = 0
C> onst adLockReadOnly = 1
C> onst adUseClient = 3
C> onst adCmdText = 1
> ' Variables for connection object, recordset, connection string, query
s> tring
D> im conn, rs, connString, qString
> ' Open the database connection
c> onnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
S> erver.MapPath("..\..\database\CustFdbk\CustFdbk.mdb")
S> et conn = Server.CreateObject("ADODB.Connection")
c> onn.Open connString
> %>
> <HTML>
<> HEAD>
<> TITLE> Database paging </TITLE>
<> SCRIPT language=Javascript>
> function ShowStartDate()
> {
> var cSearchValue=showModalDialog
(> "QuickCalendar.asp",0,"dialogwidth=298px;dialogheight=280px");
>
> if (cSearchValue != -1 && cSearchValue != null && cSearchValue !
=> "")
> {
> document.all.startdate.value = cSearchValue;
> }
> }
> function ShowEndDate()
> {
> var cSearchValue=showModalDialog
(> "QuickCalendar.asp",0,"dialogwidth=298px;dialogheight=280px");
>
> if (cSearchValue != -1 && cSearchValue != null && cSearchValue !
=> "")
> {
> document.all.enddate.value = cSearchValue;
> }
> }
<> /SCRIPT>
<> /HEAD>
<> body bgcolor="#99FF66" text="#000000" alink="#000000" link="#0000FF"
v> link="#0000FF">
> <p><font size="5" face="Arial, Helvetica, sans-serif, Verdana"
c> olor="#003366"><b>
> <img src="../TRWred256.jpg" width="65" height="20"> Customer Services
F> eedback Database </b></font></p>
> <H3><FONT FACE=TAHOMA>Please select a start date and an end date
between
w> hich you would like feedback records to be displayed.</FONT></H3>
<> p align=left>
<> TABLE BORDER= CELLPADDING=0 CELLSPACING=0 BGCOLOR=SILVER><TR><TD>
> <TABLE BORDER=0>
> <form action="feedbacklist.asp" method=post name=myform>
> <TR>
> <TD ALIGN=LEFT><b>Start Date:</b></TD>
> <TD ALIGN=LEFT><b>End Date:</b></TD>
> <td rowspan="2" valign="bottom"><input
t> ype="Submit" value="Retrieve Records"</td>
> <td rowspan="2" valign="bottom"><input
t> ype="Button" name="exit" value="Return To Menu"
o> nclick="window.location.href = 'custfdbkmenu.html'"></td>
> </TR>
> <TR>
> <TD ALIGN=LEFT><INPUT type="text" name=startdate
v> alue="<%= request.form ("startdate")%>">
> <A OnClick=ShowStartDate()>
> <IMG align=top border=0 height=25 id=dimg1
s> rc="calendar.gif" width=26>
> </A>
> </TD>
> <TD ALIGN=LEFT><INPUT type="text" name=enddate
v> alue="<%=request.form ("enddate")%>">
> <A OnClick=ShowEndDate()>
> <IMG align=top border=0 height=25 id=dimg1
s> rc="calendar.gif" width=26>
> </A>
> </TD>
> <td></td>
> </TR>
>
> </TABLE>
<> /TD></TR>
<> /TABLE>
<> /p>
> <%
> Dim mypage, numpages, numrecs, pagesize
> ' Get the page number we need to display from Form or QueryString
> mypage = CInt( Request("page") )
> If mypage=0 Then mypage=1
> ' Get the requested number of records per page
> pagesize = CInt( Request("recs") )
> If pagesize = 0 Then pagesize = 10 ' If we aren't given a value
use
1> 0
> ' The code we need to set up a recordset object for paging
> Set rs = Server.CreateObject("ADODB.RecordSet")
> rs.PageSize = pagesize
> rs.CacheSize = pagesize ' This is optional
> rs.CursorLocation = adUseClient
>
> ' Open the recordset
i> f request.form ("startdate")="" or request.form ("enddate") ="" then
q> String ="SELECT * FROM tblCustomerFeedback WHERE
(> ((tblCustomerFeedback.Date)>=#" & formatdatetime(date(),1) &"#) AND
(> (tblCustomerFeedback.Date)<=#" & formatdatetime(date(),1) & "#));"
e> lse
q> String ="SELECT * FROM tblCustomerFeedback WHERE
(> ((tblCustomerFeedback.Date)>=#" & formatdatetime(request.form
(> "startdate"),1) &"#) AND ((tblCustomerFeedback.Date)<=#" &
formatdatetime
(> request.form("enddate"),1) & "#));"
e> nd if
> rs.Open qString, conn, adOpenForwardOnly, adLockReadOnly, adCmdText
r> esponse.write qstring
> ' Get the number of pages and records
> numpages = rs.PageCount
> numrecs = rs.RecordCount
> ' Just in case we have a bad request
> If mypage > numpages Then mypage = numpages
> If mypage < 1 Then mypage = 1
> ' Set the page we want to display
> rs.AbsolutePage = mypage
> ' Write out the total number of records in the recordset
> Response.Write("<P> <B>" & numrecs & " records found.</B>" )
> ' Write out the current page number and total number of pages
> Response.Write("<BR> Page " & mypage & " of " & numpages & "</P>")
> Response.Write("<CENTER>")
%> >
> ' Write out the records contained in this page
> <table width="100%" border="0" cellpadding="0" cellspacing="0">
> <tr>
> <td width="100%" height="22" valign="top"
b> gcolor=silver><strong>Search Results</strong></td>
> </tr>
> <tr>
> <td valign="top" height="116">
> <table width="100%" border="0" cellpadding="0" cellspacing="0">
> <tr>
> <td width="100%" height="45" valign="top">
> <table align="center" border="1">
> <tr>
> <td align="default" width="20%"> <b><font size="3"
c> olor="#003399">Customer Name</font></b>
> </td>
> <td align="default" width="20%"> <b><font size="3"
c> olor="#003399">Customer Contact</font></b>
> </td>
> <td align="default" width="20%"> <b><font
s> ize="3" color="#003399">Customer Service Contact</font></b>
> </td>
> <td align="default" width="25%"> <b><font
s> ize="3" color="#003399">Customer Feedback</font></b>
> </td>
> <td align="default" width="5%"> <b><font
s> ize="3" color="#003399">Date Created</font></b>
> </td>
> <td align="default" width="5%"> <b><font
s> ize="3" color="#003399">QAR Ref.</font></b>
> </td>
> <td align="default" width="5%"> <b><font
s> ize="3" color="#003399">Reference Number</font></b>
> </td>
> </tr>
> <%
> Dim i
> For i=1 To pagesize
> If NOT rs.EOF Then%>
<> tr>
> <td align="default">
> <font size="3" color="#993333"><b><%=(rs.Fields.Item
(> "customername").Value)%></b></font> </td>
> <td align="default">
> <font size="3" color="#993333"><b><%if Len
(> rs.Fields.Item("customercontactname").Value)>0 Then response.write
(> rs.Fields.Item("customercontactname").Value) Else response.write
(> " ") End If%></b></font> </td>
> <td align="default">
> <font size="3" color="#993333"><b><%=(rs.Fields.Item
(> "trwcontactname").Value)%></b></font> </td>
> <td align="default">
> <font size="3" color="#993333"><b><%=(rs.Fields.Item
(> "customerfeedback").Value)%></b></font> </td>
> <td align="default">
> <font size="3" color="#993333"><b><%=(rs.Fields.Item
(> "date").Value)%></b></font> </td>
> <td align="default">
> <font size="3" color="#993333"><b><%if Len
(> rs.Fields.Item("QARReferencenumber").Value)>0 Then response.write
(> rs.Fields.Item("QARReferencenumber").Value) Else response.write
(> " ") End If%></b></font> </td>
> <td align="default">
> <a href="DisplayDetail.asp?<%
=> "Customerfeedbackreferencenumber=" & rs.Fields.Item
(> "Customerfeedbackreferencenumber").Value %>"><font size="3"><b><font
c> olor="#993333"><%=(rs.Fields.Item
(> "Customerfeedbackreferencenumber").Value)%></font></b></font></a> </td>
> </tr>
> <% rs.MoveNext
> End If
> Next
> Response.Write("<P>")
> ' Write out the page links
> Dim x, lb, ub
> For x=1 To numpages
> lb = (x-1) * pagesize + 1
> ub = x * pagesize
> If ub > numrecs Then ub = numrecs
> If x <> mypage Then
> Response.Write("<A HREF=feedbacklist.asp?page=" & x
& "&recs="
&> pagesize & ">" & lb & "-" & ub & "</A>")
> Else
> Response.Write(lb & "-" & ub)
> End If
> If x <> numpages Then Response.Write(" | ")
> Next
> Response.Write("</CENTER>")
> ' Clean up
> rs.Close
> conn.Close
> Set rs = Nothing
> Set conn = Nothing
> %>
>
<> P>
>
> <!-- The dropdown box for selecting the number of records to display -->
S> how
<> SELECT NAME=recs OnChange="updateBoxes(myform)">
> <OPTION VALUE=5> 5 <!-- Change values to suit. -->
> <OPTION VALUE=10> 10 <!-- You can add/remove options -->
> <OPTION VALUE=25> 25 <!-- if required, just make sure -->
> <OPTION VALUE=50> 50 <!-- each has a numeric VALUE -->
> <OPTION VALUE=100> 100 <!-- parameter. -->
<> /SELECT>
> records per page
> <BR>
> <!-- The dropdown box for selecting the page to display -->
D> isplay page
<> SELECT NAME=page>
<> OPTION> <!-- You need these empty options to -->
<> OPTION> <!-- fix a bug in Netscape Navigator -->
<> OPTION> <!-- The number of <OPTION> tags you -->
<> OPTION> <!-- put here determines the size -->
<> OPTION> <!-- of the dropdown box -->
<> OPTION>
<> OPTION>
<> OPTION>
<> /SELECT>
> <BR>
> <INPUT TYPE=submit VALUE="GO!">
> </FORM>
> <SCRIPT LANGUAGE="JavaScript">
> NUM_RECORDS = <%=numrecs%>;
> function updateBoxes(theFormObj)
{>
> var selectedRecs = theFormObj.recs.options
[> theFormObj.recs.selectedIndex].value;
> var numpages = Math.ceil(NUM_RECORDS / selectedRecs);
> var numOptions = theFormObj.page.length;
> for(var i=0 ; i<numOptions ; i++) {
> theFormObj.page.options[0] = null;
> }
> for(var j=0 ; j<numpages ; j++) {
> theFormObj.page.options[j] = new Option(j+1,j+1);
> }
> theFormObj.page.selectedIndex = 0;
}>
> </SCRIPT>
> <!-- This fills the page selector with data on start-up -->
<> SCRIPT> updateBoxes(document.myform); </SCRIPT>
> </BODY>
> </HTML>
Message #3 by constancebeckett@h... on Fri, 10 May 2002 11:48:22
|
|
Hi
I'm having problems when I click on the link to the next page of records,
reason being that when I click the hyperlink it is not posting it is
referencing the page URL instead, therefore I have no values in startdate
and enddate and therefore the incorrect SLQ string is used. Any ideas??
If I don't use form inputs the page works fine, however I require the
user to input dates.
Thanks
Connie
<% @ LANGUAGE="VBScript" %>
<%
Option Explicit
Response.Buffer = True
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adUseClient = 3
Const adCmdText = 1
' Variables for connection object, recordset, connection string, query
string
Dim conn, rs, connString, qString
' Open the database connection
connString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("..\..\database\CustFdbk\CustFdbk.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connString
%>
<HTML>
<HEAD>
<TITLE> Database paging </TITLE>
<SCRIPT language=Javascript>
function ShowStartDate()
{
var cSearchValue=showModalDialog
("QuickCalendar.asp",0,"dialogwidth=298px;dialogheight=280px");
if (cSearchValue != -1 && cSearchValue != null && cSearchValue !
= "")
{
document.all.startdate.value = cSearchValue;
}
}
function ShowEndDate()
{
var cSearchValue=showModalDialog
("QuickCalendar.asp",0,"dialogwidth=298px;dialogheight=280px");
if (cSearchValue != -1 && cSearchValue != null && cSearchValue !
= "")
{
document.all.enddate.value = cSearchValue;
}
}
</SCRIPT>
</HEAD>
<body bgcolor="#99FF66" text="#000000" alink="#000000" link="#0000FF"
vlink="#0000FF">
<p><font size="5" face="Arial, Helvetica, sans-serif, Verdana"
color="#003366"><b>
<img src="../TRWred256.jpg" width="65" height="20"> Customer Services
Feedback Database </b></font></p>
<H3><FONT FACE=TAHOMA>Please select a start date and an end date between
which you would like feedback records to be displayed.</FONT></H3>
<p align=left>
<TABLE BORDER= CELLPADDING=0 CELLSPACING=0 BGCOLOR=SILVER><TR><TD>
<TABLE BORDER=0>
<form action="feedbacklist.asp" method=post name=myform>
<TR>
<TD ALIGN=LEFT><b>Start Date:</b></TD>
<TD ALIGN=LEFT><b>End Date:</b></TD>
<td rowspan="2" valign="bottom"><input
type="Submit" value="Retrieve Records"</td>
<td rowspan="2" valign="bottom"><input
type="Button" name="exit" value="Return To Menu"
onclick="window.location.href = 'custfdbkmenu.html'"></td>
</TR>
<TR>
<TD ALIGN=LEFT><INPUT type="text" name=startdate
value="<%= request.form ("startdate")%>">
<A OnClick=ShowStartDate()>
<IMG align=top border=0 height=25 id=dimg1
src="calendar.gif" width=26>
</A>
</TD>
<TD ALIGN=LEFT><INPUT type="text" name=enddate
value="<%=request.form ("enddate")%>">
<A OnClick=ShowEndDate()>
<IMG align=top border=0 height=25 id=dimg1
src="calendar.gif" width=26>
</A>
</TD>
<td></td>
</TR>
</TABLE>
</TD></TR>
</TABLE>
</p>
<%
Dim mypage, numpages, numrecs, pagesize
' Get the page number we need to display from Form or QueryString
mypage = CInt( Request("page") )
If mypage=0 Then mypage=1
' Get the requested number of records per page
pagesize = CInt( Request("recs") )
If pagesize = 0 Then pagesize = 10 ' If we aren't given a value use
10
' The code we need to set up a recordset object for paging
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.PageSize = pagesize
rs.CacheSize = pagesize ' This is optional
rs.CursorLocation = adUseClient
' Open the recordset
if request.form ("startdate")="" or request.form ("enddate") ="" then
qString ="SELECT * FROM tblCustomerFeedback WHERE
(((tblCustomerFeedback.Date)>=#" & formatdatetime(date(),1) &"#) AND
((tblCustomerFeedback.Date)<=#" & formatdatetime(date(),1) & "#));"
else
qString ="SELECT * FROM tblCustomerFeedback WHERE
(((tblCustomerFeedback.Date)>=#" & formatdatetime(request.form
("startdate"),1) &"#) AND ((tblCustomerFeedback.Date)<=#" & formatdatetime
(request.form("enddate"),1) & "#));"
end if
rs.Open qString, conn, adOpenForwardOnly, adLockReadOnly, adCmdText
response.write qstring
' Get the number of pages and records
numpages = rs.PageCount
numrecs = rs.RecordCount
' Just in case we have a bad request
If mypage > numpages Then mypage = numpages
If mypage < 1 Then mypage = 1
' Set the page we want to display
rs.AbsolutePage = mypage
' Write out the total number of records in the recordset
Response.Write("<P> <B>" & numrecs & " records found.</B>" )
' Write out the current page number and total number of pages
Response.Write("<BR> Page " & mypage & " of " & numpages & "</P>")
Response.Write("<CENTER>")
%>
' Write out the records contained in this page
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="100%" height="22" valign="top"
bgcolor=silver><strong>Search Results</strong></td>
</tr>
<tr>
<td valign="top" height="116">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="100%" height="45" valign="top">
<table align="center" border="1">
<tr>
<td align="default" width="20%"> <b><font size="3"
color="#003399">Customer Name</font></b>
</td>
<td align="default" width="20%"> <b><font size="3"
color="#003399">Customer Contact</font></b>
</td>
<td align="default" width="20%"> <b><font
size="3" color="#003399">Customer Service Contact</font></b>
</td>
<td align="default" width="25%"> <b><font
size="3" color="#003399">Customer Feedback</font></b>
</td>
<td align="default" width="5%"> <b><font
size="3" color="#003399">Date Created</font></b>
</td>
<td align="default" width="5%"> <b><font
size="3" color="#003399">QAR Ref.</font></b>
</td>
<td align="default" width="5%"> <b><font
size="3" color="#003399">Reference Number</font></b>
</td>
</tr>
<%
Dim i
For i=1 To pagesize
If NOT rs.EOF Then%>
<tr>
<td align="default">
<font size="3" color="#993333"><b><%=(rs.Fields.Item
("customername").Value)%></b></font> </td>
<td align="default">
<font size="3" color="#993333"><b><%if Len
(rs.Fields.Item("customercontactname").Value)>0 Then response.write
(rs.Fields.Item("customercontactname").Value) Else response.write
(" ") End If%></b></font> </td>
<td align="default">
<font size="3" color="#993333"><b><%=(rs.Fields.Item
("trwcontactname").Value)%></b></font> </td>
<td align="default">
<font size="3" color="#993333"><b><%=(rs.Fields.Item
("customerfeedback").Value)%></b></font> </td>
<td align="default">
<font size="3" color="#993333"><b><%=(rs.Fields.Item
("date").Value)%></b></font> </td>
<td align="default">
<font size="3" color="#993333"><b><%if Len
(rs.Fields.Item("QARReferencenumber").Value)>0 Then response.write
(rs.Fields.Item("QARReferencenumber").Value) Else response.write
(" ") End If%></b></font> </td>
<td align="default">
<a href="DisplayDetail.asp?<%
="Customerfeedbackreferencenumber=" & rs.Fields.Item
("Customerfeedbackreferencenumber").Value %>"><font size="3"><b><font
color="#993333"><%=(rs.Fields.Item
("Customerfeedbackreferencenumber").Value)%></font></b></font></a> </td>
</tr>
<% rs.MoveNext
End If
Next
Response.Write("<P>")
' Write out the page links
Dim x, lb, ub
For x=1 To numpages
lb = (x-1) * pagesize + 1
ub = x * pagesize
If ub > numrecs Then ub = numrecs
If x <> mypage Then
Response.Write("<A HREF=feedbacklist.asp?page=" & x & "&recs="
& pagesize & ">" & lb & "-" & ub & "</A>")
Else
Response.Write(lb & "-" & ub)
End If
If x <> numpages Then Response.Write(" | ")
Next
Response.Write("</CENTER>")
' Clean up
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>
<P>
<!-- The dropdown box for selecting the number of records to display -->
Show
<SELECT NAME=recs OnChange="updateBoxes(myform)">
<OPTION VALUE=5> 5 <!-- Change values to suit. -->
<OPTION VALUE=10> 10 <!-- You can add/remove options -->
<OPTION VALUE=25> 25 <!-- if required, just make sure -->
<OPTION VALUE=50> 50 <!-- each has a numeric VALUE -->
<OPTION VALUE=100> 100 <!-- parameter. -->
</SELECT>
records per page
<BR>
<!-- The dropdown box for selecting the page to display -->
Display page
<SELECT NAME=page>
<OPTION> <!-- You need these empty options to -->
<OPTION> <!-- fix a bug in Netscape Navigator -->
<OPTION> <!-- The number of <OPTION> tags you -->
<OPTION> <!-- put here determines the size -->
<OPTION> <!-- of the dropdown box -->
<OPTION>
<OPTION>
<OPTION>
</SELECT>
<BR>
<INPUT TYPE=submit VALUE="GO!">
</FORM>
<SCRIPT LANGUAGE="JavaScript">
NUM_RECORDS = <%=numrecs%>;
function updateBoxes(theFormObj)
{
var selectedRecs = theFormObj.recs.options
[theFormObj.recs.selectedIndex].value;
var numpages = Math.ceil(NUM_RECORDS / selectedRecs);
var numOptions = theFormObj.page.length;
for(var i=0 ; i<numOptions ; i++) {
theFormObj.page.options[0] = null;
}
for(var j=0 ; j<numpages ; j++) {
theFormObj.page.options[j] = new Option(j+1,j+1);
}
theFormObj.page.selectedIndex = 0;
}
</SCRIPT>
<!-- This fills the page selector with data on start-up -->
<SCRIPT> updateBoxes(document.myform); </SCRIPT>
</BODY>
</HTML>
|
|
|
View Cart
