VB6 ADODB。Recordset RecordCount属性总是返回-1。

I am trying to get some old VB6 code to work with SQL Server Compact.

我正在尝试使用一些旧的VB6代码来使用SQL Server Compact。

I can connect, open the database and all seems well. I can run insert select commands which work.

我可以连接、打开数据库，一切看起来都很好。我可以运行insert select命令。

However the ADODB.Recordset RecordCount property always returns -1 even though I can access the Fields and see the data. Changing the CursorLocation = adUseClient causes a problem when executung the SQL (multiple-step operation generated errors).

然而ADODB。记录集记录计数属性总是返回-1，即使我可以访问字段并查看数据。当执行SQL时，更改CursorLocation = adUseClient会导致问题(多步操作会产生错误)。

Option Explicit
    Private Const mSqlProvider          As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;"
    Private Const mSqlHost              As String = "Data Source=C:\Database.sdf;"
    Private mCmd                        As ADODB.Command   ' For executing SQL
    Private mDbConnection               As ADODB.Connection


Private Sub Command1_Click()


   Dim rs As ADODB.Recordset

    Set rs = New ADODB.Recordset


    Dim DbConnectionString As String

    DbConnectionString = mSqlProvider & _
                            mSqlHost


    Set mDbConnection = New ADODB.Connection
    mDbConnection.CursorLocation = adUseServer

    Call mDbConnection.Open(DbConnectionString)

    If mDbConnection.State = adStateOpen Then
        Debug.Print (" Database is open")
        ' Initialise the command object
        Set mCmd = New ADODB.Command
        mCmd.ActiveConnection = mDbConnection

        mCmd.CommandText = "select * from myTestTable"
        mCmd.CommandType = adCmdText

        Set rs = mCmd.Execute

        Debug.Print rs.RecordCount  ' Always returns -1  !!
        Debug.Print rs.Fields(0)   ' returns correct data for first row, first col
        Debug.Print rs.Fields(1)   ' returns correct data for first row, 2nd col
        Debug.Print rs.Fields(2)   ' returns correct data for first row, 3rd col

    End If

End Sub

Any advice would be gratefully accepted.

任何建议都会被感激地接受。

11 个解决方案

#1

That's a result of the type of cursor used to access the data, this post covers the issue and possible fixes.

这是用于访问数据的游标类型的结果，本文将介绍问题和可能的修复。

http://www.devx.com/tips/Tip/14143

EDIT

编辑

I apologize for not being more attentive to the fact that you were dealing with Compact. With Compact the situation is similar to the one I referenced, as it uses forward only cursors by default (which do not support row count) but there are two other cursor types available as documented in the link below.

我很抱歉没注意到你在处理契约。使用Compact，情况与我引用的情况类似，因为它默认只使用forward游标(不支持行计数)，但是下面的链接中有另外两种游标类型。

http://support.microsoft.com/kb/272067

#2

Actually the CursorLocation plays a major role in this case. Use rs.CursorLocation = adUseClient to set the cursor location and try.

实际上，CursorLocation在这个案例中扮演了重要角色。使用rs.CursorLocation = adUseClient设置光标位置并尝试。

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    Dim DbConnectionString As String

    DbConnectionString = mSqlProvider & _
                            mSqlHost


    Set mDbConnection = New ADODB.Connection
    mDbConnection.CursorLocation = adUseServer

    Call mDbConnection.Open(DbConnectionString)

    If mDbConnection.State = adStateOpen Then
        Debug.Print (" Database is open")
        ' Initialise the command object
        Set mCmd = New ADODB.Command
        mCmd.ActiveConnection = mDbConnection

        mCmd.CommandText = "select * from myTestTable"
        mCmd.CommandType = adCmdText

        Set rs = mCmd.Execute

        Debug.Print rs.RecordCount  ' This should now return the right value.
        Debug.Print rs.Fields(0)   ' returns correct data for first row, first col
        Debug.Print rs.Fields(1)   ' returns correct data for first row, 2nd col
        Debug.Print rs.Fields(2)   ' returns correct data for first row, 3rd col

    End If

End Sub

#3

Check Recordset Property

检查记录集属性

The follow is result that RecordCount value returned by com.status.live code

下面是由com.status返回的记录计数值的结果。生活的代码

+------------------+-------------------+-------------+---------------+--------------+
|    CursorTypeEnum|adOpenForwardOnly=0|dOpenKeyset=1|adOpenDynamic=2|adOpenStatic=3|
|CursorLocationEnum|                                                                |
+------------------+-------------------+-------------+---------------+--------------+
|adUseServer = 2   |         X         |      O      |       X       |       O      |
|adUseClient = 3   |         O         |      O      |       O       |       O      |
+------------------+-------------------+-------------+---------------+--------------+

#4

You may try something like this..

你可以试试这样的。

Set rs = mCmd.Execute

rs.MoveFirst

Do Until rs.EOF = true

    Debug.Print rs.RecordCount  ' Always returns -1  !!
    Debug.Print rs.Fields(0)   ' returns correct data for first row, first col
    Debug.Print rs.Fields(1)   ' returns correct data for first row, 2nd col
    Debug.Print rs.Fields(2)   ' returns correct data for first row, 3rd col

   counter = counter + 1
   rs.MoveNext

Loop

#5

With Compact the default cursor attribute is adOpenForwardOnly for improved performance. As such RecordCount is returned as "-1" which means its not available, rather than blank. This is by design because the # of records in a dynamic cursor could change and result in pinging back and forth between the client server to maintain accuracy. However, if the record count is vital try setting it to use adOpenKeyset or adOpenStatic with a server-side cursor.

使用Compact，默认的游标属性将只用于提高性能。这样的记录计数被返回为“-1”，这意味着它不可用，而不是空白。这是通过设计来实现的，因为动态游标中的记录的#可以更改并导致在客户端服务器之间来回切换以保持准确性。但是，如果记录计数很重要，请尝试将其设置为使用adOpenKeyset或使用一个服务器端游标的adOpenStatic。

#6

Below code might help you,

下面的代码可能会帮助您，

set conn = CreateObject("ADODB.Connection") 
conn.open "<connection string>" 
set rs = CreateObject("ADODB.Recordset") 
sql = "SELECT columns FROM table WHERE [...]" 
rs.open sql,conn,1,1 
if not rs.eof then 
    nr = rs.recordcount 
    response.write "There were " & nr & " matches." 
    ' ... process real results here ... 
else 
    response.write "No matches." 
end if 
rs.close: set rs = nothing 
conn.close: set conn = nothing

#7

Here is a solution for you that I used

这里有一个我用过的方法。

Dim recordnumber As Long
Dim SalRSrec As New ADODB.Recordset
Set SalRSrec = Nothing
SalRSrec.Open ("SELECT count(*) from SALARY where EMPID= '" & cmb_empid & "' ;"), Dbase, adOpenKeyset, adLockOptimistic
recordnumber = SalRSrec.GetString
MsgBox recordnumber

#8

From memory with working with VB6/ADO a long time ago the .RecordCount field doesn't return meaningful data until you've moved to the end of the recordset.

从内存中使用VB6/ADO很久以前，. recordcount字段在您移动到记录集的末尾之前不会返回有意义的数据。

rs.MoveLast
rs.MoveFirst
Debug.Print rs.RecordCount

Though with this you'll need to make sure you have the appropriate cursor type (i.e., not forward only).

尽管如此，您需要确保您有合适的游标类型(例如。,而不是仅向前型)。

The only other solution I can think of is to do a separate SELECT COUNT(*) FROM myTestTable, etc but this has issues with the data changing between that call, and the one that actually returns the rows.

我能想到的唯一的另一个解决方案是从myTestTable中执行一个单独的SELECT COUNT(*)，等等，但是这个解决方案在调用和实际返回行之间的数据变化上有问题。

#9

Replace Set rs = mCmd.Execute with:

替换集合rs = mCmd。执行:

set rs = new ADODB.Recordset
rs.Open "select * from myTestTable", mDBConnection, adOpenDynamic, adLockOptimistic

The adOpenDynamic will allow a forward/backward read through to get your recordcount.

adOpenDynamic允许向前/向后读取数据以获取记录计数。

#10

Try using following code if still returns -1

如果仍然返回-1，请尝试使用以下代码

Set Conn = createobject("ADODB.connection")
Set Rs = createobject("ADODB.recordset")
Conn.Open "DSN=DSN_QTP" 
'Rs.Open "Select * From orders",Conn,adOpenDynamic,adLockBatchOptimistic
Rs.Open "Select * from [QTP-Table]",Conn,1 'Use either 1 or 3
'I tried using adopendynamic but it still returned -1. Using 1 it gave me correct count.       'Though I am using this code in QTP (Vbscript) same should work for VB6 also.
msgbox Rs.RecordCount

#11

-1

This following code returns the recortcount exactly...

下面的代码将正确地返回recortcount…

Public Sub test()
    Dim cn As New ADODB.Connection()
    Dim sPath As String = Application.ExecutablePath
    sPath = System.IO.Path.GetDirectoryName(sPath)

    If sPath.EndsWith("\bin") Then
        sPath = sPath.Substring(0, Len(sPath) - 4)
    End If

    Dim DbConnectionString As String
    DbConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & sPath & "\students.mdb"

    cn.ConnectionString = DbConnectionString
    cn.Open()

    Dim rs As New ADODB.Recordset()
    rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
    rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
    rs.Open("select * from students", cn)
    MsgBox(rs.RecordCount)

    rs.ActiveConnection = Nothing
    cn.Close()
End Sub

#1