ADODB.Recordset AbsolutePage不能与Oracle数据库（ASP.net）一起使用

I have the following code:

我有以下代码：

Dim PageNum AS Integer = 1
Dim ThePageSize As Integer = 30
Dim RowCT As Integer = 0

Dim SqlStr As String = "SELECT * FROM TheTable"
Dim TCConStr As String = ConfigurationManager.ConnectionStrings("TCConStr").ConnectionString

Dim Objrs As New ADODB.Recordset

Objrs.Open(SqlStr, TCConStr, 3, 3)

If Not Objrs.EOF Then
  Objrs.MoveFirst()
  Objrs.PageSize = ThePageSize
  Dim TotalPages As Integer = Objrs.PageCount
  Objrs.AbsolutePage = PageNum
End If

While Not Objrs.EOF And RowCT < Objrs.PageSize
  RowCT = RowCT + 1

  'Do Stuff

  Objrs.MoveNext()
End While
Objrs.Close()
Objrs = Nothing

When I try to run it I get: "System.Runtime.InteropServices.COMException: Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype." The error page points to "Objrs.AbsolutePage = PageNum"

当我尝试运行它时，我得到：“System.Runtime.InteropServices.COMException：Current Recordset不支持书签。这可能是提供者或所选cursortype的限制。”错误页面指向“Objrs.AbsolutePage = PageNum”

Any ideas of if I have done anything wrong here, or what I can do to get around it? It would kind of really stink to show 500 records on one page.

如果我在这里做错了什么，或者我能做些什么来绕过它？在一个页面上显示500条记录真的很臭。

1 个解决方案

#1

My somewhat naïve guess, given that I know very little about ADODB, is that the Oracle provider for ADODB doesn't support paging. The documentation for the AbsolutePage property does indeed suggest this:

考虑到我对ADODB知之甚少，我有点天真的猜测是ADODB的Oracle提供程序不支持分页。 AbsolutePage属性的文档确实建议：

The provider must support the appropriate functionality for this property to be available.

提供程序必须支持此属性的适当功能。

So, instead of doing the paging in your ADO and VB.NET, you could do the paging in your Oracle query.

因此，您可以在Oracle查询中进行分页，而不是在ADO和VB.NET中进行分页。

The typical way of doing paging with Oracle is to use a query like the following. This returns the 31st to the 40th rows of the table some_table, which would be for the 4th page of 10-row pages:

使用Oracle进行分页的典型方法是使用如下查询。这将返回表some_table的第31行到第40行，这将是10行页面的第4页：

SELECT a, b, c
  FROM (SELECT a, b, c, ROWNUM as num
          FROM some_table
         WHERE ROWNUM <= 40)
 WHERE num > 30

The two numbers in this query should be PageNum * ThePageSize and (PageNum - 1) * ThePageSize in that order.

此查询中的两个数字应该是PageNum * ThePageSize和（PageNum - 1）* ThePageSize。

Note that if the query you're attempting to page has an ORDER BY clause or a GROUP BY clause, you'll need to move that into another subquery. For example, if we needed to sort by column b and then page the results, we'd use something like:

请注意，如果您尝试寻呼的查询具有ORDER BY子句或GROUP BY子句，则需要将其移动到另一个子查询中。例如，如果我们需要按列b排序然后分页结果，我们会使用如下内容：

SELECT a, b, c
  FROM (SELECT a, b, c, ROWNUM as num
          FROM (SELECT a, b, c 
                  FROM some_table
                 ORDER BY b)
         WHERE ROWNUM <= 40)
 WHERE num > 30

#1