Download demo project - 15 Kb
Introduction
Every once in a while I come across the task of displaying a large number of records in ASP pages. The good example is displaying the results of a search. Most of the time I do not know the number of records that I have to display in advance. In addition to this, as the usage of the application growth the size of the database will grow accordingly. That leaves me as well as anyone with the similar application requirements no other choice, but to develop some kind of algorithm to display records in the smaller chunks - pages.
Everyone is familiar with the way search results are displayed by Internet search engines. You get the first page of results that are limited to some number of records (20 for example) and some navigational links to go to the first, previous, next or the last page. Some sites give you the ability to go directly to specific page number, some use a mixture of both.
So how does one implements record paging mechanism in ASP? Specifically, how do we implement record paging using ADO?
Let’s pretend that we have a database with the table called tblItem that is used to store information about our Items (whatever they are?). Let me also imagine that one of the fields in tblItem called ItemName. We are given a task of creating a set of pages to give a user an ability to search for the items by the ItemName field. We decided to make a set of two pages. One page will display the search form and one for the results of the search.
Please excuse me, but I will skip all the variable declarations and HTML formatting.
First page should be easy. It’s a standard HTML form that could look something similar to this:
...
<FORM ACTION="results.asp" METHOD="GET">
Item Name: <INPUT TYPE="text" NAME="Keyword"> <INPUT TYPE="submit" VALUE=" Find ">
</FORM>
...
Second page is where all the magic should happen. This is what the second page (results.asp) should be able to do:
- Receive the Keyword that user have entered.
- Search the database for records containing Keyword.
- Display a page of resulting records.
- Provide user with some navigation links to display more pages of results if needed.
1. Receive the Keyword
Receiving the Keyword is as easy as:
Keyword = Trim(Request.QueryString("Keyword"))
2. Search the database for records containing Keyword
Now we have everything we need to get an ADO recordset with the items that contain our keyword in their ItemName.
First we create a sql statement that will do the search:
SQL = "SELECT * FROM tblItem WHERE ItemName LIKE '%" & Replace(Keyword, "'", "''") & "%'"
Notice that I’ve used Replace function to double single quotes in the search string. Without it if user enters a single quote in his/her Keyword you will receive an error.
' Create and open ADO recordset
Set RS = Server.CreateObject("ADODB.Recordset")
RS.CursorLocation = 3 ' adUseClient
RS.Open SQL, strConnectionString
Notice that I set CursorLocation property of the recordset to adUseClient (3). We have to do it in order to be able to use some of the properties of this recordset later. I left out the CursorType and LockType parameters, because the defaults adOpenForwardOnly and adLockReadOnly are exactly what we need in this case to get the best performance.
4. Provide navigation links.
Yes it is a fourth step. I did leave the third step (displaying of the results) for the last because in order for us to display the records we need to figure some things out. I also think it is better to create and display navigation links on the top of the page before the results.
At this point we have to figure out couple of things: do we have any results from our search and if so how many pages of results do we have?
Presence of the results is easily determent by checking the EOF property of the recordset:
If RS.EOF Then
RS.Close
... ' Clean up
... ' Do the no results HTML here
Response.Write "No Items found."
Response.End
... ' Done
End If
The number of pages we have is obviously depends on the number of items we want to display per page.
' Set the page size of the recordset
RS.PageSize = nItemsPerPage
' Get the number of pages
nPageCount = RS.PageCount
Now we need to talk about the current page number. Since we want this page (results.asp) to be able to display any one of the pages of results we have to have a way to specify which page will the user see right now. We will do it by passing an additional parameter to our results.asp script that we will call "Page". So the link to our page could look like this:
http:
With that said we can figure out the number of the page that is requested by the user by simple checking Page parameter:
nPage = CLng(Request.QueryString("Page"))
We need to make sure that the number of page requested by user is within the acceptable range and fix it if needed:
If nPage < 1 Or nPage > nPageCount Then
nPage = 1
End If
Now we can create our navigation links by simply linking to this page with the same value for Keyword and the specific page number. For example:
' First page
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=1"">First Page</A>"
' Previous page:
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=" & nPage - 1 & """>Prev. Page</A>"
' Next page
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=" & nPage + 1 & """>Next Page</A>"
' Last page
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=" & nPageCount & """>Last Page</A>"
' 15th page:
Response.Write "<A HREF=""results.asp?Keyword=" & Keyword & "&Page=15"">15th Page</A>"
3. Display a page of resulting records.
All we have left is to display one page of results:
' Position recordset to the correct page
RS.AbsolutePage = nPage
' Display the page of results
Do While Not ( RS.Eof Or RS.AbsolutePage <> nPage )
Response.Write RS("ItemName") & "<br>"
RS.MoveNext
Loop
RS.Close
That is it. Sample that I have included here is a bit more complex, because I've combined both HTML search form and displaying of the results in one ASP page. That is why there is an additional Mode parameter is being used in every link. All the code concerning displaying of the results of the search is in the ShowResults() function.
| You must Sign In to use this message board. |
|
|
 |
