[RESOLVED] How to check if connection is open/closed, is recordset is open/closed-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] How to check if connection is open/closed, is recordset is open/closed

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    757

    Resolved [RESOLVED] How to check if connection is open/closed, is recordset is open/closed

    I want to make sure that I am properly closing my recordsets and connections.

    I have read the FAQs and understand how to close them properly, but I would like to check while my application is running if either a recordset (rs) or connection (cn) to mysql database is open or closed. And if they are set to nothing.

    I was thinking I would just run my application, and at random times I could pause the app and enter something into the immediate window to find this out.

    Can someone please advise?

    There is a lot going on in my code (a lot of if...then... elseifs...) and I want to make sure that throughout my application that I closed everything when I needed to.

    Thanks!!!!

  2. #2
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: How to check if connection is open/closed, is recordset is open/closed

    Check the State property value of each object.
    State Property (ADO)


    Describes for all applicable objects whether the state of the object is open or closed.

    Describes for a Recordset object executing an asynchronous method, whether the current state of the object is connecting, executing, or fetching.

    Return Value

    Returns a Long value that can be one of the following constants.

    Constant Description
    adStateClosed Default. Indicates that the object is closed.
    adStateOpen Indicates that the object is open.
    adStateConnecting Indicates that the Recordset object is connecting.
    adStateExecuting Indicates that the Recordset object is executing a command.
    adStateFetching Indicates that the rows of the Recordset object are being fetched.


    Remarks

    You can use the State property to determine the current state of a given object at any time. This property is read-only.

    The Recordset 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.
    State property applies to ado Connection/Command and Recordset objects.

  3. #3
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: How to check if connection is open/closed, is recordset is open/closed

    Also, you may use code similar to this (not tested):
    Code:
        If Not myConnection Is Nothing Then
            Select Case myConnection.State
                Case adStateClosed
                Case adStateOpen
                Case Else
            End Select
        Else
            'reset connection object
        End If

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    757

    Re: How to check if connection is open/closed, is recordset is open/closed

    Thanks. I am using ado, so that helps.

    I don't know how to use the immediate window... I am googling it, but cannot seem to find a clear-cut explanation as what to enter for this.

    I found debug.print cn.state , but I get: Run time error 91, Object variable or with variable not set from the immediate window.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    757

    Re: How to check if connection is open/closed, is recordset is open/closed

    Thanks Rhinobull - I am using code similar to that in form_unload event especially. It works well.

    I guess I am just working on ways to learn about the immediate window, and this was a good place to start.

    I am fairly certain that my connections are closing properly, but I would still like to figure out how to display the info in the immediate window. In case, I need something like this for later on.

  6. #6
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: How to check if connection is open/closed, is recordset is open/closed

    Quote Originally Posted by chris.cavage View Post
    I found debug.print cn.state , but I get: Run time error 91, Object variable or with variable not set from the immediate window.
    That's because you did not check if it was still valid object (it was already set to nothing I guess).

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    757

    Re: How to check if connection is open/closed, is recordset is open/closed

    Yeah, that's right rhinobull. I paused the app while it was loading a form that connected remotely. It returns "1" while connecting.

    So if I run that line of code in immediate, and if I get that error, then I know that connection is closed and set to nothing.

  8. #8
    VBaholic & Loving It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    GetWindowRect()
    Posts
    11,999

    Re: How to check if connection is open/closed, is recordset is open/closed

    Immediate window shortcut: Ctrl+G
    When using that window, to type & execute commands, to check values in your code, the code must be stopped/paused in the routine you are checking before you try to execute something in the immediate wiindow. Exceptions are if you are querying something declared Public in a bas module or in the declarations section of a form/class/usercontrol and the code is also paused in that form/class/usercontrol.

    Be warned, just as you can check a value, you can also set a value. Setting a value in the immediate window could have adverse effects on the code that follows the current pause/break point in that routine.
    Insomnia is just a byproduct of, "It can't be done"

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum. Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts


    {Alpha Image Control} {Memory Leak FAQ} {GDI+ Classes/Samples} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VBA Control Arrays} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    757

    Re: How to check if connection is open/closed, is recordset is open/closed

    Thanks, I am going to work on all this now. Appreciate the advice.

    Chris

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

    Re: How to check if connection is open/closed, is recordset is open/closed

    Thread moved to Database Development forum (the "VB6" forum is only meant for questions which don't fit in more specific forums)

    An important part of what RhinoBull quoted (which also applies to the other ADO objects with a .State property) is this:
    The Recordset 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.
    ..as such, doing a direct comparison is not safe - you should do bit masking instead, eg:
    Code:
        If Not myConnection Is Nothing Then
          If (myConnection.State And adStateOpen) = adStateOpen Then
            'open
          Else
            'closed
          End If
        Else
          'not set
        End If
    So, if you want to tidy up properly when you have finished with it, you could use this:
    Code:
        If Not myConnection Is Nothing Then
          If (myConnection.State And adStateOpen) = adStateOpen Then
            myConnection.Close
          End If
          Set myConnection = Nothing
        End If
    (note that you can safely use this in an error handler)

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    757

    Re: How to check if connection is open/closed, is recordset is open/closed

    Awesome, thanks.

    Here is the code that I use now:

    vb Code:
    1. If Not rs Is Nothing Then
    2.        
    3.         If (rs.State And adStateOpen) = adStateOpen Then
    4.             rs.Close
    5.         End If
    6.        
    7.         Set rs = Nothing
    8.     End If
    9.    
    10.     If Not cn Is Nothing Then
    11.         If (cn.State And adStateOpen) = adStateOpen Then
    12.             cn.Close
    13.         End If
    14.         Set cn = Nothing
    15.     End If

    ... so I was closing properly. I use this code after I am done with my connection, and I also put it in Form_Unload, because my program resets itself to the main form after so many seconds of idle time.

    Thanks.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    757

    Re: How to check if connection is open/closed, is recordset is open/closed

    I wasn't too concerned earlier about closing my connections, because I knew I was closing properly with the code I just posted.

    However, I started testing the "program reset feature" that I just mentioned. I had some forms show modally also - I noticed that if the forms were shown modally, I was getting some problems when my program was unloading all open forms and returning to the main form. So instead of showing modally, I disabled the "parent" form and re-enable it when the "child" form was unloaded.

    This helped my connections close properly.

    When I was testing this over and over again - I eventually got an error that I never got before: Out of Memory. I was thinking it was because I was showing "child" forms modally before I closed the connection.

    And if the program was resetting itself, those connections were not ever being closed properly (and my recordsets).

    ... I haven't gotten that memory error again.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.