使用 RecordCount 属性可确定 Recordset 对象中记录的数目。ADO 无法确定记录数时,或者如果提供者或游标类型不支持 RecordCount,则该属性返回 –1。读已关闭的 Recordset 上的 RecordCount 属性将产生错误。如果 Recordset 对象支持近似定位或书签(即 Supports (adApproxPosition) 或 Supports (adBook) 各自返回 True),不管是否完全填充该值,该值将为 Recordset 中记录的精确数目。如果 Recordset 对象不支持近似定位,该属性可能由于必须对所有记录进行检索和计数以返回精确 RecordCount 值而严重消耗资源。Recordset 对象的游标类型会影响是否能够确定记录的数目。对仅向前游标,RecordCount 属性将返回 -1,对静态或键集游标返回实际计数,对动态游标取决于数据源返回 -1 或实际计数。
Rs.RecordCount
Adodc1.Recordset.RecordCount
该范例使用 Filter 属性打开一个新的 Recordset,它基于适用于已有 Recordset 的指定条件。它使用 RecordCount 属性显示两个 Recordsets 中的记录数。该过程运行时需要 FilterField 函数。Public Sub FilterX() Dim rstPublishers As ADODB.Recordset
Dim rstPublishersCountry As ADODB.Recordset
Dim strCnn As String
Dim intPublisherCount As Integer
Dim strCountry As String
Dim strMessage As String ' 使用出版商表中的数据打开记录集。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "publishers", strCnn, , , adCmdTable ' 充填记录集。
intPublisherCount = rstPublishers.RecordCount ' 让用户输入。
strCountry = Trim(InputBox( _
"Enter a country to filter on:")) If strCountry <> "" Then
' 打开已筛选的记录集对象。
Set rstPublishersCountry = _
FilterField(rstPublishers, "Country", strCountry) If rstPublishersCountry.RecordCount = 0 Then
MsgBox "No publishers from that country."
Else
' 打印原始记录集和已筛选记录集对象的记录数。
strMessage = "Orders in original recordset: " & _
vbCr & intPublisherCount & vbCr & _
"Orders in filtered recordset (Country = '" & _
strCountry & "'): " & vbCr & _
rstPublishersCountry.RecordCount
MsgBox strMessage
End If
rstPublishersCountry.Close End IfEnd Sub
MsgBox "Records: " & Data1.Recordset.RecordCount
实时错误 '-2147217884 (80040e24)'
行集不支持反向取。
在这种时候,有两个办法。
1.
recordcnt=0
rs.movefirst
do while not rs.eof
recordcnt = recordcnt + 1
rs.movenext
loop2.
set rs = con.execute("select count(*) from table")
recorndnt = rs.fields(0).value
MovePrevious和MoveLast会有问题,没法子用。
相信他吧
或许与VB(SP5)有关吧。
to shawls(小山(坚持VB,学Delphi和C#)) :什么是光标怎样避免出现这种情况?
这是由数据库内部进行数据量的计算啊。
这与VB是没有关系的。
只是一句SQL。
返回值不应该是-1的。0 或别的数字还有可能。
rstPublishers.CursorType = adOpenStatic
Dim CON As New Connection
Dim RS As Recordset
CON.ConnectionString = " "
Set RS = CON.Execute("SELECT COUNT(*) FROM TABLE")
RECORDCNT = RS.FIELDS(0)
这样RS.FIELDS(0)的值要么是0,要么就是数据量
Dim Con as New ADODB.Connection
Dim Rs as New ADODB.Recordset
Set Rs=Con.execute("Select distinct(aa),bb From cc Where cc='dd'")
MsgBox Rs.recordcount
Dim Rs as New ADODB.Recordset
rs.CursorType = adOpenStatic
Set Rs=Con.execute("Select distinct(aa),bb From cc Where cc='dd'")
MsgBox Rs.recordcount
一切ok!
Dim Rs as New ADODB.Recordset
rs.CursorType = adOpenStatic
Set Rs=Con.execute("Select distinct(aa),bb From cc Where cc='dd'")
rs.movelast
MsgBox Rs.recordcount
:(
TO lulu999() ,你的方案返回还是-1,但RS中又确实有数据!!!!????我看我只能用baoxiang(包香) 的第一个方案了:(
if not rs.eof then
myaa=rs.getrows()
mycount=ubound(myaa,2)+1
else
mycount=0
end if
mycount 既是数据行数
谢谢你!成功了,没想到只能用这种偏方:(
to baoxiang(包香) 及所有同志:
谢谢大家,现在我觉得这20分远远不够,我会给帖子加分的!
dim rs as adodb.recordset
rs.CursorLocation=adUseClient
rs.Open "select * from abc"
msgbox rs.RecordCount
sql="select count(*) as max from my_table"
rs.open sql
纪录数=max
我遇到过,就是这样解决的!
ado的光标类型:
动态
静态
单项向下
键集!请注意使用资源消耗:
动态>键集>静态>单向向下
记住使用该属性之前一定要先判断记录集是否为 NULL,否则你会死得很难看;
See the detail Pagramter from MSDN please! 给你一个最简单的例子:
'Get the record to display it
Public Function GetPressbyOptn( _
Optional PressID As Long, _
Optional SimpleName As String, _
Optional FullName As String, _
Optional intFullOrNotFlag As Integer = 0, _
Optional intOrderBy As Integer = 1) _
As ADODB.RecordsetOn Error GoTo ErrorProcess
Dim strSql As String
Dim Conn As ADODB.Connection
Dim p_RS As ADODB.Recordset
Dim strWhere(3) As String
Dim strWhereResult As String
Dim i As Integer, iMax As Integer
m_SuccessOrNot = False
SimpleName = Trim(SimpleName)
FullName = Trim(FullName)
strSql = "SELECT * FROM T_Press "
If PressID > 0 Then
strWhere(1) = " ID = " & PressID & " "
End If
If SimpleName <> "" Then
If intFullOrNotFlag = 2 Then
strWhere(2) = " SimpleName= '" & SimpleName & "' "
ElseIf intFullOrNotFlag = 0 Then
strWhere(2) = " SimpleName like '%" & SimpleName & "%' "
End If
End If
If FullName <> "" Then
If intFullOrNotFlag = 3 Then
strWhere(3) = " FullName= '" & FullName & "' "
ElseIf intFullOrNotFlag = 0 Then
strWhere(3) = " FullName like '%" & FullName & "%' "
End If
End If
'' (Composition) WHERE
strWhereResult = " ID <> 0 "
iMax = UBound(strWhere)
For i = 1 To iMax
If (strWhere(i) <> "") Then
strWhereResult = strWhereResult & " AND " & strWhere(i) & " "
End If
Next i
If strWhereResult <> "" Then
strWhereResult = " WHERE " & strWhereResult
strSql = strSql & strWhereResult
End If
Select Case intOrderBy
'' Order By ID
Case 1
strSql = strSql & "Order by ID"
'' Order By SimpleName
Case 2
strSql = strSql & "Order by SimpleName"
'' Order by FullName
Case 3
strSql = strSql & "Order by FullName"
'' the same as 1 -- Order by ID
Case Else
strSql = strSql & "Order by ID "
End Select
'' Initialize ADO connection
Set Conn = New ADODB.Connection
Set p_RS = New ADODB.Recordset
Conn.Open g_StrConn
'' Open the recordset
p_RS.Open strSql, Conn, adOpenStatic
'SQLPublishers, strCnxn, adOpenStatic, , adCmdTable
Set GetPressbyOptn = p_RS
m_SuccessOrNot = True '' Success Flag
Exit Function
ErrorProcess:
Err.Raise Err.Number & Err.Source
Set GetPressbyOptn = Nothing
m_SuccessOrNot = False '' False flag
End Function