The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Must a recordset always be closed?
Posted: 14 Mar 08 7:14 PM
|
Or is it only done with an updateable recordset?
Here's my code:
Sub lveLeaderExitControl(Sender)
Dim strLeaderID
Dim strLeaderEmail
strLeaderID = lveLeader.LookupID
strSQL = "Select email from userinfo where userid = '" & strLeaderid & "'"
set objCon = application.GetNewConnection
set objRS = objCon.Execute(strSQL)
strLeaderEmail = objRS.fields(0).value
if strLeaderEmail <> "" then
(manulpuate form controls)
end if
objRS.close
Set objRS = nothing
Set objCon = nothing
End Sub
I'm getting an "Object required: objRS" error on the line reading objRS.Close. I don't get it. objRS is defined and used earlier in the routine. The plugin runs without error if I comment it out, but I was under the impression that recordsets should always be closed to conserve system resources.
|
|
|
|
Re: Must a recordset always be closed?
Posted: 15 Mar 08 8:19 AM
|
Really setting them to nothing is what frees the resources and then garbage collection pre .NET is somewhat of a hidden process anyway, but that is what you are supposed to do.
.close and then set to nothing. always.
Your right the code you show should error on the objrs.fields(0).value line if anything was wrong with the RS, not on the close line.
The only concern I see with the above code is that if the SQL returns no results your strLeaderEmail = line will fail.
Checking for EOF before using a recordset is another good idea.
That if strLeaderEmail <> "" line is interesting because if nothing came back from your SQL you would have crashed one line above....
Is there anything in that IF that is working with the RS? |
|
|
|
Re: Must a recordset always be closed?
Posted: 16 Mar 08 9:41 AM
|
Recordsets should be explicity closed, and set to nothing as well. Not doing so can lead to memory leaks. You would think that when the method goes out of scope the script engine would dispose of the recordset and connection objects howere it does not aloways seem to be true. It seems that because you are getting to the point where the Recordset seems to be nothing more then likely it never get set in the first place or somewhere in your code it gets closed. I would put a stop at the top of the method and walkthru with the debugger and identify any of the failing logic.
- Mark |
|
|
|
Re: Must a recordset always be closed?
Posted: 17 Mar 08 9:08 AM
|
I might be off base but when you set objRS = objCon.Execute(strSQL) you aren't actually "opening" the recordset you are setting it to the return value so you can not "close" that recordset. So that is why you get the error when you try to close the recordset.
Ted |
|
|
|
Re: Must a recordset always be closed?
Posted: 21 Mar 08 10:33 AM
|
You can always just check the state as follows:
if (objRS.state = adStateOpen) then
objRS.close
end if
set objRS = nothing
You could simplify your code extensively by simply including the SLX Database Support script and changing this:
Sub lveLeaderExitControl(Sender)
Dim strLeaderID
Dim strLeaderEmail
strLeaderID = lveLeader.LookupID
strSQL = "Select email from userinfo where userid = '" & strLeaderid & "'"
set objCon = application.GetNewConnection
set objRS = objCon.Execute(strSQL)
strLeaderEmail = objRS.fields(0).value
if strLeaderEmail <> "" then
(manulpuate form controls)
end if
objRS.close
Set objRS = nothing
Set objCon = nothing
End Sub
to this:
Sub lveLeaderExitControl(Sender)
Dim strLeaderEmail
strLeaderEmail = GetField("email","Userinfo","userid = '" & lveLeader.LookupID & "'") & "" 'add the & "" so if null is returned you know you will be working with an empty string at the least instead of the null
if strLeaderEmail <> "" then
(manipulate form controls)
end if
End Sub
|
|
|
|
Re: Must a recordset always be closed?
Posted: 22 Mar 08 11:53 PM
|
How then do you handle the case in Opportunity Products with the inline editing of the grid? A recordset object is opened, columns are assigned to it and then it isn't closed. In fact it can't be closed or else the grid will cease to function when you set grid.recordset = objectRecordSet. I decided to mimic this behavior since it was something that visibly worked and I tried everything to see if I could get around keeping the recordset open to no avail.
If I had to guess I would say the bulk of the cleanup happens when the SLX_DB object is closed and keeping that one object opened is neglegable. I could be wrong as I haven't really measured opening and closing my customization a couple dozen times. In any case it is always good practice to follow your advice unless you absolutely have to keep it open. |
|
|
|
Re: Must a recordset always be closed?
Posted: 25 Mar 08 9:40 AM
|
OK, what I did was move the close and set obj = nothing lines up before the if statement. Once the variable is set, the recordset isn't needed anymore. the application seems happy with that.
Thanks for the hint about the GetField function. That will make some things easier. |
|
|
|
Re: Must a recordset always be closed?
Posted: 26 Mar 08 9:02 AM
|
i would suggest to close the Recordset to free the memory occupied by the recordset object...
But as Per your code , you havent opened the Recordset objec..you are directly executing it with the recordset...
So if you would have used "recordset.Open" property..then your code works...fine..
Thank you |
|
|
|
Re: Must a recordset always be closed?
Posted: 26 Mar 08 6:13 PM
|
Originally posted by Jeremy Brayton
.... SLX_DB object is closed ... |
|
There's been a lot of reported cases where using the SLX_DB object from the SalesLogix db support files has caused problems.. It's implemented using a "class" and there's cases when it does not clean itself up well...
We hardly ever use the SalesLogix db support files but have created a "ToolBoxX Suite that we include when needed.. THUS:
sSQL00 = "SELECT LASTNAME + ', ' + FIRSTNAME " & _
"FROM CONTACT " & _
"WHERE CONTACTID = '" & Application.BasicFunctions.CurrentContactID & "'"
Control.Text = GetScalarValue(sSQL00)
GetScalarValue is one of 20 or 30 functions we have built..
--
RJLedger - rjlSystems |
|
|
|
Re: Must a recordset always be closed?
Posted: 27 Mar 08 1:49 PM
|
What are those cases, roughly? I made my own SLX_DB class that uses Class_Terminate() (or whatever the name is) to call a .Close() routine. To be thorough I always call .close in my code so I'm hoping that doesn't create the issue.
My class doesn't do Executes mainly because I like parsing recordsets the hard way. Also, my GetNewRecordSet takes in a SQL string parameter and only returns a recordset if the SQL is valid. I don't know if any of that helps but it makes it easier on me. |
|
|
| |
|