I've picked up some code. The record set is opened as follows...
我已经拿到了一些代码。记录集打开如下......
rs.open sql, db, 3, 3, 1
With the following SQL, the record set's RecordCount property is correct.
使用以下SQL，记录集的RecordCount属性是正确的。
SELECT client.id,
client.NAME,
postcode,
locationx,
locationy,
NULL AS blacklisted,
' ' AS distance
FROM client
LEFT JOIN county
ON client.county = county.id
WHERE hidden = 0
AND client.NAME LIKE '%'
AND ( address LIKE '%%'
OR county.NAME LIKE '%%'
OR postcode LIKE '%%'
OR phone LIKE '%%'
OR fax LIKE '%%' )
ORDER BY client.NAME
Where as with the following beast, the RecordCount property is -1.
与下面的野兽一样，RecordCount属性为-1。
SELECT booking.id,
booking.site,
site.NAME,
booking.client,
client.NAME AS clientname,
booking.confirmed,
Count(appointment.id) AS apps,
Sum(Cast(appointment.confirmed AS INT)) AS conf,
dates.[date] AS startdate,
h.hoursfull,
h.hours,
d.overdue,
e.soon
FROM booking
LEFT OUTER JOIN site
ON booking.site = site.id
LEFT OUTER JOIN dates
ON booking.id = dates.booking
LEFT OUTER JOIN appointment
ON dates.id = appointment.[date]
LEFT OUTER JOIN client
ON booking.client = client.id
LEFT OUTER JOIN
(
SELECT booking,
Sum(dates.hours) AS hours,
Sum(apps1.hourssum) AS hoursfull
FROM dates
LEFT OUTER JOIN
(
SELECT [date],
Sum(hours) AS hourssum
FROM appointment
GROUP BY [date] ) AS apps1
ON dates.id = apps1.[date]
GROUP BY booking ) h
ON booking.id = h.booking
LEFT OUTER JOIN
(
SELECT 1 AS overdue) d
ON dates.[date] <= Getdate()
LEFT OUTER JOIN
(
SELECT 1 AS soon) e
ON dates.[date] <= Dateadd(hh, 48, Getdate())
LEFT OUTER JOIN
(
SELECT DISTINCT a.booking
FROM (
SELECT dates.booking,
Sum(apps2.hourssum) AS filled,
sum(dates.hours) AS hours
FROM dates
LEFT OUTER JOIN
(
SELECT [date],
sum(hours) AS hourssum
FROM appointment
GROUP BY [date] ) AS apps2
ON dates.id = apps2.[date]
GROUP BY dates.booking )a
WHERE (
filled < hours)
OR (
filled IS NULL) )b
ON b.booking = booking.id
WHERE (
booking.hidden = 0)
AND booking.client = 2543
AND booking.confirmed = 1
AND (((
SELECT TOP 1
dates.id
FROM dates
LEFT OUTER JOIN appointment
ON dates.id = appointment.[date]
WHERE (
dates.booking = booking.id)
GROUP BY dates.id,
dates.booking
ORDER BY dates.booking) = dates.id)
OR dates.id IS NULL)
GROUP BY booking.id,
booking.site,
site.NAME,
booking.confirmed,
dates.[date],
booking.client,
client.NAME,
h.hoursfull,
h.hours,
d.overdue,
e.soon
ORDER BY startdate
I can repeat this behaviour by switching the SQL around just before the same call to "open".
我可以通过在相同的“打开”调用之前切换SQL来重复此行为。
2 个解决方案
#1
2
If you are using an ADO recordset, which I suspect you are, you must use a static or keyset cursor type, if you want to access the RecordCount property.
如果您使用的是我怀疑的ADO记录集，则必须使用静态或键集游标类型，如果要访问RecordCount属性。
Take from MSDN
从MSDN获取
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.
Recordset对象的游标类型会影响是否可以确定记录数。对于仅向前游标，RecordCount属性将返回-1;静态或键集游标的实际计数;并且-1或动态游标的实际计数，具体取决于数据源。
EDIT
编辑
It would help if I'd fully read your question! On larger more complex queries it is possible for the RecordCount to return -1, even with the correct cursor type. This can happen for a number of reasons. One example is the recordset is still populating as you start to use it. Try jumping to the final record and then querying the recordcount .
如果我完全阅读你的问题，这将有所帮助！在更大的更复杂的查询上，即使使用正确的游标类型，RecordCount也可以返回-1。这可能由于多种原因而发生。一个例子是当你开始使用它时，记录集仍在填充。尝试跳转到最终记录，然后查询记录计数。
#2
0
I changed my code for opening the record set from...
我更改了我打开记录集的代码...
rs.open sql, db, 3, 3, 1
to...
至...
rs.open sql, db, adOpenStatic, adLockReadOnly, adCmdText
Much gratitude to this site.
非常感谢这个网站。
#1
2
If you are using an ADO recordset, which I suspect you are, you must use a static or keyset cursor type, if you want to access the RecordCount property.
如果您使用的是我怀疑的ADO记录集，则必须使用静态或键集游标类型，如果要访问RecordCount属性。
Take from MSDN
从MSDN获取
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.
Recordset对象的游标类型会影响是否可以确定记录数。对于仅向前游标，RecordCount属性将返回-1;静态或键集游标的实际计数;并且-1或动态游标的实际计数，具体取决于数据源。
EDIT
编辑
It would help if I'd fully read your question! On larger more complex queries it is possible for the RecordCount to return -1, even with the correct cursor type. This can happen for a number of reasons. One example is the recordset is still populating as you start to use it. Try jumping to the final record and then querying the recordcount .
如果我完全阅读你的问题，这将有所帮助！在更大的更复杂的查询上，即使使用正确的游标类型，RecordCount也可以返回-1。这可能由于多种原因而发生。一个例子是当你开始使用它时，记录集仍在填充。尝试跳转到最终记录，然后查询记录计数。
#2
0
I changed my code for opening the record set from...
我更改了我打开记录集的代码...
rs.open sql, db, 3, 3, 1
to...
至...
rs.open sql, db, adOpenStatic, adLockReadOnly, adCmdText
Much gratitude to this site.
非常感谢这个网站。