VB6 ADODB.Recordset RecordCount属性总是返回-1
我想获得一些旧的VB6代码来使用SQL Server Compact。VB6 ADODB.Recordset RecordCount属性总是返回-1
我可以连接，打开数据库，一切似乎都很好。我可以运行插入选择命令的工作。
但是，ADODB.Recordset的RecordCount属性始终返回-1，即使我可以访问这些字段并查看数据。更改CursorLocation = adUseClient会导致执行SQL时出现问题（多步操作生成错误）。
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
任何意见将欣然接受。
这是用于访问数据的游标类型的结果，本文涵盖了问题和可能的修复。
http://www.devx.com/tips/Tip/14143
编辑
我的不是更加关注的是你处理压缩的事实表示道歉。使用Compact时，情况类似于我所引用的情况，因为它默认使用只向前游标（不支持行计数），但还有其他两种游标类型可用，如下面的链接中所述。
替换Set rs = mCmd.Execute有：
set rs = new ADODB.Recordset
rs.Open "select * from myTestTable", mDBConnection, adOpenDynamic, adLockOptimistic
的adOpenDynamic将允许向前/向后通过阅读让你的总记录。
其实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
源自用VB6工作记忆/ ADO很久以前的事，直到你移到记录的末尾.RecordCount场不返回有意义的数据。
rs.MoveLast
rs.MoveFirst
Debug.Print rs.RecordCount
虽然有了这个，你仍然需要确保你有适当的游标类型（即不只是转发）。
我能想到的唯一的其他解决方案是做一个单独的SELECT COUNT（*）FROM myTestTable等等，但是这个调用之间的数据变化以及实际返回行的数据有问题。
With Compact默认的游标属性为adOpenForwardOnly以提高性能。因为这样的RecordCount被返回为“-1”，这意味着它不可用，而不是空白。这是设计的原因，因为动态光标中的记录数量可能会发生变化，并导致客户端服务器之间来回ping以保持准确性。但是，如果记录数很重要，请尝试将其设置为使用adOpenKeyset或adOpenStatic和服务器端游标。
下面的代码完全返回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
这使用Access数据库，而不是SDF数据库。 – Belliez
下面的代码可以帮助你，
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
检查Recordset属性
后续是结果的RecordCount值由com.status.live代码返回
+------------------+-------------------+-------------+---------------+--------------+
| CursorTypeEnum|adOpenForwardOnly=0|dOpenKeyset=1|adOpenDynamic=2|adOpenStatic=3|
|CursorLocationEnum| |
+------------------+-------------------+-------------+---------------+--------------+
|adUseServer = 2 | X | O | X | O |
|adUseClient = 3 | O | O | O | O |
+------------------+-------------------+-------------+---------------+--------------+
这里是你，我用
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
尝试使用下面的代码，如果仍返回-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
你可以尝试这样的解决方案..
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
尝试了这两种方法，都未能执行“select * from myTestTable”。顺便说一句，如果我通过Sql Server Management Studio 2008打开sdf，sql将工作并返回11行。 – Belliez