Tuesday, March 27, 2012

Count in Empty RecordSet and BOF/EOF

Count has been addressed many times but, to my knowledge, the association between "Count" on an empty record set and BOF/EOF has not been discussed. There are a few comparisons to review here, thank you for your help and patience.

My question: When Count is used in a query will BOF and EOF always be FALSE even if the recordset is empty (see Method 1 example)? (Do aggregate functions always return a BOF and EOF set to FALSE?)

Also, is it better to use Method 2, or Method 3 listed below instead of Method 1?

Method 1 example:
Select Count(bill_no ) from bill_table where budget_id = '297333'

(ASP Web page):
'Will BOF and EOF always return FALSE for Count - regardless if the count is zero or not?
If objRS.BOF AND objRS.EOF Then
'Empty Record set
Else
'Record set is not empty
End If

Method 2 example:
Select Count(bill_no ) from bill_table where budget_id = '297333'

(ASP Web page):
'Is this safe or will -1 be returned in some instances even if there may be records that match?
If Cint(objRS(0)) > 0 Then
'Record set is not empty
Else
'Empty Record set
End If

Method 3:
'Uses RecordCount - (but this errors for me: returns -1 (I suspect wrong cursor))
Select bill_no from bill_table where budget_id = '297333'

(ASP Web page)
If objRS.BOF AND objRS.EOF Then
'Empty record set
Else
' not empty
intNumBills = objRS.RecordCountAny SQL statement that uses an aggregate (any aggregate) has to return rows. Even if zero rows were counted, the count itself is still returned as an aggregate row. This implies that Method 1 isn't going to work, but that Method 2 is guaranteed to work.

The value -1 in a recordcount has special meanings for various kinds of recordsets. See the RecordCount (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp) entry in the ADO documentation.

-PatP

No comments:

Post a Comment