-
I am using ASP to return a list of search results in a table. How can I modify this so that the results are returned 10 search items at a time ? The code I am using is below:
<HTML>
<HEAD>
</HEAD>
<%
dim myConnection
dim rsTitleList
dim connectString
dim sqlString
dim requestPubID
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\biblio.mdb"
Set myConnection = Server.CreateObject("ADODB.Connection")
Set rsTitleList = Server.CreateObject("ADODB.Recordset")
myConnection.Open connectString
requestPubID = Request.Form("PubID")
sqlString = "Select * From titles WHERE PubID = " & requestPubID
Set RSTitleList = myConnection.Execute(sqlString)
If (RSTitleList.BOF) AND (RSTitleList.EOF) then
Response.Write("Sorry, but Publisher Number " & requestPubID & " was not found.")
ELSE
%>
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- BEGIN column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>
Publisher ID
</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>
Title
</FONT>
</TD>
</TR>
<!-- Get Data -->
<% do while not RStitleList.EOF %>
<TR>
<TD BGcolor ="f7efde" align=center>
<font style ="arial narrow" size=2>
<%=RStitleList("PubID")%>
</font>
</TD>
<TD BGcolor ="f7efde" align=center>
<font style ="arial narrow" size=2>
<%=RSTitleList("Title") %>
</font>
</TD>
</TR>
<% RSTitleList.MoveNext%>
<%loop %><!-- Next Row -->
</TABLE>
</center>
</BODY>
<% End if %>
</HTML>
-
working on that ... get back to you soon :-)
-
I did some modification to your code ...
Please take my comments with good heart ..
1. try to avoid using * in your select statments
IE:
select * from ...
it is not a good habit sometimes you will get 10 fields when you need to use only 2 ...
2. You forgot to close the RecordSet and the Connections ..
it means that if you have lots of hits, the access will
be useless after about 25 users .. when the "connections asket" will empty.
3. About my "algorithm" this is the best way I could come out with a "next & Prev situation", there is another way when you are dealing with unique fields that is more efficiant, but I think that this one is pretty good, and it is not complecated.
The way I did it here is really good for the first pages, but as you go along, the search would become heavier and heavier, because on the first page it would grab 10 recrds from the DB and show 10, in the second page it would grab 20 records and show the relevant 10 , in the 3rd page it would grab 30 , and show the last 10 ... etc ...
But it is better then getting 1000 and show 10 everytime (like some codes I have seen).
4. I didn't have the DB , so I haven't checked it .. it might have some bugs in it, but the algorithm should be o.k (it is also 24:00 so my eyes kind of closing ... so please be forgiven) :-)
5. tell me if it doesn't work, I will look at it again/if anyone see an error in my code please say so ..
Good luck.
<HTML>
<HEAD>
</HEAD>
<%
dim myConnection
dim rsTitleList
dim connectString
dim sqlString
dim requestPubID
dim info
dim counter
dim PerPage
dim pageNumber
dim maxToShow
pageNumber=trim (request (pageNumber))
if pageNumber="" or isnull (pageNumber) then
pageNumber=0 ' first page
end if
PerPage=trim (request (PerPage))
if PerPage="" or isnull (PerPage) then
PerPage=10 ' the number per page
end if
'connectString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\biblio.mdb"
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\biblio.mdb"
Set rsTitleList = Server.CreateObject("ADODB.Recordset")
'myConnection.Open connectString
requestPubID = Request.Form("PubID")
sqlString = "Select top " & (PerPage*pageNumber)+pageNumber+1 & " PubID,Title From titles WHERE PubID = " & requestPubID
' you get one more then the PerPage in order to check if there should be a "next button"
Set RSTitleList = myConnection.Execute(sqlString)
if RSTitleList.eof then
Response.Write("Sorry, but Publisher Number " & requestPubID & " was not found.")
else
info= RSTitleList.getrows ()
RSTitleList.close
%>
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- BEGIN column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>
Publisher ID
</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>
Title
</FONT>
</TD>
</TR>
<!-- Get Data -->
<%
maxToShow=(PerPage*pageNumber)+pageNumber
if maxToShow>ubound (info,2) then maxToShow=ubound (info,2)
for counter=(PerPage*pageNumber) to maxToShow
%>
<TR>
<TD BGcolor ="f7efde" align=center>
<font style ="arial narrow" size=2>
<%=info (0,counter)%>
</font>
</TD>
<TD BGcolor ="f7efde" align=center>
<font style ="arial narrow" size=2>
<%=info (1,counter)%>
</font>
</TD>
</TR>
<%next%><!-- Next Row -->
<tr>
<td>
<%if pageNumber>0 then ' there is a previous button
%>
<a href="yourfile.asp?pageNumber=<%=pageNumber-1%>"><-Prev</a>
<%end if
if ubound (info,2)=(PerPage*pageNumber)+pageNumber+1 then ' there is a next button
%>
<a href="yourfile.asp?pageNumber=<%=pageNumber+1%>">||Next-></a>
<%end if%>
</td>
</tr>
</TABLE>
</center>
</BODY>
<% End if
set RSTitleList=nothing
myConnection.close
set myConnection=nothing
%>
</HTML>