|
|
Sir I have use ur paging code but i m gatting error on "rs.AbsolutePage = nPage" object not required..
my code is below..
<%@ Language=VBScript %>
<!--#include file="MyConn.asp"-->
<%if request("search")<> "" then
employee=Trim(Request.QueryString ("employee"))
sql="select EmployeeId,EmployeeFirstName,EmployeeLastName,MaleFemale,EmployeeAddress,City from tblemployee where employeefirstname = '"& employee &"'"
set rs = server.CreateObject ("ADODB.recordset")
rs.CursorLocation = 3 'adUseClient
rs.Open sql,connData
if (rs.EOF ) then
'rs.Close
Response.Write "No employee forund"
Response.End
end if
rs.PageSize = 10
nPageCount = rs.PageCount
nPage = CLng(Request.QueryString("Page"))
If nPage < 1 Or nPage > nPageCount Then
nPage = 1
End If
end if
%>
<HTML>
<HEAD>
<title>Example Of Paging...</title>
</HEAD>
<BODY>
<%' First page
Response.Write "First Page"
' Previous page:
Response.Write "Prev. Page"
' Next page
Response.Write "Next Page"
' Last page
Response.Write "Last Page"
' 15th page:
Response.Write "15th Page"
%>
<form name="paging" action="Paging.asp" method="get">
Enter Employee Name : <input type="text" name="employee" size="20" value=<%=employee%>>
<input type="submit" name="search" value="Search">
<%rs.AbsolutePage = nPage ////////Error occuer.
' Display the page of results
Do While Not ( rs.Eof Or rs.AbsolutePage <> nPage )
Response.Write rs("employee") & "
"
rs.MoveNext
Loop
rs.Close
%>
</form>
</BODY>
</HTML>
Life is not easy ,let's make it.
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
|
|
|
I was wondering if there was any way to have the search form appear on the results page. This would save the user from having to hit the "back home" link to do another search.
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
I took the pageing example from above and divided in HTML and ASP response page (at least I tried)
But ShowResults() is giving:
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'ShowResults'
/amos13.asp, line 88
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
I have a problem in paging. The same records(.htms) are displayed in all the pages.Even though there is one record matching the search string,the page numbers(1,2 3.....) are displayed.
<%
Dim currentPage, rowcount, i, counter, startnum , pgSize,objQuery
currentPage=trim(request("currentPage"))
counter=trim(request("counter"))
srchstr=trim(request("search"))
strlen=len(srchstr)
found="false"
srch = Replace( srchstr, "'", "")
if currentPage="" or currentPage="0" then
currentPage=1
startnum=1
counter=10
end if
set con=server.createObject( "ADODB.CONNECTION" )
con.open "PROVIDER=msidxs"
if srchstr <> "" then
set objQuery=Server.CreateObject("ixsso.Query")
With objQuery
.Catalog = "kec" ' Catalog to query
.Query = srch
.SortBy = "rank[d]"
.Columns = "DocTitle, Characterization, vpath, filename, size, write, rank"
.MaxRecords = 500
End with
Set RS1=objQuery.CreateRecordset("nonsequential")
RS1.PageSize=5
'SQLstmt = "select Characterization, Directory, HitCount, rank, DocTitle, VPATH From SCOPE() "&_
' "Where FREETEXT (Contents, '" &srch& "') > 0 "&_
' "Order By Rank DESC "
'Set RS1 = con.execute (SQLstmt)
if not RS1.EOF then
ct=0
do while not rs1.eof
ct=ct+1
rs1.movenext
loop
rs1.movefirst
end if
end if
Const intRecordsPerPage = 5
Set conn= server.createobject("ADODB.connection")
conn.open application("txtDatabase")
if srchstr = "" then
call norecords()
else
call search()
end if
sub search()
%>
In search module, i am calling sub Description
<%
sub description()
Set conn= server.createobject("ADODB.connection")
conn.open application("txtDatabase")
Set RS=Server.CreateObject("ADODB.Recordset")
RS.CursorType=adOpenStatic
RS.PageSize=5
RS.Open "Select * from tblSearch where txtFamily like '%"&srchstr&"%'or txtPName like '%"&srchstr&"%' or txtType like '%"&srchstr&"%' or memoText like '%"&srchstr&"%' or txtSelection like '%"&srchstr&"%' order by txtPCode ", Conn
pgSize=RS.PageSize
RecNum=RS.RecordCount + ct
response.write RS.RecordCount
statPg=RecNum/pgSize
y=fix(statPg)
r=statPg-y
if r > 0.0 then
statPg=y+1
end if
if rs.eof and rs1.eof then
exit sub
end if
if not rs.eof then
RS.AbsolutePage=cINT(currentPage)
end if
' RS1.AbsolutePage=cINT(currentPage)
while not rs1.eof and rowcount < pgSize
feadisp="false"
if not rs.eof then
if len(chkword(rs("memoText"),srchstr)) then
feadisp="true"
end if
if len(chkword(rs("txtFamily"),srchstr)) then
feadisp="true"
end if
if len(chkword(rs("txtPName"),srchstr)) then
feadisp="true"
end if
if len(chkword(rs("txtSelection"),srchstr)) then
feadisp="true"
end if
end if
if not RS1.EOF then
feadisp="true"
end if
if not rs.eof then
select case trim(rs("txtFamily"))
case "AC Motors"
path="/products/ACMotors/"
case "Alternators/ AC Generators"
path="/products/ACGenerators/"
case "DC Machines"
path="/products/DCMachines/"
case "Transformers"
path="/products/Transformers/"
case "Traction"
path="/products/Traction/"
case "Switchgear"
path="/products/Switchgear/"
case "Electronics"
path="/products/Electronics/"
case "DG Sets"
path="/products/Gensets/"
end select
end if
if feadisp="true" then
if not rs.eof then
select case rs("txtSelection")
case "applications"
aspnm="applica.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
case "features"
aspnm="feature.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
case "description"
aspnm="descript.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
case "specifications"
aspnm="stdspecs.asp?Product=" & rs("txtPCode") & "&Prdname=" & rs("txtPName")
end select
if rs("txtType")<>"" then
dispstr="" & rs("txtPName") & " - " & rs("txtType") & " - " & rs("txtFamily") & "
" & rs("memoText") & "
"
else
dispstr="" & rs("txtPName") & " - " & rs("txtFamily") & "
" & rs("memoText") & "
"
end if
feadisp="false"
end if
end if
if len(dispstr) or ct <> 0 then
found="true"
else
found="false"
end if
if (mid(RS1("VPath"),len(RS1("VPath"))-3)) = ".htm" then %>
<%=RS1("DocTitle")%>
<% st = 1
if (InStr(1, RS1("DocTitle"), "About Us",1) > 1) or (InStr(1, RS1("DocTitle"), "Projects",1) > 1) then
st=Cint(InStr(1, RS1("Characterization"), "Click, to know more about.",1))+27
end if
response.write(mid(RS1("Characterization"),st))%>
<% end if
response.write dispstr
rowcount=rowcount+1
if not rs.eof then
rs.movenext
end if
if not rs1.eof then
rs1.movenext
end if
Wend
if currentPage <> 1 and currentPage > 9 then
startnum=mid(currentPage,1,len(currentPage)-1)&"0"
else
startnum=1
end if
counter=startnum+10
srchstr=Server.URLEncode(srchstr)
response.write("Pages : ")
if currentPage > 9 then
response.write("First ")
response.write(" ")
response.write("Previous ")
response.write(" ")
end if
if startnum=1 then
counter=counter-1
end if
for i=startnum to statPg
'pg=RS1.Pagecount+RS.Pagecount
'for i=startnum to RS.Pagecount-1
if i < cINT(counter) then
if i = cINT(currentPage) then
response.write(""&i&" ")
else
response.write(""&i&" ")
response.write(" ")
end if
else
counter=counter+10
response.write(" ")
if i <> statPg then
'if i <> RS.Pagecount-1 then
response.write(" Next ")
response.write(" ")
response.write(" Last ")
'response.write(" Last ")
else
response.write(" Last ")
'response.write(" Last ")
end if
exit for
end if
next
end sub
%>
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
Hi I was wondering if there was a way to dynamically displaying information from the database as its being updated. For example as enteries are being added to the database the asp page is dynamically displaying that data without haing to refresh ???
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
After Inserting the values into database or after updating the values you can the function databind. so that it will refelect in datagrid...
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
Hi,
I'm trying to only show 10 records at a time. I have the first 10 records showing. The problem is that when I click the Next button I get an error message: ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. Also different people are going to search for different things so there will always be a different amount of records returned each time. I can't figure this out. My code is:
<body>
<%
Dim CurPage 'the page we want to show
Dim RowCount 'Loop controller for displaying the .PageSize
Dim i 'standard looping variable
Dim strSQL
CurPage = Request.QueryString("page")
If CurPage = "" then CurPage = 1 'set the first page number
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = 1
objRS.PageSize = 10 'number of records per page
objRS.Open varSQL, varConnString
RowCount = 0
'Need to pass parameters here!
'strSQL = "SELECT DATE, LASTNAME, FIRSTNAME, UCR, ADDRESS, CITY, STATE, DISTRICT, PID FROM (GeocodedMaster)"
'strSQL = strSQL & "WHERE DISTRICT IN(" & varDistrictList & ") AND UCR IN(" & varUCRList & ") AND DATE >= #"&varFromDate&"# AND DATE <= #"&varToDate&"# "
'strSQL = strSQL & "ORDER BY DISTRICT, DATE DESC"
'Response.Write "SQL Query: " & strSQL & "
" & vbCrLF
%>
<%dim intCount
intCount = objRS.RecordCount 'total number of records found%>
<%While Not objRS.EOF and RowCount < objRS.PageSize
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("DISTRICT") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("PID") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("LASTNAME") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("FIRSTNAME") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("ADDRESS") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("CITY") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("STATE") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("UCR") & "
")
Response.Write(" | ")
Response.Write("")
Response.Write(objRS.Fields("DATE") & "
")
Response.Write(" | ")
Response.Write(" ")
RowCount = RowCount + 1
objRS.MoveNext
Wend%>
Page <%=CInt(CurPage)%> of <%=objRS.PageCount%> <!-- display current page number out of the total number of pages -->
<%if CInt(CurPage) > 1 Then%> <!-- to move to the previous page if off the first page-->
<< Prev Page
<%End if%>
<%if CInt(CurPage) < objRS.PageCount Then%> <!-- to move to the next page if there is more than 10 records-->
Next Page >>
<%End if%>
<%objRS.Close
Set objRS = Nothing
%>
If anyone could help me I would greatly appreciate it.
Thanks
| Sign In·View Thread·PermaLink | 1.00/5 |
|
|
|
|
|
|
Hi,
I'm not an expert, but..
I believe you are reaching the end of the recordset and not stopping your while loop.
<%While Not objRS.EOF and RowCount < objRS.PageSize
This statement requires both conditions to be true, and that may not always occur at the correct time. Using an OR condition may work better in this case.
Hope this helps.
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
It's good to say you're not an expert when you're not one...
Now, look carefully:
the NOT operator has precedence over the AND.
The loop will end when either of the following (or both) occurs:
- objRS.EOF becomes true
- RowCount becomes greater than or equal to objrs.pagesize
Which, i guess, is exactly what the author wanted.
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
It does need the "loop." I tried this code. In order for the loop to end, it has to actually loop through the records. If you don't say "loop" at the end, it won't do it.
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
|
|
|
'''''''''''''''Paging in Recordset
.
.
.
if QueryString("page")="" then
intPage=1
else
intPage=QueryString("page")
end if
.
.
.
rsRS.Open strSQL, strCon
rsRS.PageSize = intNumberNewsCT
rsRS.CacheSize = rsRS.PageSize
intPageCount =rsRS.PageCount
intRecordCount = rsRS.RecordCount
intPageSize = rsRS.PageSize
If CInt(intPage) > Cint(intPageCount) Then
intPage = intPageCount
End If
If CInt(intPage) <= 0 Then
intPage = 1
End If
If intRecordCount > 0 Then
rsRS.AbsolutePage = intPage
intStart = rsRS.AbsolutePosition + ( intPage -1) * intPageSize
If Cint(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (rsRS.PageSize - 1)
End If
End If
.
.
.
'Display recordset
for i=1 to rsRS.PageSize
....
I'm an beginner !
|
| Sign In·View Thread·PermaLink | 5.00/5 |
|
|
|
|
|
|
 I have a big problem.
In a site I've developed there's an administrative service for the newsletter.
All is ok but I've the problem that the script times out because of the large numbers of subscribers.
So I need to page the result and sending paged to the SMTP server so it cannot slow down and stop.
Can somebody help me?
Fabio Fazi
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
|
|
|
Hi,
Thanks for the article that you provided. In the article, you provided
a single keyword search. What do we need to do if we want to let users to
input multiple keywords? What will the syntax looks like when we want to display the navigation for user? Thanks in advance
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
Hi, What happens if your table size is very large , say around 1 million items are stroed in it.
Since you specifed adUseClient, then rs.Open won't return until the complte recordset has been opened and downoaded to client.
In this case client will seem to hang...
Could you pl suggest how to reolve this issue of very large databse size.
Regards,
Mukesh
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
None of your users has the time or desire to looks thru 1 million of items. You can limit the number of results returned by database to some reasonable number. For example, if you show 20 items per page and you limit a number of pages someone can see to a 100 you will only need to bring first 2000 records from your database. How you are going to limit the number depends on the your application. With SQL server you can use TOP keyword in your select statement, for example.
|
| Sign In·View Thread·PermaLink | 1.00/5 |
|
|
|
|
|
|
|
|
|
|
|
|
Just order descending by doing:
select top 100 * from |
|
|
|
|
|
Since ADO2.5 (I think) you can do this (I remember having to do it Konstantin's way some time ago, but I'm glad he AFO object has got much better in this regard).
All other code is very similiar (except spelling) to Konstantin's except for the Recordset GetRows. The GetRows allows one to get the 28th page of say 25 records from a total recordset of 1 million records very quickly.
RS.Open sSQL, oConn, adOpenStatic, adLockReadOnly
IF oRS.EOF THEN
iNumInResultSet=-1
iNumTotalRecordsFound=0
ELSE
iNumTotalRecordsFound=oRS.RecordCount
iPageRecordStart=((iPageNum-1)*PageSize)
'If the iPageRecordStart exceeds the number of records, then reduce the pagenumber request
Do While(iPageRecordStart>iNumTotalRecordsFound)
iPageNum=iPageNum-1
iPageRecordStart=((iPageNum-1)*PageSize)
Loop
IF iPageRecordStart>0 THEN oRS.Move iPageRecordStart
IF (iPageRecordStart+PageSize+iPageSizeTolerance)>=iNumTotalRecordsFound THEN
aResult=oRS.GetRows
'response.write "Getting Rest "&PageSize&"/"&iPageRecordStart&"/"&iPageNum&"
"
ELSE
aResult=oRS.GetRows(PageSize,0)
END IF
iNumInResultSet=UBound(aResult,2)
'response.write iNumTotalRecordsFound&"/"&iNumInResultSet&"/"&iPageNum
END IF
Dave McRae
|
| Sign In·View Thread·PermaLink | 5.00/5 |
|
|
|
|
|
|
|
|
|
|
|
|
If (nPage>=1) Then
SQL = "SELECT top "&RECORDS_PER_PAGE&" * FROM tblItem WHERE ItemName LIKE '%" & Replace(Keyword, "'", "''") & "%' and ItemID not in(SELECT top "&RECORDS_PER_PAGE*nPage&" ItemID FROM tblItem WHERE ItemName LIKE '%" & Replace(Keyword, "'", "''") & "%' order by ItemID desc) order by ItemID desc"
else
SQL = "SELECT top "&RECORDS_PER_PAGE&" * FROM tblItem WHERE ItemName LIKE '%" & Replace(Keyword, "'", "''") & "%' order by ItemID desc"
End if
TRY THIS AND modify another code.
-- modified at 21:51 Monday 18th December, 2006
Java is My Power.
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
|
