Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: 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.RecordCount

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Any 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 entry in the ADO documentation.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •