"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...
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.
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
