Returns a recordset from a function in classic ASP

Question

I do not understand how I can return a readable record set from a function in classic ASP.

Here is what I came up with, but it does not work:

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 

String Response.Write Count.Fields(0).Value gives an error Item cannot be found in the collection corresponding to the requested name or ordinal. .

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

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

Edit after Mark B's answer:

I have already considered disabled record sets, but I don’t know how to use them in my example: each tutorial passes the query directly to the record set using Recordset.Open , but I use parameterized queries and even try many ways I could not get the same result when in the way of ADODB.Command .

What should I do?

Thanks in advance.

Here's a solution based on Eduardo Molteni, responding:

Function that 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 

Code calling 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

Here is a function that returns a disabled 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 

Answer 2

Well, you close the recordset and connection right after setting the return function to explain error messages.

I'm not a VB developer, but I think you need to look, these are Disconnected Recordsets. Take a look at this article , it does practically what you want.