VB6 ADODB.Recordset RecordCount property always re

问题:

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

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

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).

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.

回答1:

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

    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

回答2:

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.

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

回答3:

Check Recordset Property

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

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

回答4:

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

回答5:

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.

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.

回答6:

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.

回答7:

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

回答8:

Replace Set rs = mCmd.Execute with:

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.

回答9:

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 

回答10:

Try using following code if still returns -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:

This following code returns the recortcount exactly...

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