Home/Tutorials/ASP/ASP Recordset Paging with Page Numbers
ASP Recordset Paging with Page Numbers
May 26, 2010 in ASP by Alan Holmes

ASP Recordset Paging with Page Numbers

1 Star2 Stars3 Stars4 Stars5 Stars
No Ratings Yet
Loading ... Loading ...

This Tutorial will explain how to create paging in ASP. As well as create the standard next and previous links, it will also output page numbers.

Tags: , , ,

About the Author: Alan Holmes

Hi I'm Alan Holmes, the owner of AHdevelopment. I have been doing web development for many years, and I created this site so that I can finally start archiving my development solutions in one central place, as well as making them available for others to find.

Alan Holmes has written 8 article(s).

The Idea

You’ll be hard pressed to find a dynamic website these days that doesn’t make use of paging in some way or form, especially on list pages such as Products, News, etc. For a long time, I used the in built server behaviours in Dreamweaver, which suited most purposes, as they provided working previous, next, first and last links.

Problems started however as pages got more complex, and for unknown reasons the generated code would work on some sections and not others, even despite the code being the same. This combined with the issues that came with recordsets containing more and more data, I needed to look for an alternative, and more efficient solution.

The Solution

After much searching, I found that the general consensus, for more efficient paging, is to retrieve only the required amount of records from the database, so have 1000 records, then you only get the 50 that you are wanting to display at that time. This will require a little more planning when constructing your code and SQL statements, but will save a lot of load on your database, as you will not be requesting 1000 records on every page load.

In this tutorial, a sample news list page will be created, you can view a demo here.

The Required SQL

Firstly, lets have a look at SQL that we need to retrieve the records, the first is for the first page of records, and the second is to return the second page of results. What the second statement does, is return the top 5 results, after ignoring the first 5 records, and then on following pages we would ignore 10, 15, 20 and so forth records.

/* SQL request for the first page of records */
SELECT top 5 fldNewsID, fldTitle, fldAbstract, fldDate FROM Tbl_News ORDER BY fldDate desc

/* SQL request for the second page of records */
SELECT TOP 5 fldNewsID, fldTitle, fldAbstract, fldDate FROM Tbl_News WHERE (1=1) AND fldNewsID NOT IN (SELECT TOP 5 fldNewsID FROM Tbl_News WHERE (1=1) ORDER BY fldDate desc) ORDER BY fldDate desc

The ASP Functions

We shall be creating a function that will generate these statements for us, but first there are values that need to be set in order to set these values correctly.

'Default Paging Values
intTotalRecords = 0	'Total Records
intNumPages = 0		'Number of pages
intPageSize = -1	'Amount per page, -1 means all
intPageNumber = 1	'The current page to display

'News Table Values
strFrom = "Tbl_News"
strPrimKey = "fldNewsID"
strColumns = "fldNewsID, fldTitle, fldAbstract, fldDate"
strWhere = ""
strOrderBy = "fldDate desc"

'Get the current page number from the querystring
intCurPage = 1
if (trim(request("p")) <> "") and (isnumeric(request("p"))) then
	intCurPage = request("p")
end if 'if (trim(request("p")) <> "") and (isnumeric(request("p"))) then

'Calls the function to set the current paging values
SetPagingValues 5, intCurPage, strFrom, strPrimKey, strWhere

Having set some presents, and gotten the current page number from the querystring, we call a function SetPagingValues that will take these details, and then set the Total Records and Number of Pages so that the paged SQL can be created properly.

