1

I've been looking for a way to know if a ADO recordset is open. I find this where I met the syntax: 

If Not (rs Is Nothing) Then
  If (rs.State And 1) = 1 Then rs.Close
  Set rs = Nothing
End If

I read explanation posted by @Raybarg however I have the following question: Syntax:

If Not (rs Is Nothing) Then
  If rs.State > 0 Then rs.Close
  Set rs = Nothing
End If

is not equivalent? There may be a case where Recordset exists but .State property returns something other than an ObjectStateEnum value?

Thanks!

Cip
  • 26
  • 5
  • What context are you using this in? Depending on the what the state of the Recordset is, attempting to close it could throw an error if `ObjectStateEnum` > `adStateOpen`. – Comintern Sep 12 '18 at 13:01
  • **May there be a case where Recordset exists but .State property returns something other than an ObjectStateEnum value?** Is this actually your question? – Vityata Sep 12 '18 at 13:17
  • I usually use the condition `If rs.State > 0 Then rs.Close` and I want to know if this can return other results than expected. For this I try to understand the problem... If the two code is equivalent case is solved. – Cip Sep 12 '18 at 13:37

2 Answers2

0

You have answered your own question, actually: the two are not equivalent since there are five different States, of which adStateOpen (= 1) is the only one the 1st syntax allows closing the Object. You might not want to close the object while Executing or Fetching, etc. (as in Open and Executing: adStateOpen + adStateExecuting = 1 + 4 = 5)

The x AND 1 = 1 only if x=1, otherwise x AND 1 = 0 for any other x value. This said, in the 1st syntax example, it would have been enough to just test If rs.State = 1 Then rs.Close.

  • I'm not sure that `x AND 1 = 1 only if x = 1`! In the example from [this post](https://stackoverflow.com/questions/17611545/check-if-adodb-connection-is-open?answertab=votes#tab-top) I find: `'ex. If (0001 And 0001) = 0001 (only open flag) -> true  'ex. If (1001 And 0001) = 0001 (open and retrieve) -> true  'This second example means that it is open, but its value is not 1 and if rs.State = 1 -> false, even though it is open` – Cip Sep 13 '18 at 06:07
  • @Raybarg says in that post that this is because it's bitwise `And` operation. On the other hand, the Recordset closure lines are usually in the errorhandling or exit area when you may want to close the object even though the Executing or Fetching. – Cip Sep 13 '18 at 06:08
  • @Cip It makes no sense whatsoever to be in this conversation and to say "**I'm not sure** that x AND 1 = 1 only if x = 1!" It takes a couple of second to just test it. The flags are indeed designed to be binary: adStateOpen = 1 = 0b0001, adStateFetching = 8 = 0b1000; this way you can just raise or lower an appropriate bit to indicate the appropriate combination of states: adStateOpen + adStateFetching = 0b1001... It's Electrical Engineering, Networks, generally speaking. BUT WHAT DOES THIS HAVE TO DO WITH YOUR QUESTION?!!! –  Sep 13 '18 at 12:05
  • **1** I tested: statement `x AND 1 = 1 only if x=1` is FALSE for all odd numbers. **2** The reformulated question: under what conditions `rs.State > 0` can have another value of truth than `(rs.State And 1) = 1` – Cip Sep 13 '18 at 14:19
  • @Cip Oh, c'mon! The first one is `True` if `rs` is not closed (whatever else might be happening) while the second one is `True` only if `rs` is open and nothing else is going on :) –  Sep 13 '18 at 14:29
  • _while the second one is True only if rs is open and nothing else is going on_ **it certainly is not so!** Example: When data is fetched from the database, `rs.State` is 9 (`adStateOpen` + `adStateFetching`). Then the expression `(rs.State And 1) = 1` becomes `(9 And 1) = 1` ie `(1001 and 0001) = 0001`. The operation in brackets is bit to bit and the result is 0001. In conclusion `(rs.State And 1) = 1` is True even when fetching data! – Cip Sep 14 '18 at 07:04
0

Normally, each bit could be individually set and so checking the first way ensures that the open flag is set. Maybe you are thinking that there is no case where the state is not open because the other states are modifiers of an open state. Let's look at the MS API reference for a RecordSet.State. It holds something called ObjectStateEnum. We can see there is one state that is not open and not closed called "Connecting". Your second method would try to close a connection that is in Connecting state where the first method would not. That is the only functional difference I can see.

ObjectStateEnum

Constant            Value   Description
--------            -----   -----------
adStateClosed       0       Indicates that the object is closed.
adStateOpen         1       Indicates that the object is open.
adStateConnecting   2       Indicates that the object is connecting.
adStateExecuting    4       Indicates that the object is executing a command.
adStateFetching     8       Indicates that the rows of the object are being retrieved.

SOURCE: https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/objectstateenum?view=sql-server-2017

HackSlash
  • 4,944
  • 2
  • 18
  • 44