Pagination

This document details the pagination feature of the cDataBase class, supporting SQL Server, MySQL, Access and other databases.

---

Table of Contents

• Pagination Overview
• Page Method
• Supported Databases
• Usage Examples
• Performance Optimization
• Common Questions

---

Pagination Overview

What is Pagination

Pagination is the technique of dividing large amounts of data into multiple pages for display, with each page showing a fixed number of records.

Advantages of Pagination

• Performance Optimization - Only query needed data, reduce memory usage
• User Experience - Fast loading, avoid long waits
• Resource Saving - Reduce network transmission and database load

Pagination Principle

Total records: 1000
Items per page: 10
Total pages: 100

Page 1: Records 1-10   (OFFSET 0, LIMIT 10)
Page 2: Records 11-20  (OFFSET 10, LIMIT 10)
Page 3: Records 21-30  (OFFSET 20, LIMIT 10)
...

---

Page Method

Syntax

Function Page(Optional num As Long = 1, Optional Limit As Long = 10) As cDataBase

Parameters

Parameter	Type	Description
num	Long	Page number (optional, default 1)
Limit	Long	Records per page (optional, default 10)

Return Value

Returns cDataBase object, supports chained calls.

Example

' Query page 1, 10 items per page
db.Sql("SELECT * FROM users").Page(1, 10).Query

' Query page 2, 20 items per page
db.Sql("SELECT * FROM users").Page(2, 20).Query

---

Supported Databases

SQL Server (2012+)

Uses OFFSET ... ROWS FETCH NEXT ... ROWS ONLY syntax.

' Original SQL
db.Sql("SELECT * FROM users").Page(2, 10).Query

' Automatically converted to
SELECT * FROM users
ORDER BY (SELECT NULL)
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Note: SQL Server 2012 and above only supports OFFSET FETCH.

MySQL

Uses LIMIT ... OFFSET ... syntax.

' Original SQL
db.Sql("SELECT * FROM users").Page(2, 10).Query

' Automatically converted to
SELECT * FROM users
LIMIT 10 OFFSET 10

Access

Uses TOP and subquery (simplified implementation).

' Original SQL
db.Sql("SELECT * FROM users").Page(2, 10).Query

' Automatically converted to
SELECT TOP 20 * FROM (
    SELECT * FROM users
) AS T

Note: Access pagination is a simplified version and may not be perfectly accurate.

---

Usage Examples

Example 1: Basic Pagination

' Query page 1, 10 items per page
If db.Sql("SELECT * FROM users").Page(1, 10).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

Example 2: Paginated with Sorting

' Descending order by age, paginated display
If db.Sql("SELECT * FROM users ORDER BY age DESC").Page(2, 10).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name") & " - " & db.Rows(i)("age")
    Next
End If

Example 3: Conditional Query Pagination

' Query users older than 18, paginated display
If db.Sql("SELECT * FROM users WHERE age > 18").Page(1, 20).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

Example 4: Get Total Record Count

' Query total count
Dim lTotal As Long
lTotal = db.Count("users")

' Calculate total pages
Dim lPageSize As Long
lPageSize = 10
Dim lTotalPages As Long
lTotalPages = Int((lTotal + lPageSize - 1) / lPageSize)

' Paginated query
If db.Sql("SELECT * FROM users").Page(1, lPageSize).Fetch Then
    ' Display data
End If

Example 5: Complete Pagination Function

' Pagination query function
Function GetUsersPage(lPage As Long, lPageSize As Long) As Collection
    Set GetUsersPage = New Collection

    ' Query data
    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
        Set GetUsersPage = db.Rows
    End If
End Function

' Usage
Dim colUsers As Collection
Set colUsers = GetUsersPage(1, 10)

Example 6: Pagination Navigation

' Pagination navigation class
Private m_lCurrentPage As Long
Private m_lPageSize As Long
Private m_lTotalRecords As Long

Private Sub LoadPage(lPage As Long)
    ' Validate page number
    If lPage < 1 Then lPage = 1

    Dim lTotalPages As Long
    lTotalPages = Int((m_lTotalRecords + m_lPageSize - 1) / m_lPageSize)
    If lPage > lTotalPages Then lPage = lTotalPages

    m_lCurrentPage = lPage

    ' Query data
    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, m_lPageSize).Fetch Then
        ' Display data
        DisplayUsers
    End If