Sub SetPagingValues(intTempPageSize, intTempPageNumber, strFrom, strPrimKey, strWhere)
	strSQLCount = ""
	sngTempNumPages = 0
	intTempNumPages = 1

	'Set the Page Size (number of records to show per page)
	If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then
		intPageSize = intTempPageSize
	Else 'If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then
		'not valid number, so set to -1
		'IE all records
		intPageSize = -1
	End If 'If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then

	'Set the page number (the current page being viewed)
	If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then
		intPageNumber = intTempPageNumber
	Else 'If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then
		'if not a valid number, set page number to 1
		intPageNumber = 1
	End If 'If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then

	'SQL to get the total number of records in table
	strSQLCount = "SELECT Count(*) As RecCount From " & strFrom & " WHERE (1=1) "

	'If given add the where clause
	If Trim(strWhere) <> "" Then
		strSQLCount = strSQLCount & " AND " & strWhere
	End If 'If Trim(strWhere) <> "" Then

	'open the sql
	Set objCmd = Server.CreateObject("ADODB.Command")
	objCmd.ActiveConnection = strConnectionString

	objCmd.CommandText = strSQLCount
	set rsRecCount = objCmd.Execute 

	'Set the total records value
	If not rsRecCount.eof Then
		intTotalRecords = rsRecCount("RecCount")
	Else 'If not rsRecCount.eof Then
		intTotalRecords = 0
	End If 'If not rsRecCount.eof Then

	'close the count
	set rsRecCount = nothing

	'close the connection
	objCmd.ActiveConnection = nothing
	Set objCmd = nothing

	'if Total Records and Page size is greater than 0
	'then work out the number of pages
	If (intTotalRecords > 0) And (intPageSize > 0) Then
		sngTempNumPages = intTotalRecords / intPageSize
		intTempNumPages = RoundUp(sngTempNumPages)
	Else 'If (intTotalRecords > 0) And (intPageSize > 0) Then
		intTempNumPages = 1
	End If 'If (intTotalRecords > 0) AND (intPageSize > 0) Then

	'set the num pages
	intNumPages = intTempNumPages

	'if Page number is greater than number of pages, then set to
	'number of pages
	If cint(intPageNumber) > cint(intNumPages) Then
		intPageNumber = intNumPages
	End If 'If intPageNumber > intNumPages Then
End Sub 'Sub SetPagingValues(intTempPageSize, intTempPageNumber, strFrom, strPrimKey, strWhere)

Function RoundUp(intNum)
	'Rounds up the given number
	If (intNum - Fix(intNum) <> 0) Then
		RoundUp = Fix(intNum) + 1
	Else 'If (intNum - Fix(intNum) <> 0) Then
		RoundUp = intNum
	End If 'If (intNum - Fix(intNum) <> 0) Then
end function 'Function RoundUp(intNum)

The RoundUp is simply used to ensure that we have the correct number of pages. As the number of pages is calculated by Total Records/Amount Per page, it is quite possible that the answer could contain a decimal, such as 3.5, in this case, we need to ensure that the next full number (in this case 4) is used.

Now that we have all the variables set correctly, we can call the function to create the SQL statement.

'calls the function to get the paged sql
strSQL = CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)

Function CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)
	'Returns paged dataset for supplied SQL, broken down into sections
	strSQL = ""
	strTopRecords = ""
	intIgnoreRecs = 0
	strPagedWhere = " WHERE (1=1) "

	'Create where clause
	If Trim(strWhere) <> "" Then
		strPagedWhere = strPagedWhere & " AND " & strWhere
	End If 'If Trim(strPagedWhere) <> "" Then

	'create Order clause
	If Trim(strOrderBy) <> "" Then
		strOrderBy = " ORDER BY " & strOrderBy
	End If 'If Trim(strOrderBy) <> "" Then

	'set how many records to get
	If intPageSize > 0 Then
		strTopRecords = " TOP " & intPageSize
	End If 'If intPageSize > 0 Then

	'if not on page one, set the number of records to ignore
	If (intNumPages > 1) And (intPageNumber > 1) Then
		intIgnoreRecs = intPageSize * (intPageNumber - 1)
		strPagedWhere = strPagedWhere & " AND " & strPrimKey & " NOT IN (SELECT TOP " & intIgnoreRecs & " " & strPrimKey & " FROM " & strFrom & " " & strPagedWhere & " " & strOrderBy & ")"
	End If 'If (intNumPages > 1) And (intPageNumber > 1) Then

	'create sql
	strSQL = "SELECT " & strTopRecords & " " & strColumns & " FROM " & strFrom & " " & strPagedWhere & " " & strOrderBy

	'return the sql
	CreatePagedSQL = strSQL
end function 'Function CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)

We now have our SQL statement, so we can now use it to output the selected records.

'Open a command oject for connections to the database
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnectionString

'Get the records from the database
objCommand.CommandText = strSQL
set rsNewsList = objCommand.Execute 

