Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VB6 ADODB.Recordset RecordCount property always returns -1

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.

like image 499
Belliez Avatar asked Feb 17 '10 16:02

Belliez


People also ask

What is Adodb Recordset?

The ADO Recordset object is used to hold a set of records from a database table. A Recordset object consist of records and columns (fields). In ADO, this object is the most important and the one used most often to manipulate data from a database.

What is Recordset vb6?

A Recordset object represents the records in a base table or the records that result from running a query.

What are the methods used for accessing Recordset object?

You can use the MoveFirst, MoveLast, MoveNext, and MovePrevious methods; the Move method; and the AbsolutePosition, AbsolutePage, and Filter properties to reposition the current record, assuming the provider supports the relevant functionality. Forward-only Recordset objects support only the MoveNext method.

Which methods is used to open ADO Recordset?

The default cursor for an ADO Recordset is a forward-only, read-only cursor located on the server. Using the Open method on a Recordset object opens a cursor that represents records from a base table, the results of a query, or a previously saved Recordset.


1 Answers

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.

like image 66
Gareth Wilson Avatar answered Oct 25 '22 18:10

Gareth Wilson