How do I implement pagination in SQL for MS Access?

Question

I'm accessing a Microsoft Access 2002 database (MDB) using ASP.NET through the OdbcConnection class, which works quite well albeit very slowly.

My question is about how to implement pagination in SQL for queries to this database, as I know I can implement the TOP clause as:

SELECT TOP 15 *
FROM table

but I am unable to find a way to limit this to an offset as can be done with SQL Server using ROWNUMBER. My best attempt was:

SELECT ClientCode,
    (SELECT COUNT(c2.ClientCode)
        FROM tblClient AS c2
        WHERE c2.ClientCode <= c1.ClientCode)
    AS rownumber
FROM tblClient AS c1
WHERE rownumber BETWEEN 0 AND 15

which fails with:

Error Source: Microsoft JET Database Engine

Error Message: No value given for one or more required parameters.

I can't work out this error, but I'm assuming it has something to do with the sub-query that determines a rownumber?

Any help would be appreciated with this; my searches on google have yielded unhelpful results :(

Answer 1

This is the simple method of pagination using OleDbDataAdapter and Datatable classes. I am using a different SQL command for simplicity.

        Dim sSQL As String = "select Name, Id from Customer order by Id"
        Dim pageNumber As Integer = 1
        Dim nTop As Integer = 20
        Dim nSkip As Integer = 0
        Dim bContinue As Boolean = True
        Dim dtData as new Datatable
        Do While bContinue

            dtData = GetData(sSQL, nTop, nSkip, ConnectionString)

            nSkip = pageNumber * nTop
            pageNumber = pageNumber + 1

            bContinue = dtData.Rows.Count > 0
            If bContinue Then
                For Each dr As DataRow In dtData.Rows
                    'do your work here
                Next
            End If
        Loop

Here is the GetData Function.

    Private Function GetData(ByVal sql As String, ByVal RecordsToFetch As Integer, ByVal StartFrom As Integer, ByVal BackEndTableConnection As String) As DataTable
    Dim dtResult As New DataTable
    Try
        Using conn As New OleDb.OleDbConnection(BackEndTableConnection)
            conn.Open()
            Using cmd As New OleDb.OleDbCommand
                cmd.Connection = conn
                cmd.CommandText = sql
                Using da As New OleDb.OleDbDataAdapter(cmd)
                    If RecordsToFetch > 0 Then
                        da.Fill(StartFrom, RecordsToFetch, dtResult)
                    Else
                        da.Fill(dtResult)
                    End If
                End Using
            End Using
        End Using
    Catch ex As Exception
    End Try
    Return dtResult
End Function

The above codes will return 10 rows from the table Customer each time the loop operate till the end of file.