Still having issues with ADO objects to work with Stored Procedures

[BG12424]

I followed this procedure line for line but when I got to the "while not rs.EOF" line of code, I get the message that "Operation is not allowed when the object is closed." This is really what I have been getting as an error all along. Is there any insight on this? Thanks

This posting is exactly what I am trying to accomplish. I tried this code line-for-line and get the same error message (different stored procedure though, which is below). Link is:

http://msdn.microsoft.com/library/en-us/adosql/adoprg02_525v.asp

I am including the stored procedure code. Maybe it is something with the procedure.

Is there a chance that this procedure is not finished executing by the time I am checking for rs.EOF?

ASP Function:
function WV_CalculateMargin(userid)

scrub userid,0

dim cmd, rs, dbConn, retval

Set dbConn = Server.CreateObject("ADODB.Connection&quot
Set rs = Server.CreateObject("ADODB.Recordset&quot
Set cmd = Server.CreateObject("ADODB.Command&quot

dim strConn1
strConn1 = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;Password=flibble;Initial Catalog=WV_DEV;Network Address=GAINES-XE3-W2K"
dbConn.Open strConn1

with cmd
.ActiveConnection = dbConn
.CommandText = "WVCalculateMargin"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Return", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@userID",adInteger,adParamInput, ,userid)
retval = .Parameters("Return&quot
end with

Set rs = cmd.Execute

dim i
while not rs.EOF
for each fldloop in rs.fields
Response.Write(rs.fields(i & &quot;<BR>&quot)
i = i + 1
next
i = 0
rs.movenext
wend

rs.close
CloseDBConn(dbConn)

end function


Stored Procedure:

CREATE Procedure WVCalculateMargin

(
@userID INT
)

As

DECLARE @LOGMSG VARCHAR(255)
DECLARE @pendingmargin MONEY
DECLARE @openMargin MONEY
DECLARE @quantity INT
DECLARE @openQuant INT
DECLARE @commodityID INT
DECLARE @orderID INT
DECLARE @isCancelledBy INT
DECLARE @pendingAction INT
DECLARE @pendMonths INT
DECLARE @openMonths INT
DECLARE @fullSym VARCHAR
DECLARE @actionID INT
DECLARE @reverseQuant INT
DECLARE @profit MONEY
DECLARE @balance MONEY
DECLARE @total_margin MONEY
DECLARE @marginFactor FLOAT

IF NOT EXISTS (SELECT * FROM USERS WHERE ID=@userID)
BEGIN
/*===LOG ENTRY===*/
SELECT @LOGMSG='Error calculating margin. Requested user does not exist.'
EXEC WVLogUserEvent 4, @LOGMSG, @userID
RETURN 1 /*Invalid user ID*/
END

SET @pendingmargin = 0
SELECT @marginFactor = MARGIN_FACTOR FROM USERS WHERE ID = @userID

/*Get any margin from pending or working orders */
IF EXISTS (SELECT * FROM ORDERS WHERE FK_USER=@userID AND
(FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'PENDING') OR
FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'WORKING') OR
FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'ACCEPTED') ) )
BEGIN
PRINT 'USER '+CAST(@userID AS VARCHAR)+ ' has a pending order'
DECLARE ORDER_CURSOR CURSOR FOR
SELECT ID FROM ORDERS
WHERE FK_USER=@userID AND FK_STATUS =
(SELECT ID FROM ORDER_STATUS WHERE STATUS = 'PENDING') OR
FK_USER = @userID AND FK_STATUS =
(SELECT ID FROM ORDER_STATUS WHERE STATUS = 'WORKING') OR
FK_USER = @userID AND FK_STATUS =
(SELECT ID FROM ORDER_STATUS WHERE STATUS = 'ACCEPTED')
FOR READ ONLY
OPEN ORDER_CURSOR
FETCH NEXT FROM ORDER_CURSOR INTO @orderID
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @pendingAction = FK_ACTION FROM ORDER_DETAILS WHERE FK_ORDER = @orderID
SELECT @quantity = QUANTITY FROM ORDER_DETAILS WHERE FK_ORDER =@orderID
SELECT @commodityID = FK_COMMODITY FROM ORDER_DETAILS WHERE FK_ORDER = @orderID
SELECT @isCancelledBy = CANCELLED_BY FROM ORDERS WHERE ID = @orderID
SELECT @pendMonths = MONTHS FROM ORDER_DETAILS WHERE FK_ORDER = @orderID
/* If the order is an oco or a modified order just use 1/2 of real margin */
IF ISNULL(@isCancelledBy,-1) > 0
BEGIN
SELECT @pendingmargin = @pendingmargin + (SELECT MARGIN FROM COMMODITIES WHERE ID = @commodityID) *@quantity * 0.5
PRINT 'Pending margin for OCO orderID ' + cast(@orderID as varchar) + ' is ' + CAST(@pendingmargin as varchar)
END
ELSE
BEGIN
SELECT @pendingmargin = @pendingmargin + (SELECT MARGIN FROM COMMODITIES WHERE ID = @commodityID) *@quantity
PRINT 'Pending margin for orderID ' + cast(@orderID as varchar) + ' is ' + CAST(@pendingmargin as varchar)
END

FETCH NEXT FROM ORDER_CURSOR INTO @orderID
END
SELECT @pendingmargin = @pendingmargin * @marginfactor
PRINT 'MARGIN FROM PENDING, WORKING AND ACCEPTED ORDERS =' + CAST(@pendingmargin as varchar)
CLOSE ORDER_CURSOR
DEALLOCATE ORDER_CURSOR
END


SET @openMargin = 0
IF EXISTS(SELECT PID FROM VW_OPEN_POSITIONS WHERE UID = @userID)
BEGIN
SELECT @openMargin = SUM(MARGIN) FROM VW_OPEN_POSITIONS WHERE UID = @userID
/* Loop through each commodity the use has an open positions for */
DECLARE COMMOD_CURSOR CURSOR FOR
SELECT DISTINCT CID, ACTID,FULLSYM, COUNT(PID) FROM VW_OPEN_POSITIONS
WHERE UID=@userID GROUP BY CID, ACTID, FULLSYM FOR READ ONLY
OPEN COMMOD_CURSOR
FETCH NEXT FROM COMMOD_CURSOR INTO @commodityID, @actionID, @fullSym, @openQuant
WHILE @@FETCH_STATUS=0
BEGIN
/* Look to see if there is a pending position in the commodity */
IF @actionID = (SELECT ID FROM ACTIONS WHERE ACTION = 'B')
BEGIN
PRINT 'There is an open buy position'
SELECT @reverseQuant = SUM(ORDER_DETAILS.QUANTITY) FROM ORDERS, ORDER_DETAILS WHERE FK_USER = @userID AND
(FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'PENDING')
OR FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'WORKING')
OR FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'ACCEPTED') ) AND
ORDER_DETAILS.FK_ORDER = ORDERS.ID AND
FK_COMMODITY = @commodityID AND
ORDER_DETAILS.FK_ACTION = (SELECT ID FROM ACTIONS WHERE ACTION = 'S')
END
ELSE IF @actionID = (SELECT ID FROM ACTIONS WHERE ACTION = 'S')
BEGIN
SELECT @reverseQuant = SUM(ORDER_DETAILS.QUANTITY) FROM ORDERS, ORDER_DETAILS WHERE FK_USER = @userID AND
(FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'PENDING')
OR FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'WORKING')
OR FK_STATUS = (SELECT ID FROM ORDER_STATUS WHERE STATUS = 'ACCEPTED') ) AND
ORDER_DETAILS.FK_ORDER = ORDERS.ID AND
FK_COMMODITY = @commodityID AND
ORDER_DETAILS.FK_ACTION = (SELECT ID FROM ACTIONS WHERE ACTION = 'B')
END
PRINT 'Pending positions in commodity ' + cast(@commodityID as varchar) + ' is '+ cast(isnull(@reverseQuant,0) as varchar)
IF ISNULL(@reverseQuant,-1) > 0
BEGIN
/* Get the quantity of open positions in the commodity */
PRINT 'Adjusting margin, original openMargin = ' + cast(@openMargin as varchar)
IF @reverseQuant <= @openQuant
BEGIN
SELECT @openMargin = @openMargin - (SELECT MARGIN FROM COMMODITIES WHERE ID = @commodityID) * @reverseQuant
PRINT 'Adjusting margin, adjusted openMargin = ' + cast(@openMargin as varchar)
END
ELSE
BEGIN
SELECT @openMargin = @openMargin - (SELECT MARGIN FROM COMMODITIES WHERE ID = @commodityID) * @openQuant
PRINT 'Adjusting margin, adjusted openMargin = ' + cast(@openMargin as varchar)
END
END

