Why can't I return a recordset using OLEDB in a stored procedure?
I once wrote a program that operates on a temporary table (the main function is to retrieve the top 10 records of each category).
The stored procedure uses ODBC to connect to the database normally and can obtain correct results. But when using oledb to connect, the record set cannot be returned, and once the operation returns the record set, an error message appears:
ADODB.Recordset error '800a0e78'
The operation requested by the application is not allowed if the object is closed.
It baffles me. why is that?
This is because there is a difference between OLEDB and ODBC, that is, when ASP obtains the record set from ODBC, ODBC filters out the records created by create table.
Or insert into generates some record sets that only occupy positions but cannot perform any operations. When ASP obtains the record set from OLEDB, OLEDB does not
These recordsets are not filtered out. Therefore, we should execute set nocount on before returning the recordset in the stored procedure.
Stored procedures are prohibited from returning recordsets; to return recordsets, set nocount off must be executed first. In this way, the problem will be solved.