Classic ASP stored procedure return status

Question

I want to delete an item from a database based on a button that is clicked on a webpage. A user can choose to delete items from their account.

I pass the account number and item number through to a stored procedure. The stored procedure deletes the item if it exists, and returns either 1 for success or 0 if there was an error.

My problem is that the ASP page when submitted constantly returns an error:

ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed.

If I submit the stored procedure directly, I can delete the item and a record is created where status is 1. If it is not deleted, record has status as 0.

Therefore, there should always be a recordset created that has status either 1 or 0.

Here is the stored procedure:

CREATE PROCEDURE dbo.SAVED_ITEM_DELETE
   @AccountID int,
   @Item int
AS
BEGIN
   DECLARE @Status INT

   SET @Status = 1       

   IF EXISTS(SELECT AccountID FROM Accounts_Items 
             WHERE AccountID = @AccountID AND Item = @Item)
   BEGIN TRY                
       DELETE Account_Items 
       WHERE AccountID = @AccountID AND Item = @Item               
   END TRY
   BEGIN CATCH
       SET @status = 0
   END CATCH    

   SELECT @Status AS Status
END

And here is the ASP that calls the procedure

dim de
Set de = Server.CreateObject("ADODB.recordset")
de.open "SAVED_ITEM_DELETE 1,49", connect,2

IF not de.EOF THEN
    IF de("Status") = 1 THEN
        response.write request("delete") & " - deleted"
    ELSE
        response.write request("delete") & " - not deleted"
    END IF
END IF

I am getting the error on the line:

IF NOT de.EOF THEN

Please, please, please help! I just cannot fathom why ASP doesn't see a recordset with a field called Status that's either 1 or 0.

Why do you need a recordset anyway? use an output parameter instead... — Zohar Peled
I've not heard of that before. Can you explain? Thanks for replying. — TVRV8S
The sql is very simple - you just change the @status from a variable to a parameter and add the keyword output, I really can't remember, though, how to get it back to adoddbo.Command. It's been over a decade since I last used asp3. — Zohar Peled
...or use RETURN as decribed here: msdn.microsoft.com/en-us/library/ms174998.aspx — Foxbox

schudel schudel · Accepted Answer · 2016-07-09T14:57:23

If you would like to go with the version you started, you could try using the nocount option to ensure the select is the first result sent to your recordset. Also there is probably a typo as you are using Accounts_Items and Account_Items as table name.

CREATE PROCEDURE dbo.SAVED_ITEM_DELETE
   @AccountID int,
   @Item int
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @Status INT

   SET @Status = 1       

   IF EXISTS(SELECT AccountID FROM Accounts_Items 
             WHERE AccountID = @AccountID AND Item = @Item)
   BEGIN TRY                
       DELETE Accounts_Items 
       WHERE AccountID = @AccountID AND Item = @Item               
   END TRY
   BEGIN CATCH
       SET @status = 0
   END CATCH    

   SET NOCOUNT OFF
   SELECT @Status AS Status
END

Classic ASP stored procedure return status

1 Answers