Listing Records and ADO Recordset Paging in ASP

Home > ASP > Listing Records and ADO Recordset Paging in ASP

Listing Records and ADO Recordset Paging in ASP

October 7th, 2009 Mehmatrix Leave a comment Go to comments

Using databases gives us lots of advantages. Listing and paging our records dynamically is one of them. In our applications, we may need to show so many records to user and in order to make it clear we have to list them well-arranged. However, listing too many database records in one time can be a handicap for us, because if the number of records reaches the huge values, then loading pages takes long time. So, we should separate records to pages. It means that in respect of number of database records, pages will be created automatically and a specified number of records will be shown on each page.

We have to know some paging and listing statements to do this issue. There are some paging and listing statements which will be used in this article.

Paging Statements
- PageSize is used to define limit number of records which will be shown in each page.
- AbsolutePage is used to determine the page selected.
- PageCount is used to get number of pages created automatically.

Listing Statements
- Move(X) is used to get Xth record from current record.
- MoveNext is used to get the next record.
- MovePrevious is used to get the previous record.
- MoveLast is used to get the last record.
- MoveFirst is used to get the first record.

There is also RecordCount statement for getting number total records but this is optional. You don’t have to use it.

To make it clear, let’s make an example. We are going to create a database connection to get records. We have talked about connecting databases in ASP before. So, we are skipping this issue now.

Example 1.0: We are going to make a list of customers on customers.asp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

<%
'We are creating our db connection first.
Set MyConn=Server.CreateObject("Adodb.Connection")
MyConn.Open "driver={SQL Server};server=127.0.0.1;uid=memisologin;pwd=memiso;database=memisodb"

'Then we have to define a variable that contains the page number.
MyPage=Request.Querystring("pg")
if MyPage="" Then MyPage=1

'Now we will create our recordset.
Set MyRs=Server.CreateObject("Adodb.Recorset")
MyQuery="Select * from customers order by customer_name asc"
MyRs.Open MyQuery, MyConn, 1, 3

'At least, we are going to define paging parameters.
MyRs.PageSize=5 '5 Records will be shown per page.
MyRs.AbsolutePage= MyPage 'MyPage variable’s value is equalized to absolute page.

'I want to write here the total count of cusmers. This is my choise.
'You don’t need to do this
Response.write "Total Customers: "&MyRs.RecordCount&" "

for i=1 to MyRs.PageSize 'We crated the loop for listing customers
if MyRs.eof then exit for 'If there is no record to show then we break the loop
Response.Write i+((MyPage-1)*MyRs.PageSize) 'We give numbers to each customer
Response.Write " ) "&MyRs("customer_name")&" " 'Customer’s name is written.
MyRs.MoveNext 'The next record is requested.
Next

'Listing customers is done. There is one more step to finish this application.
'We need to write links for the automatically created pages.
Response.Write " Pages: "
for p=1 to MyRs.PageCount 'Loop starts from 1 to number of total pages created.
Response.Write "<a href=customers.asp?pg="&p&">"&p&"</a> "
next
%>

Output of Example 1.0: customers.asp?pg=1

1
2
3
4
5
6
7
8
9

Total Customers: 17

1 ) Keanu Reeves
2 ) Laurence Fishburne
3 ) Carrie-Anne Moss
4 ) Hugo Weaving
5 ) Marcus Chong

Pages: 1 2 3 4

Output of Example 1.0: customers.asp?pg=3

1
2
3
4
5
6
7
8
9

Total Customers: 17

11 ) Craig Bierko
12 ) Gretchen Mol
13 ) Dennis Haysbert
14 ) Armin Mueller-Stahl
15 ) Vincent D'Onofrio

Pages: 1 2 3 4

We stated the basics of paging and listing records. From now on, you can make different algorithms to make your lists. There are some little additions for making this example more useful.

1.1 ) Detecting the page selected and emphasizing it.

If we want to write the page selected different from the other page links, we can use these codes instead of the codes that start at line 32 in example 1.0.

Example 1.1: An Addition to Example 1.0

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<%
…

Response.Write " Pages: "
for p=1 to MyRs.PageCount
if p=MyPage Then
Response.Write "<a href=customers.asp?pg="&p&">M</a> "
else
Response.Write "<a href=customers.asp?pg="&p&">"&p&"</a> "
end if
next

…
%>

Output of Example 1.1: customers.asp?pg=1

1
2
3
4
5
6
7
8
9

Total Customers: 17

1 ) Keanu Reeves
2 ) Laurence Fishburne
3 ) Carrie-Anne Moss
4 ) Hugo Weaving
5 ) Marcus Chong

Pages: M 2 3 4

Output of Example 1.1: customers.asp?pg=3

1
2
3
4
5
6
7
8
9

Total Customers: 17

11 ) Craig Bierko
12 ) Gretchen Mol
13 ) Dennis Haysbert
14 ) Armin Mueller-Stahl
15 ) Vincent D'Onofrio

Pages: 1 2 M 4

As you see, we wrote M for the page selected. Henceforth, imagination is up to you.