Results 1 to 20 of 20

Thread: [RESOLVED] ADODB.Recordset, how to know if its open??

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Resolved [RESOLVED] ADODB.Recordset, how to know if its open??

    ADODB.Recordset

    As in the title, is there a way to test if the ADODB.Recordset is open

    I want to create a test before I try to close the recordset because if it is not open then I get an error

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: ADODB.Recordset, how to know if its open??

    It has a State property that will let you know if it's closed or not.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: ADODB.Recordset, how to know if its open??

    Quote Originally Posted by techgnome View Post
    It has a State property that will let you know if it's closed or not.

    -tg
    How will it do that?

    I have tested using the code


    Code:
    If RS1.State = 1 Then
    RS1.Close
    Set RS1 = Nothing
    End If
    Which will work if the recordset is open, but if it is closed then the 'state' property does not get set so the if statement does not work and still fires the RS1.close statement

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: ADODB.Recordset, how to know if its open??

    Ummm... no... if it hasn't been set, then it's value is closed:
    Quote Originally Posted by From_MSDN
    The default value is adStateClosed
    so if it's getting into the If statement, then it is open...

    http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx
    Something to keep in mind...
    Quote Originally Posted by From_MSDN
    The object's State property can have a combination of values. For example, if a statement is executing, this property will have a combined value of adStateOpen and adStateExecuting.
    something you may want to do is instead of checking if it's open, check if it isn't Closed.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: ADODB.Recordset, how to know if its open??

    Thanks for your help, this seems to work:

    Code:
    Dim State As Long
    State = RS1.State
    
    If State <> 0 Then
    RS1.Close
    Set RS1 = Nothing
    End If

  6. #6
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Smile Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    after open connection . you can try to check using rs.state if it is equal to 1 then your recordset is open .otherwise recordset is not open .see the following code for better idea.
    Code:
    Private Sub Command1_Click()
    
    Dim db As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim lTimer As Long
    
    Screen.MousePointer = vbHourglass
    Command3_Click
    MSFlexGrid1.Refresh
    lTimer = Timer
    
    MSFlexGrid1.Visible = False
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"
    rs.Open "SELECT * FROM COMUNI", db, adOpenStatic, adLockReadOnly
    
    If rs.State = 1 Then
       MsgBox ("Recordset is open")
    ElseIf rs.State <> 1 Then
      MsgBox ("Recordset is not open")
      Exit Sub
    End If
    rs.MoveFirst'Now try to process the data
    
    MSFlexGrid1.Rows = rs.RecordCount + 1
    MSFlexGrid1.Cols = rs.Fields.Count - 1
    MSFlexGrid1.Row = 0
    MSFlexGrid1.Col = 0
    MSFlexGrid1.RowSel = MSFlexGrid1.Rows - 1
    MSFlexGrid1.ColSel = MSFlexGrid1.Cols - 1
    MSFlexGrid1.Clip = rs.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString)
    MSFlexGrid1.Row = 1
    MSFlexGrid1.Visible = True
    
    Set rs = Nothing
    Set db = Nothing
    
    Screen.MousePointer = vbDefault
    
    MsgBox "Execution time: " & Timer - lTimer & " sec." & vbCr & "of " & MSFlexGrid1.Rows - 1 & " record"
    
    End Sub:wave:
    Last edited by firoz.raj; Feb 27th, 2012 at 02:13 AM.

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Quote Originally Posted by firoz.raj View Post
    after open connection . you can try to check using rs.state if it is equal to 1 then your recordset is open .otherwise recordset is not open
    That is wrong, and what you recommend will fail in various circumstances - see the documentation (link in post #4) for the details.

    The proper way to do it is bit-masking, eg:
    Code:
    If (rs.State And adStateOpen) Then
       MsgBox "Recordset is open"
    End If

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Quote Originally Posted by si_the_geek View Post
    That is wrong, and what you recommend will fail in various circumstances - see the documentation (link in post #4) for the details.

    The proper way to do it is bit-masking, eg:
    Code:
    If (rs.State And adStateOpen) Then
       MsgBox "Recordset is open"
    End If

    Far be it for me to correct the mighty 'si_the_geek' but that will not work either,

    If the rs.state is just closed maybe it's OK, but once it is 'set = nothing' then the value of the rs.state function is "Object variable or With block variable not set" so it will still pass the test si_the_geek has posted and trigger the messagebox.

    As I posted above this does seem to work

    Code:
    Dim State As Long
    State = RS1.State
    
    If State <> 0 Then
    RS1.Close
    Set RS1 = Nothing
    End If
    Last edited by JohnSavage; Feb 27th, 2012 at 08:09 AM.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    No, actually it won't... and that's a different problem all together, and something that you should be checking for if it is likely to occur. If it's still running once the error happens, then I suspect you've got On Error Resume Next... which isn't a good idea.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Quote Originally Posted by JohnSavage View Post
    Far be it for me to correct the mighty 'si_the_geek' but that will not work either,
    Yes it does, as long as what you want to check is whether it is open - which is what you had told us before, but now turns out to not be the case.

    If you also want to check if it is Set, you should be checking that separately.

    If you also want to use On Error Resume Next, you need to be extra careful (as always with OERN), otherwise you will sabotage your program - as you did here. In this case the check would need to be that it is not open, and you should also not simply ignore the many other potential errors that could occur (such as locks etc) rather than handling them in an appropriate manner (for many of them an error message would be preferable).

    As I posted above this does seem to work

    Code:
    ...
    If State <> 0 Then
    ...
    The word "seem" is very important, because that code is not reliable - there are situations where it can be closed but the state is not 0.


    The proper way to do both checks you want is like this (without OERN):
    Code:
    If Not(RS1 Is Nothing) Then
      If (RS1.State And adStateOpen) = adStateOpen Then RS1.Close
      Set RS1 = Nothing
    End If

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Quote Originally Posted by techgnome View Post
    No, actually it won't... and that's a different problem all together, and something that you should be checking for if it is likely to occur. If it's still running once the error happens, then I suspect you've got On Error Resume Next... which isn't a good idea.

    -tg
    Ok, my solution does not work, si_the_geek's solution does not work and firoz.raj, solution does not work so how's it done

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    You can check whether or not the RS variable is Nothing before checking its State, but normally you only do this in places like a Form_Unload event for last-ditch cleanup.


    I'm constantly amazed by all of the programs people write where their code doesn't "know" the state of objects when it depends on that state. Over and over we see questions asking how to know if a Form is loaded, etc.

    If you have closed the Recordset (or not opened it yet) your User Interface should be managed to reflect the fact. Disable controls that can't be used until your Recordset is open, enable them when you open it, and disable them when you close it unless you are shutting down.

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Look at post #10

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Thank You gentlemen, all I can say is I come here to learn and I have

    I believe this thread is now closed, but I could be wrong again

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    I trust that this meets with the approval of all ?

    Code:
    If (RS1 Is Nothing) Then
      MsgBox "Recordset is closed and = Nothing"
      Else
      If (RS1.State And adStateOpen) = adStateOpen Then
      MsgBox "Recordset is open"
      End If
    End If

  16. #16
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Aside from the msgboxes and the fact it is not doing anything it looks fine.

    Debug.Print is your friend

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    It's fine, but can be made much easier to read by using ElseIf and apt indenting:
    Code:
    If (RS1 Is Nothing) Then
      MsgBox "Recordset is closed and = Nothing"
    ElseIf (RS1.State And adStateOpen) = adStateOpen Then
      MsgBox "Recordset is open"
    End If

  18. #18
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Smile Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    That is wrong, and what you recommend will fail in various circumstances - see the documentation (link in post #4) for the details.

    The proper way to do it is bit-masking, eg:
    if recordset is open .then rs.state must be equal to adstateopen.and it
    cannot be fail in anyway .

    Code:
    if rs.state=adstateopen then
        
       ' do whatever you want 
       
       End if 
    
    
    or if (not rs is nothing) then
          ' Do whatever you want
       End if

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Quote Originally Posted by firoz.raj View Post
    if recordset is open .then rs.state must be equal to adstateopen.
    That is wrong.

    Here is a repeat of the previous quote of the documentation:
    The object's State property can have a combination of values. For example, if a statement is executing, this property will have a combined value of adStateOpen and adStateExecuting.
    If the recordset is open the value can be just adStateOpen (1), or it can be adStateOpen+adStateExecuting (1+2=3), or it can be other combinations of values which include adStateOpen.

  20. #20
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] ADODB.Recordset, how to know if its open??

    Quote Originally Posted by firoz.raj View Post
    if recordset is open .then rs.state must be equal to adstateopen.and it
    cannot be fail in anyway .

    Code:
    if rs.state=adstateopen then
        
       ' do whatever you want 
       
       End if 
    
    
    or if (not rs is nothing) then
          ' Do whatever you want
       End if
    No... no... no... you didn't read what I posted did you.... it is possible to have MULTIPLE states.... IE, it can OPEN AND EXECUTING AT THE SAMETIME... therefore your code fails the open test... It can also be CLSOED and EXECUTING ... in theory... which is why when I used to use this code, I checked for "Not .State = Closed" ... so if it is ANYTHING other than an explicit close, then I issue the Close command.

    The safest thing to do is as si has shown, check for instance (is not nothing) then check the .State using the bit masking. If the bit masking contains Open, then close it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width