Next 5 records

By ServerWatch Staff (Send Email)
Posted Jun 10, 1999


by Armand Datema

After Seeing a lot of questions on limiting the number of records returned, I decided to make this little tutorial.

After Seeing a lot of questions on limiting the number of records returned, I decided to make this little tutorial.

Ok, lets start with the Cursor Type values

<%
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

After this we continue with the Locking Type values

Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

Now lets Create the database connection

Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")

Now lets open the database connection, create the SQL Statement and open the result set. , my ODBC name hee is contact and the table I am going to query is called  contacts

Conn.Open "contact"
sql=" select * from contacts"

RS.Open sql, Conn, adOpenKeyset,adLockReadOnly
TotalRows=RS.RecordCount

The code so far was pretty standard, in the following part we are going to set the limit for the number of records to be displayed per page and be avle to navigate back and forward.

Lets start with setting the number of rows diplayed per page, for thsi example i chose 5

RS.PageSize = 5' Number of rows per page
PageSize=RS.PageSize

Now we need to enter the code for the buttons to go to the next 5 or the previous 5 results.

if Request("Action") = "" Then
FormAction = " pietje.asp" '
Yes this is one of the forms that subbmits to itself
end if
ScrollAction = Request("ScrollAction")
if ScrollAction <> "" Then
'Create Page Information
PageNo = mid(ScrollAction, 5)
if PageNo<1 Then
PageNo = 1
end if
else
PageNo = 1
end if
RS.AbsolutePage = PageNo

Now we need to add the code to check how many pages there are

PageNumber=PageNo
TotalPages=RS.PageCount%>

So far we completed most of the script, all we need to do now is create the layout, html code and incorporate the above script in the page

Lets start by creating the beginning of the form

<html>
<head>
<title> pietje</title>
</head>
<body bgcolor=#FFFFFF>
<p><%Response.Write("")%></p>
<FORM METHOD=GET ACTION="<%=FormAction%>">

Now we add the code to loop through the results.

<%Do while not (RS is nothing) %>

Lets create the HTML table, for more info look at the comments

<TABLE BORDER=1>
<TR>
<th align="Left" valign="Top" bgcolor=#00FFFF>
<font color=#000000>naampje</font></th>
</TR>
<%RowCount = rs.PageSize
Do While Not RS.EOF and rowcount>0%>
<TR>
<td align="Left" valign="Top" bgcolor="#FFFFFF">
<font color="#000000"><%=RS("Name")%></font>&nbsp;</td>
</TR>
<%RowCount=RowCount-1
'Now we move to the next record in the recordset
RS.MoveNext
Loop%>
<P>
<%
'Now we set the result to the next recordset
set RS=RS.NextRecordSet
Loop
'Finally we close the connnection
Conn.Close
set RS=nothing
set Conn=nothing%>
</TABLE>

All that is left now is the code for the navigation buttons.

'This code is for the button to go to the previous result, as you can see it starts when pageno=1, this is because on page 0 with the first 5 records, there is no previous result
<INPUT TYPE="HIDDEN" NAME="sql" VALUE="<%=sql%>">
<%if PageNo>1 Then %>
<INPUT TYPE="SUBMIT" NAME="ScrollAction" VALUE="<%="Page " & PageNo-1%>">
<%end if %>
'The following code is for the button to go the next result
<%if PageNo<TotalPages or (PageNo=1 and totalpages>1) Then %>
<INPUT TYPE="SUBMIT" NAME="ScrollAction" VALUE="<%="Page " & PageNo+1%>">
<%end if %>

And finally the closing HTML tags

</FORM>
</body>
</html>

This completes yer ASP page, look below for the complete code of pietje.asp

<%
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
Conn.Open "contact"
sql=" select * from contacts"
RS.Open sql, Conn, adOpenKeyset,adLockReadOnly
TotalRows=RS.RecordCount
RS.PageSize = 5' Number of rows per page
PageSize=RS.PageSize
if Request("Action") = "" Then
FormAction = " pietje.asp" 'NOTE This is the name of the current form, in this case pietje.asp
end if
ScrollAction = Request("ScrollAction")
if ScrollAction <> "" Then
'Create Page Information
PageNo = mid(ScrollAction, 5)
if PageNo<1 Then
PageNo = 1
end if
else
PageNo = 1
end if
RS.AbsolutePage = PageNo
PageNumber=PageNo
TotalPages=RS.PageCount%>
<html>
<head>
<title> pietje</title>
</head>
<body bgcolor=#FFFFFF>
<p><%Response.Write("")%></p>
<FORM METHOD=GET ACTION="<%=FormAction%>">
<%Do while not (RS is nothing) %>
<TABLE BORDER=1>
<TR>
<th align="Left" valign="Top" bgcolor=#00FFFF>
<font color=#000000>naampje</font></th>
</TR>
<%RowCount = rs.PageSize
Do While Not RS.EOF and rowcount>0%>
<TR>
<td align="Left" valign="Top" bgcolor="#FFFFFF">
<font color="#000000"><%=RS("Name")%></font>&nbsp;</td>
</TR>
<%RowCount=RowCount-1
'Now we move to the next record in the recordset
RS.MoveNext
Loop%>
<P>
<%
'Now we set the result to the next recordset
set RS=RS.NextRecordSet
Loop
'Finally we close the connnection
Conn.Close
set RS=nothing
set Conn=nothing%>
</TABLE>
'This code is for the button to go to the previous result, as you can see it starts when pageno=1, this is because on page 0 with the first 5 records, there is no previous result
<INPUT TYPE="HIDDEN" NAME="sql" VALUE="<%=sql%>">
<%if PageNo>1 Then %>
<INPUT TYPE="SUBMIT" NAME="ScrollAction" VALUE="<%="Page " & PageNo-1%>">
<%end if %>
'The following code is for the button to go the next result
<%if PageNo<TotalPages or (PageNo=1 and totalpages>1) Then %>
<INPUT TYPE="SUBMIT" NAME="ScrollAction" VALUE="<%="Page " & PageNo+1%>">
<%end if %>
</FORM>
</body>
</html>

Download Code

Page 1 of 2


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.