Paging certain # of records using Stored Procedure, Command Object and MS-SQL Server

Contributed by aspfree
Rating:

/ 6
August 04, 2001

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Paging certain # of records using
Stored Procedure, Command Object and MS-SQL Server

I recently was putting together some web-based screens and I wanted an easy way to display a certain number of records on each page. I wanted it to be flexible enough I could change it on-the-fly. Well after some hunting through the recordset object there is this nice method called paging. I started with the maxrecords method, tried a few other things and then BOOM, I found the paging method.

Its real easy, in this demo I let the user display different number of records depending on their mood. If you don't do anything with this the default is 10 records. There are 2 pages, one has the dropdown box to select the # of records, the 2nd one displays the information. Below is the code, hope it helps. This example shows using the Command Object, the main reason is the dis-connected recordset model of using Database resources most efficently.

Page 1-The page with the dropdown box.

<html><head>
<title>paging</title></head>
<body>
<form method="post" action="paging.asp" name="form1">
<select name="d1">
<option value="10">10</option>
<option value="7">7</option>
<option value="5">5</option>
</select>
<input type="submit" value="submit" name="b1">
</form>
</body>

Page 2-The page that shows the records

<%@ Language="VBScript"%>

<%
' I'm using a DSN-less connection.

ConnString = "Driver={SQL Server};Description=sqldemo;SERVER=127.0.0.1;UID=sa;PWD=;DATABASE=pubs"

' Declare variables
Dim iPageSize 'How big our pages are
Dim strPageCount 'The number of pages we get back
Dim strPageCurrent 'The page we want to show
Dim strSql 'SQL select to limit fields
Dim conn 'The connection object
Dim rs 'The recordset object
Dim x 'Standard looping var

If request("d1") <> "" Then Session("d1") = request("d1")

'Get parameters from the dropdown box on the previous page.
' You could easily just use the default of 10
iPageSize = Session("d1")
If Request("page") = "" Then
    strPageCurrent = 1
Else
    strPageCurrent = CInt(Request("page"))
End If

'Open the connection string
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring

set cmd = server.CreateObject("adodb.command")

set cmd.ActiveConnection = conn
'set sql statement to a local variable

'This is the Stored proc syntax
'CREATE PROCEDURE spAuthors AS

'select * from authors
'Return
'Go

strSql = "spAuthors"

'adCmdUnspecified -1 Does not specify the command type argument.
'adCmdText 1 Evaluates CommandText as a textual definition of a command or stored procedure call.
'adCmdTable 2 Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
'adCmdStoredProc 4 Evaluates CommandText as a stored procedure name.
'adCmdUnknown 8 Default. Indicates that the type of command in the CommandText property is not known.
'adCmdFile 256 Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
'adCmdTableDirect 512 Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect.
'This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.

With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "spAuthors"
End With

Set rs = Server.CreateObject("ADODB.Recordset")

' Set cursor location and pagesize
rs.CursorLocation = adUseClient
rs.PageSize = iPageSize

' Open Recordset object and
rs.Open cmd

'Disconnect from database
cmd.ActiveConnection = nothing

' Get the count of the pages using the given page size
strPageCount = rs.PageCount

' If the request page falls outside the range,
' give them the closest match (1 or max)
If 1 > strPageCurrent Then strPageCurrent = 1
If strPageCurrent > strPageCount Then strPageCurrent = strPageCount

' Move to the selected page
rs.AbsolutePage = strPageCurrent

' Start output with a page x of n line
Response.Write "<FONT SIZE=""+1"">Page <B>"
Response.Write strPageCurrent
Response.Write "</B> of <B>"
Response.Write strPageCount
Response.Write "</B></FONT><BR><BR>" & vbCrLf

' Continue with a title row in our table
Response.Write "<TABLE BORDER=""1"">" & vbCrLf

' Show field names
Response.Write vbTab & "<TR>" & vbCrLf
For x = 1 To rs.Fields.Count
    Response.Write vbTab & vbTab & "<TD><B>"
    Response.Write rs.Fields(x - 1).Name
    Response.Write "<B></TD>" & vbCrLf
Next
    Response.Write vbTab & "</TR>" & vbCrLf

' Loop through our records
Do While rs.AbsolutePage = strPageCurrent And Not rs.EOF
    Response.Write vbTab & "<TR>" & vbCrLf
    For y = 1 To rs.Fields.Count
        Response.Write vbTab & vbTab & "<TD>"
        Response.Write rs.Fields(y - 1)
        Response.Write "</TD>" & vbCrLf
    Next
        Response.Write vbTab & "</TR>" & vbCrLf

'Move to the next record!
rs.MoveNext
Loop

'Closing html table tag
Response.Write "</TABLE>" & vbCrLf

' Close all objects and clear from Memory
rs.Close
Set rs = Nothing

'Show "previous" and "next" links which navigate between pages

    Response.Write "<A HREF=""./paging3.asp?page=1"
    Response.Write """>First Page</A>"

    Response.Write "   "
If strPageCurrent <> 1 Then
    Response.Write "<A HREF=""./paging3.asp?page="
    Response.Write strPageCurrent - 1
    Response.Write """>Previous Page</A>" & vbCrLf
'Spacer - inside the if so we don't get it unless needed
    Response.Write "  " & vbCrLf
End If
If strPageCurrent < strPageCount Then
    Response.Write "<A HREF=""./paging3.asp?page="
    Response.Write strPageCurrent + 1
    Response.Write """>Next Page</A>" & vbCrLf
End If
    Response.Write "   "
    Response.Write "<A HREF=""./paging3.asp?page="
    Response.Write strPageCount
    Response.Write """>Last Page</A>" & vbCrLf

%>
<html>

<head>
<title>Paging Results page</title>
</head>

<body>
</body>
</html>