为什么对于新的非空记录集，bof/eof之一偶尔会为真 [英] Why extremely occasionally will one of bof/eof be true for a new non-empty recordset

问题描述

 set recordsetname = databasename.openrecordset(SQLString)
    if recordsetname.bof <> true and recordsetname.eof <> true then
    'do something
    end if

2个问题:

1. 以上测试可以错误地评估为假，但极少发生 (我的代码中潜伏着一个，今天却失败了，我相信这是5年内每天使用的第一次，这就是我发现它的方式).为什么对于非空记录集，bof/eof之一偶尔会为真?似乎如此罕见，我不知道为什么会这样.

1. the above test can evaluate to false incorrectly but only extremely rarely (I've had one lurking in my code and it failed today, I believe for the first time in 5 years of daily use-that's how I found it). Why very occasionally will one of bof/eof be true for a non-empty recordset. It seems so rare that I wonder why it occurs at all.

这是一个万无一失的替代品吗?

Is this a foolproof replacement:

if recordsetname.bof <> true or recordsetname.eof <> true then

编辑以添加代码的详细信息:

客户有订单，每个订单都以BeginOrder项开始，以EndOrder项结束，并且中间是订单中的项.

Customers have orders, each order begins with a BeginOrder item and end with an EndOrder item and in between are the items in the order.

SQL是:

' ids are autoincrement long integers '
SQLString = "select * from Orders where type = OrderBegin or type = OrderEnd"           

Dim OrderOpen as Boolean
OrderOpen = False

Set rs = db.Openrecordset(SQLString)
If rs.bof <> True And rs.eof <> True Then
    myrec.movelast
    If rs.fields("type").value = BeginOrder Then
         OrderOpen = True
    End If
End If

If OrderOpen F False Then
    'code here to add new BeginOrder Item to Orders table '
End If

ShowOrderHistory 'displays the customer's Order history '

在这种情况下，看起来是这样

In this case which looks this this

BeginOrder
Item a
Item b
...
Item n
EndOrder

BeginOrder
Item a
Item b
...
Item n
EndOrder

BeginOrder
Item a
item b
...
Item m

BeginOrder     <----should not be there as previous order still open

推荐答案

文档明确指出，如果打开没有记录的Recordset:

The documentation clearly states that, if you open a Recordset that has no records:

• BOF将为真
• EOF将为真
• RecordCount将是0

• BOF will be true
• EOF will be true
• RecordCount will be 0

对于非空的Recordset，除非您移出第一条或最后一条记录，否则BOF和EOF都不正确.

For a non-empty Recordset, neither BOF and EOF are true until you move beyond the first or last record.

是否可能是不时有人在刚刚打开并更改结果集的记录集中的一个表中添加/删除了一条记录?
可能是比赛条件造成的.

Could it be that, from time to time, someone else could have added/deleted a record to one of the tables in the recordset you're just opening and change the resultset?
It could be the result of a race condition.

您可以在Recordcount上进行测试，而不必使用BOF或EOF:如果记录集为空，则始终为0. 如果记录集不为空，则通常在记录集打开后立即返回1；否则，记录集将返回1.在这种情况下，Recordcount并不是昂贵的操作.
真正返回实际数量的记录的唯一方法是在调用Recordcount强制加载所有记录之前发出MoveLast.

Rather than use BOF or EOF, you can test on Recordcount: it's always 0 if the recordset is empty.
If the recordset is not empty, it will usually return 1 right after the recordset has been open; Recordcount isn't an expensive operation in that case.
The only way to really return the actual number of records is to issue a MoveLast before calling Recordcount to force all records to be loaded.

通常，如果我需要以只读方式遍历结果集:

Usually, if I need to iterate through a resultset in read-only fashion:

Dim db as DAO.Database
Dim rs as DAO.RecordSet

