If I call a function to create a recordset (eg. based on criteria I pass 

to it), and return that recordset back to the calling command, like:



function getdata(criteria)

  [create objRS and query db to get data]

  set getdata = objRS

end function



[in body of asp page]

set objData = getdata(criteria)



...then does objRS automatically get closed?  If I close objRS in the 

function I cannot access the recordset objData.

Or, should I use " set getdata = objRS.Clone " in the function and then 

objRS.Close?

Don't close the recordset in your function. You'll need an open recordset 

in your calling page, so have the function return an open recordset and 

close that in the calling page.



AFAIK, this is caused by the fact that both variables for the recordset are 

pointing to the same recordset. Since you are using Set, both 

Objectvariables (the return value of getdata and objRS) are pointing to the 

same object / recordset.



Here is a short example. I happen to use the Execute method of a Command 

object to create the Recordset (in line 3). This is just an example: the 

Open method of a Recordset object would work equally well.



Function GetRecordset (byVal sMySQLStatement)

         ' Create a command object (objCommand) and a connection (omitted)

         Dim tempRS ' as ADODB.Recordset

         Set tempRS = objCommand.Execute

         Set GetRecordset = tempRS

'       tempRS.Close

End function



You would expect that when in line 4 you assign the recordset to the 

returnvalue of the function, tempRS is no longer needed. So you should be 

able to close it in line 5. However, after line 4, both the returnvalue of 

the function as tempRS are pointing to the same recordset in memory. So if 

you close tempRS you are also closing GetRecordset. If you uncomment line 

5, you will receive an error in the calling page.



Here is how to do it:



<include file.asp>

         Function GetRecordset (byVal sMySQLStatement)

                 ' Create a command object (objCommand) and a connection 

(omitted)

                 Dim tempRS ' as ADODB.Recordset

                 Set tempRS = objCommand.Execute

                 Set GetRecordset = tempRS

         End Function

</include file.asp>



<calling page.asp>

         Dim rsWhatever ' as ADODB.Recordset

         Set rsWhatever = GetRecordset ("SELECT field1, field2, field3 FROM 

SomeTable)

         if not rsWhatever.EOF then

                 ' Do something with recordset here

         end if

         rsWhatever.Close

         Set rsWhatever = Nothing

</calling page.asp>



What you can do to save some resources, is disconnect the recordset as soon 

as possible, inside the function.

So you could do this:



         Set tempRS = objCommand.Execute

         Set tempRS.ActiveConnection = Nothing

         Set GetRecordset = tempRS



This will only work with recordsets that have a ClientSide cursor. Since 

the default cursorlocation for a recordset is ServerSide, you'll have to 

manually change that. This means that you cannot use .Execute, as this will 

always return a ServerSide cursor, even if you have explicitly set it to 

ClientSide first.



A completely different solution is to do all your data-access inside the 

function, and use .GetRows() to return an array. Then inside the function 

you can close and clean up all your connection, command and recordset 

objects, and use the array to display the content in the calling page.



Hope this helps,



Imar









At 02:47 AM 3/24/2001 +0000, you wrote:

>If I call a function to create a recordset (eg. based on criteria I pass

>to it), and return that recordset back to the calling command, like:

>

>function getdata(criteria)

>   [create objRS and query db to get data]

>   set getdata = objRS

>end function

>

>[in body of asp page]

>set objData = getdata(criteria)

>

>...then does objRS automatically get closed?  If I close objRS in the

>function I cannot access the recordset objData.

>Or, should I use " set getdata = objRS.Clone " in the function and then

>objRS.Close?