-
Feb 26th, 2012, 12:27 PM
#1
Thread Starter
Fanatic Member
[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
-
Feb 26th, 2012, 02:03 PM
#2
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
-
Feb 26th, 2012, 03:03 PM
#3
Thread Starter
Fanatic Member
Re: ADODB.Recordset, how to know if its open??
Originally Posted by techgnome
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
-
Feb 26th, 2012, 03:26 PM
#4
Re: ADODB.Recordset, how to know if its open??
Ummm... no... if it hasn't been set, then it's value is closed:
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...
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
-
Feb 26th, 2012, 05:23 PM
#5
Thread Starter
Fanatic Member
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
-
Feb 27th, 2012, 02:10 AM
#6
Frenzied Member
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.
-
Feb 27th, 2012, 06:58 AM
#7
Re: [RESOLVED] ADODB.Recordset, how to know if its open??
Originally Posted by firoz.raj
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
-
Feb 27th, 2012, 07:44 AM
#8
Thread Starter
Fanatic Member
Re: [RESOLVED] ADODB.Recordset, how to know if its open??
Originally Posted by si_the_geek
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.
-
Feb 27th, 2012, 07:53 AM
#9
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
-
Feb 27th, 2012, 08:39 AM
#10
Re: [RESOLVED] ADODB.Recordset, how to know if its open??
Originally Posted by JohnSavage
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
-
Feb 27th, 2012, 08:43 AM
#11
Thread Starter
Fanatic Member
Re: [RESOLVED] ADODB.Recordset, how to know if its open??
-
Feb 27th, 2012, 08:44 AM
#12
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.
-
Feb 27th, 2012, 08:45 AM
#13
Re: [RESOLVED] ADODB.Recordset, how to know if its open??
-
Feb 27th, 2012, 09:04 AM
#14
Thread Starter
Fanatic Member
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
-
Feb 27th, 2012, 09:21 AM
#15
Thread Starter
Fanatic Member
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
-
Feb 27th, 2012, 09:23 AM
#16
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
-
Feb 27th, 2012, 09:32 AM
#17
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
-
Feb 27th, 2012, 09:36 AM
#18
Frenzied Member
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
-
Feb 27th, 2012, 09:57 AM
#19
Re: [RESOLVED] ADODB.Recordset, how to know if its open??
Originally Posted by firoz.raj
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.
-
Feb 27th, 2012, 09:59 AM
#20
Re: [RESOLVED] ADODB.Recordset, how to know if its open??
Originally Posted by firoz.raj
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
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
|