-
Nov 2nd, 2013, 03:05 PM
#1
Thread Starter
Enjoy the moment
[RESOLVED] question about Rs.Open Rs.Close
if my code is like this
Code:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Customers", CN, adOpenStatic, adLockOptimistic
If rs.RecordCount = 0 Then
MsgBox "no Customers Found ", vbExclamation
Exit Sub
Else
FrmPhoneBook.Show 1
End If
do i need to add Rs.Close?
if so where do i add this?
-
Nov 2nd, 2013, 03:12 PM
#2
Re: question about Rs.Open Rs.Close
You should always close recordsets that you open, where you should close it depends on what you are doing with it.
In general you close it when you are done with it
Code:
Rs.Open .......
' Do something with RS
Rs.Close
Or
Code:
Set RS=Cn.Execute .....
'do something with RS
Rs.Close
VB does its best to clean up behind you but is always better to close the objects when done with them to free up memory and avoid possible unforeseen issues.
Of course in your case as usual you have an Exit Sub in there that should not be there and that could cause an issue.
You really need to get out of that habit, it is almost as bad as using Goto
-
Nov 2nd, 2013, 03:17 PM
#3
Thread Starter
Enjoy the moment
Re: question about Rs.Open Rs.Close
so after every set Rs and Rs.Open
i need to use a Rs.Close?
second question
how do i check in my project if there is any connection or any Rs open?
-
Nov 2nd, 2013, 03:32 PM
#4
Thread Starter
Enjoy the moment
Re: question about Rs.Open Rs.Close
and i tell you why i put exit sub
beacuse somebody told me that if i dont put exit sub then the form will display
e.x
if abs = 1 then
msgbox " wrong password"
exit sub
else
you understand DM?
-
Nov 2nd, 2013, 03:35 PM
#5
Re: question about Rs.Open Rs.Close
Set really has nothing to do with it
When you do a Cn.Execute as you often do with an RS as the target that is the same as if you did RS.OPEN
You should close any recordsets that you open as well as connections, files and anything else that you open in your program.
in the other question if you mean through code then you check the state property of the recordset or connection
but if you mean hw to find places where you are not closing them I would say search for .Execute and .Open in most of those cases you will likely find you have opened a recordset that you did not close
-
Nov 2nd, 2013, 03:44 PM
#6
Thread Starter
Enjoy the moment
Re: question about Rs.Open Rs.Close
i understand
well i take you advice and removed a exit sub from a code
and stiil it lets me do something when i told him no
please tell me why?
Code:
If CmbUser.ListIndex = -1 Then
MsgBox "choose a user from the list", vbExclamation
CmbUser.SetFocus
'Exit Sub when i removed this the procedure keep going when it is not supposed to :confused:
End If
do you use exit sub?
if so in what conditions?
-
Nov 2nd, 2013, 03:46 PM
#7
Re: question about Rs.Open Rs.Close
Originally Posted by salsa31
and i tell you why i put exit sub
beacuse somebody told me that if i dont put exit sub then the form will display
e.x
if abs = 1 then
msgbox " wrong password"
exit sub
else
you understand DM?
Let me try and explain this one for you
Code:
Private SomeSub()
if abs = 1 then
msgbox " wrong password"
exit sub
else
form1.show
End If
End Sub
Code:
Private SomeSub()
if abs = 1 then
msgbox " wrong password"
Goto Done
else
form1.show
End If
Done:
End Sub
Code:
Private SomeSub()
if abs = 1 then
msgbox " wrong password"
else
form1.show
End If
End Sub
All three of those will do exactly the same thing except the top two have a line of code that causes a jump to the end the third one is correct and does no jumps.
One major issue with using Exit Sub or Goto is that you have to pay attention to every line of code to see if there is something there that disrupts the flow and it can make it much harder to read your code and find issues with it
Code:
If Something Then
Do Something
Exit Sub
Else
Do Something Else
End If
Do another thing
End Sub
Now in this case the line Do another thing would execute no matter what the If test is without the exit sub in there. The exit sub causes it to skip that line of code but if the code was slightly different it would not need to jump at all and would do the same thing
Code:
If Something Then
Do Something
Else
Do Something Else
Do another thing
End If
End Sub
In most cases where you have used exit sub it would not be needed if the If block was correct and in some cases the If block is correct and it is not needed. It is ok to use it now and then but it should not be used often unless used as part of an error handler where it is required, as is Goto.
Code:
Sub SomeSub()
On Error Goto ErrHandler
'Some lines of code
Exit Sub
ErrHandler:
'Lines of code to handle the error
End Sub
This is a common way that exit sub is used here the reason is to skip the error handler when no error has occurred.
-
Nov 2nd, 2013, 03:49 PM
#8
Thread Starter
Enjoy the moment
Re: question about Rs.Open Rs.Close
that is very explained thank you very much DM
-
Nov 2nd, 2013, 03:51 PM
#9
Re: [RESOLVED] question about Rs.Open Rs.Close
One more example
Code:
If Something then
Msgbox "You can't do that"
Exit Sub
End If
If SomethingElse then
'do some stuff
End IF
End Sub
Now in the code above the exit sub prevents the second If test from executing when the first test is true because the exit sub will execute and cause the code to jump down to the end sub line
However you could write that same piece of code like this
Code:
If Something then
Msgbox "You can't do that"
Else
If SomethingElse then
'do some stuff
End IF
End If
End Sub
Which uses a structured If Block to control the program flow and is much more readable without the abrupt jump in execution
Both pieces of code will do exactly the same thing
-
Nov 2nd, 2013, 03:52 PM
#10
Re: [RESOLVED] question about Rs.Open Rs.Close
In this code:
Code:
if abs = 1 then
msgbox " wrong password"
exit sub
else
If abs is equal to 1 then you bring up a msgbox. That is ALL. You have an else there, SO, anything between that else and your End Sub will not be executed ANYWAY. However, in a lot of your code you have shown on this forum, you use Exit Sub when you SHOULD HAVE HAD an ELSE. In all those cases, you should revisit your code and rewrite your IF-Else statement so that you don't need an Exit Sub.
For example, you do things LIKE:
Code:
Private Sub COmmand1_click()
IF a = x then
'do this code
Exit Sub
End if
Label1.caption = cstr(a)
label2.caption = cstr(x)
End sub
This code is more appropriately written with an Else statement, like thus:
Code:
Private Sub COmmand1_click()
IF a = x then
'do this code
else
Label1.caption = cstr(a)
label2.caption = cstr(x)
end if
End sub
See? The only thing that gets done in that second (like the first--but is bad practice) is that if a equals x, you 'do some code. The stuff inbetween the else and end if never get done. It ONLY gets done if a does NOT equal x.
Understand SA?
-
Nov 2nd, 2013, 03:53 PM
#11
Re: question about Rs.Open Rs.Close
Hmmm,,,I see DM just posted same stuff....sorry for interrupting.
-
Nov 2nd, 2013, 03:58 PM
#12
Thread Starter
Enjoy the moment
Re: [RESOLVED] question about Rs.Open Rs.Close
thats ok sami
so DM you are telling me that if there is a else then the exit sub dosnt have any need?
-
Nov 2nd, 2013, 04:05 PM
#13
Re: [RESOLVED] question about Rs.Open Rs.Close
Well everything depends on how your code is written
Code:
If something then
'Some code here will execute only if the If tests as true
Else
'Some code here will execute only if the If test Is false
End If
' Once the code in either part has finished the code will go here
'Code here would execute no matter what the If test was
End Sub
So yes in general you should never need an exit sub unless you have a procedure error trap at the bottom like in the one example I showed.
This is normally the only place I use them when writing a program
I do however get lazy and use them sometimes when I am making a change and don;t have much time
It can be easier to through a exit sub in the middle of a large procedure rather than creating the proper structure but is never the best way to go.
-
Nov 2nd, 2013, 04:09 PM
#14
Re: [RESOLVED] question about Rs.Open Rs.Close
It is also better to handle the most likely case first and the exception in the else portion
So for example rather than writing this
Code:
If Text1.Text="" Then
msgBox "Warning"
Exit Sub
End If
'Do something
'or
If Text1.Text="" then
msgBox "Warning"
Else
' do stuff
End If
It is better to write it like
Code:
If Not Text1.Text="" Then
'do stuff
Else
msgbox "Warning"
End If
Of course either way will work but the last one will be the fastest in most cases because there will usually be something in the text box
-
Nov 2nd, 2013, 04:11 PM
#15
Thread Starter
Enjoy the moment
Re: [RESOLVED] question about Rs.Open Rs.Close
mmm
tell me what you do in this case
Code:
If FrmEmployees Is ActiveForm Then
MsgBox "Please close all windows before exiting the program", vbExclamation
cancel = True
Exit Sub
End If
If FrmEnterLog Is ActiveForm Then
MsgBox "Please close all windows before exiting the program", vbExclamation
cancel = True
Exit Sub
End If
what you do instead of the Exit Sub?
-
Nov 2nd, 2013, 05:21 PM
#16
Re: [RESOLVED] question about Rs.Open Rs.Close
Is that all of the code in the sub?
Since there is no line for the top and end of the sub I can't tell
If that is all then you would do something like this
Code:
If FrmEmployees Is ActiveForm Then
MsgBox "Please close all windows before exiting the program", vbExclamation
cancel = True
ElseIf FrmEnterLog Is ActiveForm Then
MsgBox "Please close all windows before exiting the program", vbExclamation
cancel=true
End If
Or better still you could
Code:
If FrmEmployees Is ActiveForm Or FrmEnterLog Is ActiveForm Then
MsgBox "Please close all windows before exiting the program", vbExclamation
cancel=true
End If
or you could use a select case or ....
Last edited by DataMiser; Nov 2nd, 2013 at 05:27 PM.
-
Nov 2nd, 2013, 05:50 PM
#17
Thread Starter
Enjoy the moment
Re: [RESOLVED] question about Rs.Open Rs.Close
that definitely answered my question
tnx for the info realy helpful
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
|