Set db = CurrentDB()
Set rs = db.OpenRecordSet("...", dbOpenForwardOnly)
If Not (rs Is Nothing) Then
    With rs
       Do While Not .EOF
            ' Do stuff '
            .MoveNext
       Loop
       .Close
    End With
    Set rs = Nothing
End If
Set db = Nothing

如果我不需要遍历记录，而只需测试是否返回了任何内容:

If I don't need to iterate through records but just test if anything was returned:

Set rs = db.OpenRecordSet("...", dbOpenForwardOnly)
If Not (rs Is Nothing) Then
    With rs
       If .RecordCount > 0 Then
          ' We have a result '
       Else
          ' Empty resultset '
       End If
       .Close
    End With
    Set rs = Nothing
End If
Set db = Nothing

这非常具有防御性，您必须适应自己的情况，但是每次都能正常工作.

It's pretty defensive and you have to adapt to your circumstances, but it works correctly every time.

关于您的第二个问题，尽管我会自己使用Recordcount，但是在打开记录集后进行测试(BOF或EOF)应该比And版本更加安全.

Regarding your 2nd question, testing (BOF Or EOF) after opening the recordset should be more foolproof than the And version, although I'd use Recordcount myself.

按照修改后的问题进行

从您添加到问题中的一些代码中，我看到了两个问题，主要的问题是您的SQL语句丢失和ORDER BY子句.
问题是您期望结果集位于Begin Order后跟End Order序列中，但是您的SQL语句不能保证您这样做.
在大多数情况下，由于您使用自动增量作为ID，因此数据库引擎将以自然顺序返回数据，但不能保证:

From the bit of code you added to your question, I see a couple of issues, the main one being that your SQL Statement is missing and ORDER BY clause.
The problem is that you are expecting the resultset to be in the Begin Order followed by End Order sequence but your SQL Statement doesn't guarantee you that.
In most cases, since you're using an autoincrement as ID, the database engine will return the data in that natural order, but there is no guarantee that:

• 总是会这样发生
• 原始数据按预期顺序保存，导致ID的顺序错误.

因此，只要您对结果集的顺序有期望，就必须明确地对其排序.

So, whenever you have expectations about the sequence of the resultset, you must explicitly order it.

我还要重构这段代码:

' ids are autoincrement long integers '
SQLString = "select * from Orders where type = OrderBegin or type = OrderEnd"           

Dim OrderOpen as Boolean
OrderOpen = False

Set rs = db.Openrecordset(SQLString)
If rs.bof <> True And rs.eof <> True Then
   myrec.movelast
    If rs.fields("type").value = BeginOrder Then
        OrderOpen = True
    End If
End If

进入类似于以下的单独功能

Into a separate function similar to:

' Returns true if the given CustID has a Open Order, '
' false if they are all closed.'
Public Function IsOrderOpen(CustID as Long) As Boolean
    Dim result as Boolean
    result = False

    Dim sql as String
    ' Here I assume that the Orders table has a OrderDateTime field that '
    ' allows us to sort the order in the proper chronological sequence '
    ' To avoid loading the complete recordset, we sort the results in a way '
    ' that will return the last used order type as the first record.'
    sql = sql & "SELECT Type " 
    sql = sql & "FROM Orders "
    sql = sql & "WHERE ((type = OrderBegin) OR (type = OrderEnd)) "
    sql = sql & "      AND (CustID=" & CustID & ")"
    sql = sql & "ORDER BY OrderDateTime DESC, Type DESC;"

    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Set db = CurrentDB()
    Set rs = db.Openrecordset(sql, dbOpenForwardOnly)

    If Not (rs Is Nothing) Then
        If rs.RecordCount > 0 Then
            result = (rs!type = BeginOrder)
        End If
        rs.Close
    End If

    Set rs = Nothing
    Set db = Nothing

    IsOrderOpen = result
End Function

这会使整个过程变得更健壮.

This would make the whole thing a bit more robust.

这篇关于为什么对于新的非空记录集，bof/eof之一偶尔会为真的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！