February 6, 2004 at 10:09 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 10:59 am
The .ActiveConnection needs to be an open ADODB.Connection object. You have to explicitly open a connection and that seems to be what's missing. Something akin to...
.
.
.
Dim oConn
Dim strConnection
Dim oCmd
strConn = "<connectionstring>"
set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionString = strConnection
oConn.Open
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
.
.
.
K. Brian Kelley
@kbriankelley
February 6, 2004 at 11:59 am
Thanks but I tried already that and it had no effact. Besides, if I didn't have or couldn't create a valid connection I would have gotten an error message on the Exec - not on the line following.
Gene
February 6, 2004 at 12:04 pm
Run a Profiler trace to see the connection being created and the stored procedure being executed. Add Exceptions & Warnings to see if the stored procedure is throwing an exception when it is executed.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply