VB6 ADODB.Recordset RecordCount属性总是返回-1 [英] VB6 ADODB.Recordset RecordCount property always returns -1

问题描述

我试图获取一些旧的VB6代码以使用SQL Server Compact。

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

我可以连接，打开数据库和一切似乎很好。我可以运行insert select命令工作。

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

但是，ADODB.Recordset RecordCount属性总是返回-1，即使我可以访问字段并查看数据。更改CursorLocation = adUseClient在执行SQL（多步骤操作生成的错误）时会导致问题。

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

任何建议都可以接受。

推荐答案

实际上 CursorLocation 在这种情况下起着主要作用。使用 rs.CursorLocation = adUseClient 设置光标位置并尝试。

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

这篇关于VB6 ADODB.Recordset RecordCount属性总是返回-1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！