Return recordset from function in classic ASP

Question

Hi, I'm at a loss on how I can return a readable recordset from a function in classic ASP.

This is what I came up with, but it's not working:

Response.Clear
Response.CharSet = "utf-8"
Response.ContentType = "text/plain"

Dim Count

Set Count = Test

Response.Write Count.Fields(0).Value


Function Test

    Dim Query, Connection, Command, Recordset

    Query = " blah blah blah "

    Set Connection = Server.CreateObject("ADODB.Connection")
    Set Command = Server.CreateObject("ADODB.Command")
    Set Recordset = Server.CreateObject("ADODB.Recordset")

    Connection.ConnectionString = "blah blah blah"
    Connection.Open

    Set Command.ActiveConnection = Connection
    Command.CommandText = Query

    Set Recordset = Command.Execute

    Set Test = Recordset

    Recordset.Close
    Connection.Close

    Set Recordset = Nothing
    Set Command = Nothing
    Set Connection = Nothing

End Function

The Response.Write Count.Fields(0).Value line yields the Item cannot be found in the collection corresponding to the requested name or ordinal. error.

Replacing it with Response.Write Count.Status I get the Operation is not allowed when the object is closed. error.

Adding Count.Open gives the The connection cannot be used to perform this operation. It is either closed or invalid in this context. error.

Edit after Mark B's answer:

I already looked at disconnected recordsets but I don't know how to use them in my example: every tutorial feeds the query directly into the recordset with Recordset.Open, but I'm using parametrized queries, and even trying many ways I couldn't obtain the same result when there's an ADODB.Command in the way.

What should I do?

Thanks in advance.

Here's the solution based on Eduardo Molteni's answer:

The function which interacts with the database:

Function Test

    Dim Connection, Command, Recordset

    Set Connection = Server.CreateObject("ADODB.Connection")
    Set Command = Server.CreateObject("ADODB.Command")
    Set Recordset = Server.CreateObject("ADODB.Recordset")

    Connection.ConnectionString = "blah blah blah"
    Connection.Open

    Command.ActiveConnection = Connection
    Command.CommandText = "blah blah blah"

    Recordset.CursorLocation = adUseClient
    Recordset.Open Command, , adOpenForwardOnly, adLockReadOnly

    Set Recordset.ActiveConnection = Nothing

    Set Test = Recordset

    Connection.Close

    Set Recordset = Nothing
    Set Command = Nothing
    Set Connection = Nothing

End Function

The code which calls the function:

Response.Clear
Response.CharSet = "utf-8"
Response.ContentType = "text/plain"

Dim Recordset

Set Recordset = Test

Response.Write Recordset.Fields(0).Value

Recordset.Close

Set Recordset = Nothing

Answer 1

Well, you are closing the recordset and connection immediately after setting the function's return variable, so that explains the error messages.

I am not a VB developer, but I think what you need to look at is Disconnected Recordsets. Take a look at this article, it's doing pretty much exactly what you want.

Answer 2

Here's a function that returns a disconnected recordset

Function RunSQLReturnRS(sqlstmt, params())
    On Error Resume next

    ''//Create the ADO objects
    Dim rs , cmd
    Set rs = server.createobject("ADODB.Recordset")
    Set cmd = server.createobject("ADODB.Command")

    ''//Init the ADO objects  & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = sqlstmt
    cmd.CommandType = adCmdText
    cmd.CommandTimeout = 900 

    ''// propietary function that put params in the cmd
    collectParams cmd, params

    ''//Execute the query for readonly
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
    If err.number > 0 then
        BuildErrorMessage()
        exit function
    end if

    ''// Disconnect the recordset
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
    Set rs.ActiveConnection = Nothing

    ''// Return the resultant recordset
    Set RunSQLReturnRS = rs

End Function