INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Access

Paging recordset in ASP by tty0
Posted: 25 Feb 03 (Edited 20 Jul 06)

The following code is as well commented as I can, if there are any questions please feel free to email me.

To use this page by copy and paste it needs to be named search.asp and it is called from an html form that passes a value called Query in the querystring. This is searched for in the first field in the database.

*****************************
<%@language=vbscript%>
<%option explicit

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
dim strSQL, objRS, I, records
Dim objConnection
Dim abspage, pagecnt

'open your connection to the database
objConnection = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot;
objConnection = objConnection & Server.MapPath(&quot;\database.mdb&quot;)

'store the record number in a variable as you will refer to this
'a few times later on
records = request.querystring(&quot;records&quot;)

set objRS = server.CreateObject(&quot;ADODB.Recordset&quot;)
objRS.PageSize = 6      'this is the amount of records you ned on a page
objRS.CacheSize = 300
objRS.CursorLocation = adUseClient

'set the SQL query
strSQL = &quot;SELECT * FROM table WHERE field1='&quot;
strSQL = strSQL & request.querystring(&quot;Query&quot;) & &quot;' order by field1&quot;

'open the recordset
objRS.Open strSQL, objConnection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
%>

<html>
<head>
<title>Your Search Results</title>
</head>
<body>
<table width=&quot;600&quot; border=&quot;0&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot; align=&quot;center&quot;>
  <tr>
    <td>
      <%
      if objRS.EOF and objRS.BOF then
        response.write(&quot;Your search has returned no results, &quot;)
        response.write(&quot; please re search the database using a different criteria.&quot;)
      else
'***************************************
'start the loop in the recordset here
'***************************************
        If Len(request.querystring(&quot;records&quot;)) = 0 then
          objRS.AbsolutePage = 1
        Else
          If CInt(request.querystring(&quot;records&quot;)) <= objRS.PageCount then
            objRS.AbsolutePage = request.querystring(&quot;records&quot;)
          Else
            objRS.AbsolutePage = 1
          End if
        End if
        abspage = objRS.AbsolutePage
        pagecnt = objRS.PageCount
'***************************************
'set this up to mirror what you are wanting in the display
'***************************************
        %>
        <TABLE WIDTH='100%' cellpadding=2 cellspacing=2 border=1>
        <%
        For I = 1 to objRS.PageSize
          If Not objRS.EOF Then
        <TR>
            'write the table data onto the page with the recordset details.%>
              <TD>
                <%=objRS(&quot;field1&quot;)%><BR>
                <%=objRS(&quot;field2&quot;)%><BR>
                <%=objRS(&quot;field3&quot;)%><BR>
                <%=objRS(&quot;field4&quot;)%><BR>
                <%=objRS(&quot;field5&quot;)%><BR>
                <%=objRS(&quot;field6&quot;)%><BR>
              </TD>
            </TR>
            <%
            objRS.movenext
          end if
        Next                       
'***************************************
'end the loop in the recordset here and close the objects
'***************************************
        %>
        </table>
        <%
      end if
    objRS.close
    set objRS = nothing
    %>
   </td>
  </tr>
  <tr>
    <td>
      <div align=&quot;right&quot;>
      <%
'********************************************************
'put the navigation links on the bottom of the page here
'********************************************************
      Response.Write &quot;<div align='right'>&quot; & vbcrlf
      Response.Write &quot;<a href='&quot;
      Response.Write &quot;search.asp&quot;
      Response.Write &quot;?Query=&quot; & request.querystring(&quot;Query&quot;) & _
      &quot;'>First Page</a>&quot;
      Response.Write &quot; | &quot;
      If abspage = 1 Then
        Response.Write &quot;<span style='color:silver;'>Previous Page</span>&quot;
      Else
        Response.Write &quot;<a href='&quot; & Request.ServerVariables(&quot;SCRIPT_NAME&quot;)
        Response.Write &quot;?records=&quot; & abspage - 1 & &quot;&ListAll=&quot; & _
        request.querystring(&quot;ListAll&quot;)& &quot;&Town=&quot; & request.querystring(&quot;Town&quot;) & _
        &quot;&County=&quot; & request.querystring(&quot;County&quot;) & &quot;'>Previous Page</a>&quot;
      End If
      Response.Write &quot; | &quot;
      If abspage < pagecnt Then
        Response.Write &quot;<a href='&quot; & Request.ServerVariables(&quot;SCRIPT_NAME&quot;)
        Response.Write &quot;?Query=&quot; & request.querystring(&quot;Query&quot;) & _
        &quot;&records=&quot; & abspage + 1 & &quot;'>Next Page</a>&quot;
      Else
        Response.Write &quot;<span style='color:silver;'>Next Page</span>&quot;
      End If
      Response.Write &quot; | &quot;
      Response.Write &quot;<a href='&quot; & Request.ServerVariables(&quot;SCRIPT_NAME&quot;)
      Response.Write &quot;?Query=&quot; & request.querystring(&quot;Query&quot;) & _
      &quot;&records=&quot; & pagecnt & &quot;'>Last Page</a>&quot;
      Response.Write &quot;</div>&quot;
'********************************************************
'end the navigation links on the bottom of the page here
'********************************************************
      %>
      </div>
    </td>
  </tr>
</table>
</body>
</HTML>
****************************************

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close