End Sub

Private Sub cmdNextPage_Click()
    LoadPage m_lCurrentPage + 1
End Sub

Private Sub cmdPrevPage_Click()
    LoadPage m_lCurrentPage - 1
End Sub

---

Performance Optimization

1. Use Indexed Fields for Sorting

' Recommended: Use indexed fields for sorting
db.Sql("SELECT * FROM users ORDER BY id").Page(1, 10).Query

' Not recommended: Use non-indexed fields for sorting
db.Sql("SELECT * FROM users ORDER BY name").Page(1, 10).Query

2. Only Query Needed Fields

' Recommended: Only query needed fields
db.Sql("SELECT id, name FROM users").Page(1, 10).Query

' Not recommended: Query all fields
db.Sql("SELECT * FROM users").Page(1, 10).Query

3. Use WHERE Conditions to Limit

' Recommended: Use WHERE conditions
db.Sql("SELECT * FROM users WHERE status = 'active'").Page(1, 10).Query

' Not recommended: Query all data then paginate
db.Sql("SELECT * FROM users").Page(1, 10).Query

4. Set Reasonable Records Per Page

' Recommended: Reasonable records per page (10-50)
db.Sql("SELECT * FROM users").Page(1, 20).Query

' Not recommended: Too many records per page
db.Sql("SELECT * FROM users").Page(1, 1000).Query

---

Common Questions

Q1: SQL Server Pagination Error

Error: Syntax error near 'OFFSET'

Cause: SQL Server version lower than 2012, does not support OFFSET FETCH.

Solution:

• Upgrade to SQL Server 2012 or higher
• Or use ROW_NUMBER() for pagination (requires modifying library code)

Q2: Access Pagination Inaccurate

Cause: Access pagination is a simplified version using TOP query.

Solution:

• For Access, recommend using ROW_NUMBER() or manual pagination
• Or upgrade to SQL Server/MySQL

Q3: How to Get Total Record Count?

' Method 1: Use Count method
Dim lTotal As Long
lTotal = db.Count("users")

' Method 2: Use COUNT(*) query
If db.Sql("SELECT COUNT(*) AS cnt FROM users").Fetch Then
    lTotal = db.Row("cnt")
End If

Q4: How to Maintain Sorting After Pagination?

' Correct: Include ORDER BY in SQL
db.Sql("SELECT * FROM users ORDER BY age DESC").Page(1, 10).Query

' Wrong: Sort after pagination (loses sorting)
db.Sql("SELECT * FROM users").Page(1, 10).Query
' Then sort results (only sorts current page, not globally)

Q5: How to Navigate to Specific Page?

Function GoToPage(lPage As Long, lPageSize As Long) As Boolean
    ' Validate page number
    If lPage < 1 Then lPage = 1

    ' Query specified page
    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
        GoToPage = True
    Else
        GoToPage = False
    End If
End Function

---

Best Practices

1. Always Use ORDER BY

' Recommended: Use ORDER BY to ensure order
db.Sql("SELECT * FROM users ORDER BY id").Page(1, 10).Query

' Not recommended: Without ORDER BY (order is uncertain)
db.Sql("SELECT * FROM users").Page(1, 10).Query

2. Validate Page Number and Records Per Page

' Recommended: Validate parameters
Function GetPage(lPage As Long, lPageSize As Long) As Collection
    If lPage < 1 Then lPage = 1
    If lPageSize < 1 Then lPageSize = 10
    If lPageSize > 100 Then lPageSize = 100  ' Limit max records per page

    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
        Set GetPage = db.Rows
    End If
End Function

3. Cache Total Record Count

' Recommended: Cache total record count to avoid frequent queries
Private m_lCachedTotal As Long
Private m_dtCacheTime As Date

Function GetTotalRecords() As Long
    ' Cache for 5 minutes
    If DateDiff("s", m_dtCacheTime, Now) > 300 Or m_lCachedTotal = 0 Then
        m_lCachedTotal = db.Count("users")
        m_dtCacheTime = Now
    End If
    GetTotalRecords = m_lCachedTotal
End Function

---

Last Updated: 2026-01-21