February 6, 2004 at 7:58 am
I have the following stored procedure. It extracts information from 2 tables, based on a provided date for a specified number of days. It then does some simple calculations and inserts the result into a memory table. The memory table is returned. When I call this from Enterprise Manager or the Query Analyzer it returns a recordset with the correct results.
The problem is when calling the proc from ASP or VB any attempt to access the recordset get “Operation cannot be performed on a closed object.” I get no error messages up to and through the execute/open – only the access FOLLOWING the open. I stepped through the VB code watching the command object and everything seems to be setup properly.
Following the stored procedure code is the ASP and VB code.
********* STORED PROCEDURE **************************************
*****************************************************************
CREATE PROCEDURE [dbo].[sp_GetFoodService]
@StartDate Datetime,
@Days Int
AS
DECLARE @@MaxMeals Int
DECLARE @@WorkDate Datetime
DECLARE @@FoodTable Table(CurDate Datetime, Breakfast Int, Lunch Int, Kosher Int)
DECLARE @@Breakfast Int
DECLARE @@Lunch Int
DECLARE @@Kosher Int
DECLARE @@BFInd bit
DECLARE @@LInd bit
DECLARE @@KCount Int
DECLARE @@NAttend Int
DECLARE @@Junk varchar(50)
BEGIN
SELECT @@MaxMeals = OptionValue FROM tblOptions WHERE OptionID = 'MaxMeals'
If @@ERROR <> 0
BEGIN
PRINT 'Unable to read max number of meals'
RETURN -1
END
Set @@WorkDate = @StartDate
WHILE @Days > 0
BEGIN
Set @@Breakfast = 0
Set @@Lunch = 0
Set @@Kosher = 0
DECLARE WorkCursor Cursor For
SELECT dbo.tbl1general.EventID, dbo.tbl1general.StaffContact, dbo.tbl1general.NumbofAttendees, dbo.tbl1General3.Breakfast,
dbo.tbl1General3.Lunch, dbo.tbl1General3.Kosher, dbo.tbl1general.Date1, dbo.tbl1general.Date2,
dbo.tbl1general.Date3
FROM dbo.tbl1general INNER JOIN
dbo.tbl1General3 ON dbo.tbl1general.EventID = dbo.tbl1General3.EventID
WHERE (dbo.tbl1general.Canceled = 0) AND (dbo.tbl1General3.Breakfast = 1) AND (dbo.tbl1general.Date1 = @@WorkDate) OR
(dbo.tbl1general.Canceled = 0) AND (dbo.tbl1General3.Breakfast = 1) AND (dbo.tbl1general.Date2 = @@WorkDate) OR
(dbo.tbl1general.Canceled = 0) AND (dbo.tbl1General3.Breakfast = 1) AND (dbo.tbl1general.Date3 = @@WorkDate) OR
(dbo.tbl1general.Canceled = 0) AND (dbo.tbl1General3.Lunch = 1) AND (dbo.tbl1general.Date1 = @@WorkDate) OR
(dbo.tbl1general.Canceled = 0) AND (dbo.tbl1General3.Lunch = 1) AND (dbo.tbl1general.Date2 = @@WorkDate) OR
(dbo.tbl1general.Canceled = 0) AND (dbo.tbl1General3.Lunch = 1) AND (dbo.tbl1general.Date3 = @@WorkDate)
ORDER BY dbo.tbl1general.EventID
OPEN WorkCursor
If @@ERROR <> 0
BEGIN
PRINT 'Error accessing food service request counts'
RETURN -2
END
FETCH NEXT FROM WorkCursor into @@Junk, @@Junk, @@NAttend, @@BFInd, @@LInd, @@KCount, @@Junk, @@Junk, @@Junk
If @@ERROR <> 0
BEGIN
PRINT 'Error accessing food service request counts'
RETURN -2
END
WHILE @@FETCH_STATUS = 0
BEGIN
If @@BFInd = 1
Begin
Set @@Breakfast = @@Breakfast + @@NAttend
End
If @@LInd = 1
Begin
Set @@Lunch = @@Lunch + @@NAttend
End
Set @@Kosher = @@Kosher + @@KCount
FETCH NEXT FROM WorkCursor into @@Junk, @@Junk, @@NAttend, @@BFInd, @@Lind, @@KCount, @@Junk,@@ Junk, @@Junk
If @@ERROR <> 0
BEGIN
PRINT 'Error accessing food service request counts'
RETURN -2
END
END
INSERT INTO @@FoodTable VALUES(@@WorkDate, @@Breakfast, @@Lunch, @@Kosher)
If @@ERROR <> 0
BEGIN
PRINT 'Error saving food service request counts'
RETURN -3
END
CLOSE WorkCursor
DEALLOCATE WorkCursor
Set @@WorkDate = DateAdd(Day, 1, @@WorkDate)
Set @Days = @Days - 1
END
SELECT * FROM @@FoodTable
RETURN @@MaxMeals
END
GO
********** ASP CODE USING A COMMAND OBJECT ***********************
******************************************************************
<%
dim cmnd
dim rsFoodCount
dim nMaxFood
Set cmnd = Server.CreateObject("ADODB.Command")
cmnd.ActiveConnection = "a long valid connection string"
cmnd.CommandText = "sp_GetFoodService"
cmnd.Parameters("@StartDate") = '2/27/2004'
cmnd.Parameters("@Days") = 3
Set rsFoodCount = cmnd.Execute
nMaxFood = cmnd.Parameters(0)
%>
********** ASP CODE USING JUST A RECORDSET ***********************
******************************************************************
<%
dim rsFoodCount
Set rsFoodCount = Server.CreateObject("ADODB.RecordSet")
rsFoodCount.Open "sp_GetFoodService '2/7/2004', 3","a long valid connection string",0,1, 1
%>
********** VB CODE ***********************************************
******************************************************************
Private Sub Command1_Click()
Dim cmnd As ADODB.Command
Dim rsFoodCount As ADODB.Recordset
Dim nMaxFood As Integer
Set cmnd = New ADODB.Command
cmnd.ActiveConnection = "a long valid connection string"
cmnd.CommandType = 4 'adCmdStoredProc
cmnd.CommandText = "sp_GetFoodService"
cmnd.Parameters("@StartDate") = "
cmnd.Parameters("@Days") = 3
Set rsFoodCount = cmnd.Execute
nMaxFood = cmnd.Parameters(0)
End Sub
February 6, 2004 at 9:09 am
I believe the problem is that your parameters are not being created properly. Try using the following lines of code in place of the two "cmnd.Parameters" lines of code in VB example:
cmnd.Parameters.Append cmnd.CreateParameter("@StartDate", adDateTime, adParamInput, , "2/7/2004")
cmnd.Parameters.Append cmnd.CreateParameter("@Days", adInteger, adParamInput, , '3')
You may also want to add a recordset to execute the stored procedure:
Set objrs = CreateObject("ADODB.Recordset")
objrs.Open cmnd
February 6, 2004 at 9:57 am
Thanks, but I had tried both of these. I tried them again just to be sure and still got the same "Operation is not allowed when the object is closed." message
February 6, 2004 at 10:25 am
Try using the following line of code:
rsFoodCount.Open cmnd
in place of the following line:
Set rsFoodCount = cmnd.Execute
February 6, 2004 at 12:04 pm
Thanks, but I already tried that one - no effect.
Gene
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply