VB6 ADODB.Recordset RecordCount property always returns -1

Question

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.

Thank you

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

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

Answer 2

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.

Answer 3

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

Answer 4

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.

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

Answer 6

THIS FOLLOWING CODE RETURNS THE RECORDCOUNT 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 '


    ' con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" & sPath & "\usrMaster.mdb"
    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

BY B.ELANGOVAN M.SC.,MPHIL,MTECH., INDIA

[email protected]