'Loop through the recordset and output the data
while not rsNewsList.eof

	response.Write("<div class=""NewsList"">")
	response.Write("<span class=""Title"">" & rsNewsList("fldTitle") & "</span>")
	response.Write("<span class=""Date"">" & rsNewsList("fldDate") & "</span>")
	response.Write("<span class=""Abstract"">" & rsNewsList("fldAbstract") & "</span>")
	response.Write("</div>")

rsNewsList.movenext
wend

'Output the numbered paging links
response.write(CreatePagingLinks("default.asp?p={PageNo}"))

'empty the recordset
Set rsNewsList = nothing

'close the command object
objCommand.ActiveConnection = nothing
Set objCommand = nothing

And the final step is to output the paging links, by creating the function CreatePagingLinks (called on line 22).

Function CreatePagingLinks(strTempAction)
	strPaging = ""
	strCurAction = ""

	'only display if there is more than one page
	If (intNumPages > 1) Then
		'open the paging
		strPaging = strPaging & "<div class=""Paging"" >"
		strPaging = strPaging & "<span class=""label"" >page:</span>"

		'if not the first page, add the previous link
		If intPageNumber > 1 Then
			strCurAction = Replace(strTempAction, "{PageNo}", intPageNumber - 1)
			strPaging = strPaging & "<a class=""Previous"" href=""" & strCurAction & """ title=""Go to the previous page"" >« Previous</a>"
		End If 'If intPageNumber > 1 Then

		'loop through and add the page numbers
		For i = 1 To intNumPages

			'set current action
			strCurAction = Replace(strTempAction, "{PageNo}", i)

			strPaging = strPaging & "<a href=""" & strCurAction & """ "

			'if this is the current page, mark as active
			If cint(i) = cint(intPageNumber) Then
				strPaging = strPaging & " class=""Active"" "
			End If 'If i = intPageNumber Then

			strPaging = strPaging & " title=""Go to Page " & i & """>" & i & "</a> "
		Next 'For i = 1 To intNumPages

		'if not the last page, show the next link
		If cint(intPageNumber) < cint(intNumPages) Then
			strCurAction = Replace(strTempAction, "{PageNo}", intPageNumber + 1)
			strPaging = strPaging & "<a class=""Next"" href=""" & strCurAction & """ title=""Go to the next page"" >Next »</a>"
		End If 'If cint(intPageNumber) < cint(intNumPages) Then

		'close the paging
		strPaging = strPaging & "<div class=""clear"" ></div>"
		strPaging = strPaging & "</div>"
	End If 'If (intNumPages > 1) Then

	'return paging links
	CreatePagingLinks = strPaging
End Function 'Function CreatePagingLinks(strTempAction)

The Full ASP Code

And thats it, the full code needed to create recordset paging with page numbers, below is the full code:

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Path to your database location\Private\News_Example.mdb"

'Default Paging Values
intTotalRecords = 0	'Total Records
intNumPages = 0		'Number of pages
intPageSize = -1	'Amount per page, -1 means all
intPageNumber = 1	'The current page to display

'News Table Values
strFrom = "Tbl_News"
strPrimKey = "fldNewsID"
strColumns = "fldNewsID, fldTitle, fldAbstract, fldDate"
strWhere = ""
strOrderBy = "fldDate desc"

'Get the current page number from the querystring
intCurPage = 1
if (trim(request("p")) <> "") and (isnumeric(request("p"))) then
	intCurPage = request("p")
end if 'if (trim(request("p")) <> "") and (isnumeric(request("p"))) then

'Calls the function to set the current paging values
SetPagingValues 5, intCurPage, strFrom, strPrimKey, strWhere

'calls the function to get the paged sql
strSQL = CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)

'Open a command oject for connections to the database
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnectionString

'Get the records from the database
objCommand.CommandText = strSQL
set rsNewsList = objCommand.Execute 

'Loop through the recordset and output the data
while not rsNewsList.eof

	response.Write("<div class=""NewsList"">")
	response.Write("<span class=""Title"">" & rsNewsList("fldTitle") & "</span>")
	response.Write("<span class=""Date"">" & rsNewsList("fldDate") & "</span>")
	response.Write("<span class=""Abstract"">" & rsNewsList("fldAbstract") & "</span>")
	response.Write("</div>")

rsNewsList.movenext
wend

'Output the numbered paging links
response.write(CreatePagingLinks("default.asp?p={PageNo}"))

'empty the recordset
Set rsNewsList = nothing

'close the command object
objCommand.ActiveConnection = nothing
Set objCommand = nothing

Sub SetPagingValues(intTempPageSize, intTempPageNumber, strFrom, strPrimKey, strWhere)
	strSQLCount = ""
	sngTempNumPages = 0
	intTempNumPages = 1

	'Set the Page Size (number of records to show per page)
	If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then
		intPageSize = intTempPageSize
	Else 'If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then
		'not valid number, so set to -1
		'IE all records
		intPageSize = -1
	End If 'If (Trim(intTempPageSize) <> "") And (isnumeric(intTempPageSize)) Then

	'Set the page number (the current page being viewed)
	If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then
		intPageNumber = intTempPageNumber
	Else 'If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then
		'if not a valid number, set page number to 1
		intPageNumber = 1
	End If 'If (Trim(intTempPageNumber) <> "") And (isnumeric(intTempPageNumber)) Then

	'SQL to get the total number of records in table
	strSQLCount = "SELECT Count(*) As RecCount From " & strFrom & " WHERE (1=1) "

	'If given add the where clause
	If Trim(strWhere) <> "" Then
		strSQLCount = strSQLCount & " AND " & strWhere
	End If 'If Trim(strWhere) <> "" Then

	'open the sql
	Set objCmd = Server.CreateObject("ADODB.Command")
	objCmd.ActiveConnection = strConnectionString

	objCmd.CommandText = strSQLCount
	set rsRecCount = objCmd.Execute 

	'Set the total records value
	If not rsRecCount.eof Then
		intTotalRecords = rsRecCount("RecCount")
	Else 'If not rsRecCount.eof Then
		intTotalRecords = 0
	End If 'If not rsRecCount.eof Then

	'close the count
	set rsRecCount = nothing

	'close the connection
	objCmd.ActiveConnection = nothing
	Set objCmd = nothing

	'if Total Records and Page size is greater than 0
	'then work out the number of pages
	If (intTotalRecords > 0) And (intPageSize > 0) Then
		sngTempNumPages = intTotalRecords / intPageSize
		intTempNumPages = RoundUp(sngTempNumPages)
	Else 'If (intTotalRecords > 0) And (intPageSize > 0) Then
		intTempNumPages = 1
	End If 'If (intTotalRecords > 0) AND (intPageSize > 0) Then

	'set the num pages
	intNumPages = intTempNumPages

	'if Page number is greater than number of pages, then set to
	'number of pages
	If cint(intPageNumber) > cint(intNumPages) Then
		intPageNumber = intNumPages
	End If 'If intPageNumber > intNumPages Then
End Sub 'Sub SetPagingValues(intTempPageSize, intTempPageNumber, strFrom, strPrimKey, strWhere)

Function CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)
	'Returns paged dataset for supplied SQL, broken down into sections
	strSQL = ""
	strTopRecords = ""
	intIgnoreRecs = 0
	strPagedWhere = " WHERE (1=1) "

	'Create where clause
	If Trim(strWhere) <> "" Then
		strPagedWhere = strPagedWhere & " AND " & strWhere
	End If 'If Trim(strPagedWhere) <> "" Then

	'create Order clause
	If Trim(strOrderBy) <> "" Then
		strOrderBy = " ORDER BY " & strOrderBy
	End If 'If Trim(strOrderBy) <> "" Then

	'set how many records to get
	If intPageSize > 0 Then
		strTopRecords = " TOP " & intPageSize
	End If 'If intPageSize > 0 Then

	'if not on page one, set the number of records to ignore
	If (intNumPages > 1) And (intPageNumber > 1) Then
		intIgnoreRecs = intPageSize * (intPageNumber - 1)
		strPagedWhere = strPagedWhere & " AND " & strPrimKey & " NOT IN (SELECT TOP " & intIgnoreRecs & " " & strPrimKey & " FROM " & strFrom & " " & strPagedWhere & " " & strOrderBy & ")"
	End If 'If (intNumPages > 1) And (intPageNumber > 1) Then

	'create sql
	strSQL = "SELECT " & strTopRecords & " " & strColumns & " FROM " & strFrom & " " & strPagedWhere & " " & strOrderBy

	'return the sql
	CreatePagedSQL = strSQL
end function 'Function CreatePagedSQL(strFrom, strPrimKey, strColumns, strWhere, strOrderBy)

Function CreatePagingLinks(strTempAction)
	strPaging = ""
	strCurAction = ""

	'only display if there is more than one page
	If (intNumPages > 1) Then
		'open the paging
		strPaging = strPaging & "<div class=""Paging"" >"
		strPaging = strPaging & "<span class=""label"" >page:</span>"

		'if not the first page, add the previous link
		If intPageNumber > 1 Then
			strCurAction = Replace(strTempAction, "{PageNo}", intPageNumber - 1)
			strPaging = strPaging & "<a class=""Previous"" href=""" & strCurAction & """ title=""Go to the previous page"" >« Previous</a>"
		End If 'If intPageNumber > 1 Then

		'loop through and add the page numbers
		For i = 1 To intNumPages

			'set current action
			strCurAction = Replace(strTempAction, "{PageNo}", i)

			strPaging = strPaging & "<a href=""" & strCurAction & """ "

			'if this is the current page, mark as active
			If cint(i) = cint(intPageNumber) Then
				strPaging = strPaging & " class=""Active"" "
			End If 'If i = intPageNumber Then

			strPaging = strPaging & " title=""Go to Page " & i & """>" & i & "</a> "
		Next 'For i = 1 To intNumPages

		'if not the last page, show the next link
		If cint(intPageNumber) < cint(intNumPages) Then
			strCurAction = Replace(strTempAction, "{PageNo}", intPageNumber + 1)
			strPaging = strPaging & "<a class=""Next"" href=""" & strCurAction & """ title=""Go to the next page"" >Next »</a>"
		End If 'If cint(intPageNumber) < cint(intNumPages) Then

		'close the paging
		strPaging = strPaging & "<div class=""clear"" ></div>"
		strPaging = strPaging & "</div>"
	End If 'If (intNumPages > 1) Then

	'return paging links
	CreatePagingLinks = strPaging
End Function 'Function CreatePagingLinks(strTempAction)

Function RoundUp(intNum)
	'Rounds up the given number
	If (intNum - Fix(intNum) <> 0) Then
		RoundUp = Fix(intNum) + 1
	Else 'If (intNum - Fix(intNum) <> 0) Then
		RoundUp = intNum
	End If 'If (intNum - Fix(intNum) <> 0) Then
end function 'Function RoundUp(intNum)

Styling

Finally, some CSS just to make the sample easier to visualise.

.NewsList{
	border:1px solid #000000;
	margin-bottom:10px;
	padding:5px;
	width:500px;
}

.NewsList .Title{
	font:bold 12pt Arial, Helvetica, sans-serif;
}

.NewsList .Date{
	font:italic 8pt Arial, Helvetica, sans-serif;
	padding-left:10px;
}

.NewsList .Abstract{
	font:10pt Arial, Helvetica, sans-serif;
	display:block;
}

.Paging .label{
	display:block;
	float:left;
	padding:3px;
	margin:0px 0px 2px 0px;
	color:#999999;
}

.Paging a{
	display:block;
	float:left;
	border:1px solid #999999;
	padding:2px;
	margin:0px 0px 2px 2px;
	text-align:right;
	width:16px;
	text-decoration:none;
	color:#999999;
}

.Paging a:hover{
	border:1px solid #000000;
	color:#000000;
}

.Paging a.Active{
	border:1px solid #000000;
	color:#000000;
}

.Paging a.Previous, .Paging a.Next{
	width:auto;
	padding:2px 4px;
}

Conclusion and Download

As well as giving the user easier controls to navigation through the pages, this code should also put less strain on your database, and also should result in quicker load times for you pages.

Download Demo Files

Any feedback or improvements are welcome.

Leave a Reply

Search AHdevelopment

Can't find what your looking for?? Try Searching


Archive