Classic ASP, ADO, recordset not populating from Stored Procedure

Question

In a Classic ASP script, I have a sql statement that executes a stored procedure:

sql="exec my_stored_proc"

I then attempt to fill an ADO Recordset object with the results and loop through the recordset.

The stored procedure itself is populating a virtual table and the last line of the procedure returns the results:

select tableName, subTable, FieldLabel, total, FieldOrder from @AdmissionsTable

When executed directly from SQL management studio, I get results, but when executed from the ASP page, this line:

do while not rs.eof

results in the error "Operation is not allowed when the object is closed."

I know it's not because I'm trying to execute a stored proc, because I tested it against another stored proc, and was able to get results into the rs object.

Is it because I'm using a virtual table? Or something else?

Extra code:

After the sql string is assigned I open the connection, set the rs object, and execute the query:

conn.Open strConnection
Set rs = server.createobject("ADODB.Recordset")
rs.open sql, conn

Then comes the error line:

do while not rs.eof

Further update:

Right after the rs.open line, I added some debug code to response.write "RS Populated," and this line executes. But then the error still appears directly after that, so I'm guessing this means that somehow the RS is getting closed right away. Not sure why.

Still further update:

I've at least isolated the problem. The err object returns "Query timeout" as the error, and rs.ActiveConnection.CommandTimeout returns "30" as my timeout value.

The query is large and takes at least 60 seconds to run, so I tried setting rs.ActiveConnection.CommandTimeout = 120, but it still fails and - most maddeningly - it still returns "30" as the timeout value.

If rs.ActiveConnection.CommandTimeout = 120 didn't work to increase the timeout, how can I get that value up?

Can you show us the rest of your code? Where do you open the connection, or populate rs? — Lynn Crumbling
Do you have an On Error Resume Next somewhere in your code? — Lynn Crumbling
When are you trying to set rs.ActiveConnection.CommandTimeout? — Lynn Crumbling

JMax2012 JMax2012 · Accepted Answer · 2013-08-01T16:01:03

Problem solved. I had to set the CommandTimeout property at the connection level:

conn.CommandTimeout=120

Now it works.

Classic ASP, ADO, recordset not populating from Stored Procedure

1 Answers