FETCH NEXT FROM COMMOD_CURSOR INTO @commodityID, @actionID, @fullSym, @openQuant
END
SELECT @openmargin = @openmargin * @marginFactor
CLOSE COMMOD_CURSOR
DEALLOCATE COMMOD_CURSOR
END


/*Get the users account balance */
SELECT @balance = BALANCE FROM ACCOUNTS WHERE ID = (SELECT FK_ACCOUNT FROM USERS WHERE ID = @userID)

/* Get the profit for the user */
SELECT @profit = SUM(CASE WHEN ACTION = 'B' THEN CURR_PRICE - FILLED_PRICE
WHEN ACTION = 'S' THEN FILLED_PRICE - CURR_PRICE ELSE 0
END / (COMMODITIES.MINIMUM_TICK * COMMODITIES.TICKS_PER_HANDLE)
* COMMODITIES.TICK_VALUE)
FROM VW_OPEN_POSITIONS, COMMODITIES
WHERE
VW_OPEN_POSITIONS.CID = COMMODITIES.ID AND
VW_OPEN_POSITIONS.UID = @userID

print 'profit: ' + cast(@profit as varchar)
/* Get the total margin for the user */
SELECT @total_margin = SUM(MARGIN) FROM VW_OPEN_POSITIONS WHERE UID = @userID
SELECT @total_margin = @total_margin * @marginFactor

SELECT @userID AS ID, @balance AS BALANCE, @balance + ISNULL(@profit,0) AS EQUITY, ISNULL(@total_margin,0) AS MARGIN, ISNULL(@profit,0) AS PROFIT, @balance - @pendingMargin - @openMargin as TRADING_POWER

/*===LOG ENTRY===*/
/*SELECT @LOGMSG='Margin Calculated.'
EXEC WVLogUserEvent 3, @LOGMSG, @userID
*/

RETURN 0 regards,
Brian