CODEWITHSUNDEEP
sql-serverasp-classic
omega
omega

Reputation: 43833

"Operation is not allowed when the object is closed" when executing stored procedure

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)

Upvotes: 30

Views: 95767

Answers (7)

Greg Sipes
Greg Sipes

Reputation: 703

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.

Upvotes: 0

banavalikar
banavalikar

Reputation: 282

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.

Upvotes: 0

fizch
fizch

Reputation: 2609

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.

Upvotes: 0

Sergio Abreu
Sergio Abreu

Reputation: 2889

Warnings may confuse the result. SET ANSI_WARNINGS OFF avoids losing the SELECT result or output parameter values.

Upvotes: 1

Ramon
Ramon

Reputation: 1036

Try this in your stored procedure:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Right below the AS.

Upvotes: 78

user3158212
user3158212

Reputation: 525

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

Upvotes: 4

johna
johna

Reputation: 10752

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)

Upvotes: 1

Related Questions