Problem Closing and Setting a Recordset to Nothing

[Miked811]

Hi,

I have an ASP Page that holds all the functions I created named Function.asp and it looks like this:

Function setCn1(strSQL)
Dim strConn1, rsRecord

strConn1 = "Provider=SQLOLEDB.1; SERVER=SQL1;
UID=ABC; PWD=DEF; Database=DBRecords"

Set rsRecord = Server.CreateObject("ADODB.Recordset")
rsRecord.Open strSQL, strConn1

setCn1 = rsRecord
End Function

I included the Function.asp page to a another ASP Page, say Records.asp.

The function returns as a RecordSet. So from the Records.asp, I call the setCN1 function:

Dim rsRecordFromFunction, strSQL
strSQL = "Select * FROM Table"

Set rsRecordFromFunction = setCn1(strSQL)
Response.Write rsRecordFromFunction ("PhoneNum") & " - "
Response.Write rsRecordFromFunction ("AgentID") & " - "
Response.Write rsRecordFromFunction ("ResultCode")

This works perfectly well. Now my concern is:

1) How do I close the rsRecord from the Function.asp page and set it to nothing without declaring it as a Global Variable in the Records.asp page?

2)If I close and set the rsRecordFromFunction (from the Records.asp page) to Nothing, it gives me an error "Object doesn't support the method...or something". Does this mean that rsRecordFromFunction does not have to be Closed and Set rsRecordFromFunction to Nothing?

3) Is rsRecordFromFunction a recordset too?

Thanks alot

Mike

 

[Miked811]

Sorry, let me correct question #1.

1) How do I close the rsRecord from the Function.asp page and set it to nothing without declaring it as a Global Variable in the Function.asp page?

Sorry about that...

Thanks

Mike

 

[Tarwn]

Actually, the recordset to nothing portion of your question is the easy one. The difficult one, which you didn't ask about, is how to close the connection and set it to nothing.

Set Recordset = Nothing
When you use set what you are doing is changing where the variable points. Somewhere down in your computer's memory there is an object, in this case a recordSet object. That recordset initially has one variable pointing at it (rsRecord). When you then assign it as the return variable for your function, the object then has 2 variables pointing at it, the original variable and the memory slot for the return value of the function. Since you no longer need a reference to it in the function, you can go ahead at this point and set the rsrecord = Nothing. this will not affect your recordset, it simply moves the variable to point at an empty object.
Now you only have one thing pointing at the object again. Once you return it and your return value is set to another variable, the function reference should go away on it's own, so you sitll have one variable pointing at the recordset.
once you are done with your new variable (rsRecordFromFunction) you can set that = Nothing and now none of your variables will be pointing at the recordset anymore and it can be cleaned up from memory.

So in a nutshell:
After the final assignment in the function where you assign the recordset as the return value, go ahead and set your original recordset refrence (rsRecord) = nothing. After your done using the recordset outside of the function, set that one to nothing as well.

Set Connection = Nothing
Now, the connection object could be a bit of a problem, unless we disconnect the recordset. Basically you will want to do this in the funciton to reduce the amount of time you hold the connection open.
The first step to disconnecting your recordset is to set the recordsets .ActiveConnection to Nothing. Basically we're simultaneously disconnecting the recordset from the open connection and reducing the number of variables that are pointing at the connection object.
The next step is to call the .Close method on your connection object and set it to nothing.
Now you have a recordset that is not connected to an active connection to the database, and you have safely closed that connection to reduce resource usage.

Other Tips:
I would suggest using the connection object to generate the recordset instead of recordset.Open. Connection.Execute is slightly more efficient.

Perhaps the most efficient way to get data and loop through it is to use te GetRows() method of the Recordset object, which returns all of the data from the recordset as an array of values. This allows you to get rid of the recordset even earlier, as well as having the benefit of dealing with an array, which is much faster than looping with the ADO recordset object. There isn't much advantage to this with a small number of records, but with larger sets of data I have seen 7.5-11 times better performance using GetRows()

Sidenote:
If you click on the Process TGML link above the "Submit Post" button, you will see examples of many of the tags available for marking up your posts. One of thoe is the code tags, which is a lot easier to read than bolding the code.

Sidenote #2:
Yes I could have posted the code to resolve your problem, but I prefer to explain why a solution will work rather than just giving people solutions. No doubt someone will be along shortly who will read my thread, apply it to your code, and post the answer. So if you weren't interested in fixing it yourself, just wait around. If you were interested in knowing the why of it, rather than just the answer, i hope my (insanely long) post cleared some of that up for you.

-T