This is my stored procedure, and when I am calling it from my classic ASP code, I am getting the error:
Operation is not allowed when the object is closed.
when I try to do a record count.
Does anyone know what is wrong here?
I am trying to return the table @t.
Thanks.
USE [Hires_new] GO /****** Object: StoredProcedure [dbo].[sp_selectNewHireWorkPeriodsSQL] Script Date: 05/13/2013 14:04:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER PROCEDURE [dbo].[sp_selectNewHireWorkPeriodsSQL] -- Add the parameters for the stored procedure here AS declare @t table (HireID int, StartDate datetime, EndDate datetime, date_initiated datetime, date_closed datetime, firmName nvarchar(100), InquiryID int) DECLARE @acc INT SET @acc = 1 DECLARE @max INT select @max = max(HireID) from NewHire WHILE (@acc <= @max) BEGIN IF (@acc in (select HireID from NewHire)) BEGIN insert into @t select HireID, StartDate, EndDate, date_initiated, date_closed, firmName, Inquiries.InquiryID from WorkPeriod, Firms, Inquiries where HireID = @acc and WorkPeriod.FirmID = Firms.FirmID and WorkPeriod.InquiryID = Inquiries.InquiryID order by HireID,StartDate DESC END set @acc = @acc + 1 END select * from @t Asp classic code
selectNewHireWorkPeriodsSQL = "EXEC sp_selectNewHireWorkPeriodsSQL" Set rsNewHireWorkPeriods = Server.CreateObject("ADODB.Recordset") rsNewHireWorkPeriods.Open selectNewHireWorkPeriodsSQL,ConnectionString,adOpenStatic NumOfNewHireWorkPeriods = rsNewHireWorkPeriods.RecordCount response.write(NumOfNewHireWorkPeriods) 7 Answers
Try this in your stored procedure:
SET NOCOUNT ON SET ANSI_WARNINGS OFF Right below the AS.
If, for whatever reason the stored procedure does not return a result set, empty or otherwise, the recordset object will not be open, so:
if rs.state = adStateOpen then x = rs.recordcount You need to create an active connection first, and pass this to the recordset object, like this:
Set conn = Server.CreateObject("ADODB.Connection") conn.Open(ConnectionString) selectNewHireWorkPeriodsSQL = "EXEC sp_selectNewHireWorkPeriodsSQL" Set rsNewHireWorkPeriods = Server.CreateObject("ADODB.Recordset") rsNewHireWorkPeriods.Open selectNewHireWorkPeriodsSQL,conn,adOpenStatic 'dont use connection string here NumOfNewHireWorkPeriods = rsNewHireWorkPeriods.RecordCount conn.Close Set conn = Nothing response.write(NumOfNewHireWorkPeriods) Warnings may confuse the result. SET ANSI_WARNINGS OFF avoids losing the SELECT result or output parameter values.
I am sure that this will not affect many people, but I just stumbled upon this issue. This was working in production and not in the development environment. What I found was that our stored procedure had a print statement in the development environment. I guess the print statement was mucking up the works and ADODB thought that was the record set.
I know that this is very old. But in my case, it was the order of parameters. It worked after I set the parameters as they appear in the stored procedure. I know that there is no logical explanation to this as parameters are named and the order should not matter really.
This can be caused by a print statement in your stored procedure. I accidently left a few in after some performance debugging....hopefully this helps someone still working in legacy ADO.
