# results per page

[jrthor2]

I have a member page that lists all of our current members. How would I split the page so it only shows 10 members per page and have "next" and "prev" links to navigate?

Thanks!

[Bullschmidt]

Database Paging
http://www.asp101.com/samples/db_paging.asp
Uses PageSize method of recordset.

[jrthor2]

Ok, I used this example but I am getting the following errer:

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/new_members/index.asp, line 100

Below is my code (I bolded where Line 100 is):

<%
Dim iPageSize 'How big our pages are
Dim iPageCount 'The number of pages we get back
Dim iPageCurrent 'The page we want to show
Dim iRecordsShown 'Loop controller for displaying iPageSize records
Dim I 'Standard looping var
Dim strOrderBy

' Get parameters
iPageSize = 2 ' You could easily allow users to change this

' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If

If sort="MembershipDate" Then
strOrderBy = "MembershipDate Desc"
Else If sort="DateLastTalked" Then
strOrderBy = "DateLastTalked Desc"
Else If Request.Querystring("sort") = "" Then
strOrderBy = "LastName Asc"
End If
End If
End If

SQLstmt = "SELECT * from New_Members order by " & strOrderBy & ";"

Set conn = server.createobject("adodb.connection")
DSNtemp="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("new_members.mdb")
conn.Open DSNtemp

Set objPagingRS = Server.CreateObject("adodb.recordset")
objPagingRS.PageSize = iPageSize

objPagingRS.CacheSize = iPageSize

' Open RS (THIS IS LINE 100)
objPagingRS.Open SQLstmt, conn, adOpenStatic, AdLockOptimistic, adCmdText


' Get the count of the pages using the given page size
iPageCount = objPagingRS.PageCount

' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1

' Check page count to prevent bombing when zero results are returned!
If iPageCount = 0 Then
Response.Write "No records found!"
Else
' Move to the selected page
objPagingRS.AbsolutePage = iPageCurrent

set rs = conn.Execute(SQLstmt)
x=0
DIM iRecordCount,mycolor
iRecordCount = 0
DO WHILE NOT rs.EOF
x=x+1
If iRecordCount Mod 2 = 0 Then
mycolor = "#d7d7ff"
Else
mycolor = "#ffffff"
End If

LastName = rs("LastName")
FirstName = rs("FirstName")
SpouseName = rs("SpouseName")
Notes = rs("Notes")
ChildrenNames1 = rs("ChildrenNames1")
Age1 = rs("Age1")
ChildrenNames2 = rs("ChildrenNames2")
Age2 = rs("Age2")
ChildrenNames3 = rs("ChildrenNames3")
Age3 = rs("Age3")
ChildrenNames4 = rs("ChildrenNames4")
Age4 = rs("Age4")
Address = rs("Address")
City = rs("City")
State = rs("State")
ZipCode = rs("ZipCode")
HomePhone = rs("HomePhone")
EmailAddress = rs("EmailAddress")
DateLastTalked = rs("DateLastTalked")
PictureID = rs("PictureID")
MembershipDate = rs("MembershipDate")
MemberID = rs("MemberID")
%>

Can someone please help

[vishu_gupt]

HI,
I think you are getting this error because ASP.dll is not able to resolve the values of "adOpenStatic", "AdLockOptimistic", "adCmdText " . Make sure that you have included the file "ADOVBS.inc". If you do not want to include this file then rewrite the line 100 as follows:
objPagingRS.Open SQLstmt, conn, 3, 3

[sanjuT]

jrthor2 u wrote:

' Get parameters
iPageSize = 2 ' You could easily allow users to change this

how can u allow users to modify this? I am trying to have a part where the user can select (either radio buttons or a dropdown) the # of records they want returned per page.

thanks!

[PeOfEo]

asp classic does not have built in data base pageing? Huh, I did not know that was new to asp.net. When I do paging I can just set it as a data grid property and do not have to fool with any extra code.

[sanjuT]

do u have an option for the user to custom set how manyy results per page is returned???

[PeOfEo]

yes. You can also select if it show page number, how many numbrs to show before it drops in a ... and ifits just a next prev link.