Member Avatar for phid2002

Can someone take a look at the code to see where is the mistake because i can't display the 10 records of my database per page

<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
 <style>
  body { font-family : Verdana; font-size : 8pt; }
  a { font-family : Verdana; font-size : 8pt;
   text-decoration : none; }
 </style>
</head>

<body>
<%

dim conn,x
set conn=Server.CreateObject("ADODB.Connection")
conn.open "sql"
 Dim rs
  Set rs = Server.CreateObject("ADODB.Recordset")

  rs.PageSize = 10
  rs.CacheSize = 5
  rs.CursorLocation = adUseClient

 rs.Open "select * from reservations", conn
 
 If Len(Request("pagenum")) = 0  Then
  rs.AbsolutePage = 1
 Else
  If CInt(Request("pagenum")) <= rs.PageCount Then
   rs.AbsolutePage = Request("pagenum")
  Else
   rs.AbsolutePage = 1
  End If
 End If

 Dim abspage, pagecnt
  abspage = rs.AbsolutePage
  pagecnt = rs.PageCount

If Not rs.EOF Then
  Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
  Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf

  Response.Write "Total number of records : " & rs.RecordCount
  Response.Write "<br><br>" & vbcrlf

  
  end if

 Dim fldF, intRec

  Response.Write "<table border=1 align=center cellpadding=3 "
  Response.Write "cellspacing=0><thead><tr>"
  For Each fldF in rs.Fields
   Response.Write "<td>" & fldF.Name & "</td>"
  Next
  Response.Write "</tr></thead><tbody>"

  For intRec="1" To rs.PageSize
   If Not rs.EOF Then
    Response.Write "<tr>"
    For Each fldF in rs.Fields
     Response.Write ("<td>" & fldF.Value & "</td>")
    Next
    Response.Write "<tr>"
    rs.MoveNext
   End If
  Next
  Response.Write "</tbody></table><p>"


%>
  • 1 Contributor
  • 1 Reply
  • 128 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by phid2002

Dani AI

Generated

Note (after 's "i sorted out"): similar pagination problems in classic ASP/ADO usually come from cursor/lock settings, cache size, HTML markup, or relying on server-side cursors that don't expose PageCount/RecordCount. The snippet in the first post shows a client-side approach; the key checks below explain why a page might not show 10 rows even when PageSize is set.

Common checks and quick fixes

  • Make CursorLocation = adUseClient before opening the Recordset and use a cursor type that supports paging (client-side static cursor, e.g. adOpenStatic). Server-side forward-only cursors often return RecordCount = -1 and won't support PageCount/AbsolutePage.
  • Set PageSize = 10 and set CacheSize to the same value (or larger). A CacheSize smaller than the page size can cause unexpected behavior.
  • Validate the incoming page number as an integer and clamp it between 1 and rs.PageCount to avoid out-of-range AbsolutePage values.
  • Check loop and HTML output: iterate a numeric loop For i = 1 To rs.PageSize and inside the loop check If Not rs.EOF Then ... rs.MoveNext. Also ensure table rows are closed with </tr> (an accidental "<tr>" instead of "</tr>" will break output and make it look like rows are missing).
  • If rs.RecordCount or rs.PageCount remains invalid after these fixes, fall back to database-side paging.

Database-side paging (recommended for large tables)

SQL Server 2012+ (OFFSET/FETCH):

SELECT *
FROM reservations
ORDER BY id
OFFSET (@PageNum-1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

SQL Server 2005+ (ROW_NUMBER):

WITH Ordered AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM reservations
)
SELECT *
FROM Ordered
WHERE rn BETWEEN ((@PageNum-1)*@PageSize)+1 AND (@PageNum*@PageSize);

Using parameterized ADODB.Command to pass page number and page size is safer and more efficient than pulling the whole table into ADO. See Microsoft docs for OFFSET/FETCH and ROW_NUMBER for details: ORDER BY / OFFSET-FETCH and ROW_NUMBER.

Member Avatar for phid2002

i sorted out

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.