-
Jul 27th, 2009, 03:09 PM
#1
Thread Starter
Fanatic Member
[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!!!!
-
Jul 27th, 2009, 03:17 PM
#2
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.
-
Jul 27th, 2009, 03:21 PM
#3
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
-
Jul 27th, 2009, 03:28 PM
#4
Thread Starter
Fanatic Member
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.
-
Jul 27th, 2009, 03:30 PM
#5
Thread Starter
Fanatic Member
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.
-
Jul 27th, 2009, 03:38 PM
#6
Re: How to check if connection is open/closed, is recordset is open/closed
Originally Posted by chris.cavage
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).
-
Jul 27th, 2009, 05:01 PM
#7
Thread Starter
Fanatic Member
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.
-
Jul 27th, 2009, 05:28 PM
#8
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.
-
Jul 27th, 2009, 07:21 PM
#9
Thread Starter
Fanatic Member
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
-
Jul 28th, 2009, 11:58 AM
#10
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)
-
Jul 28th, 2009, 04:50 PM
#11
Thread Starter
Fanatic Member
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:
If Not rs Is Nothing Then
If (rs.State And adStateOpen) = adStateOpen Then
rs.Close
End If
Set rs = Nothing
End If
If Not cn Is Nothing Then
If (cn.State And adStateOpen) = adStateOpen Then
cn.Close
End If
Set cn = Nothing
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.
-
Jul 28th, 2009, 04:57 PM
#12
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|