ADODB Recordset.RecordCount提供错误的答案 [英] ADODB Recordset.RecordCount giving incorrect answer

问题描述

我有一个问题，我的Recordset实际上有1164条记录时返回-1作为RecordCount。

I have an issue with my Recordset returning -1 as the RecordCount when in fact there are 1164 records.

我检查了查询，这很好。即使它告诉我有-1条记录， CopyFromRecordset 方法仍然有效并粘贴正确的结果。在使用 rs.RecordCount 之前从未遇到过问题？

I have checked my query which is fine. Even though it tells me there are -1 records the CopyFromRecordset method still works and pastes the correct results. Never had an issue before using the rs.RecordCount?

Dim strSQL As String
Dim rs As New ADODB.Recordset

If cust = "JPM" Then
    port = "RP L99"
Else
    port = "RP V10"
End If

strSQL = "my select query"

rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
GetCompanies = rs.RecordCount
If GetCompanies > 0 Then
   wsLive.Range("A" & ROWCOMPANYSTART).CopyFromRecordset rs
   wsLive.Range("C" & ROWCOMPANYSTART & ":C" & GetCompanies + ROWCOMPANYSTART).NumberFormat = "0.00%"
End If
CloseRecordset rs

End Function

推荐答案

要获取实际计数，您需要先将光标移到最后一条记录，然后再移回开始。但是，请记住，对于大型数据集，来回移动将是一项低效的任务。因此，我建议谨慎使用以下内容。

To get the actual count, you need to first move the cursor to the last record then move back to the beginning. However, remember for a large dataset, the moving back and forth will be an inefficient task. So, I would suggest the following with caution.

    Dim strSQL As String
    Dim rs As New ADODB.Recordset

    If cust = "JPM" Then
        port = "RP L99"
    Else
        port = "RP V10"
    End If

    strSQL = "my select query"

    rs.CursorType = adOpenDynamic

    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

    If Not (rs.BOF And rs.EOF) Then
        rs.MoveLast
        rs.MoveFirst
        GetCompanies = rs.RecordCount
        wsLive.Range("A" & ROWCOMPANYSTART).CopyFromRecordset rs
        wsLive.Range("C" & ROWCOMPANYSTART & ":C" & GetCompanies + ROWCOMPANYSTART).NumberFormat = "0.00%"      
    'Else
        'MsgBox "No Records !!"
    End If

    CloseRecordset rs
End Function

这篇关于ADODB Recordset.RecordCount提供错误的答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！