• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Pass Return Code to ASP Page

below is my stored procedure. what i want to do is to return the value 1 or 0 to an asp page. How can i pass the value 1 or 0 to an ASP page?

CREATE PROCEDURE DoesRecordExist
   @pid int
AS

IF EXISTS(SELECT * FROM BasketItems WHERE ProductID = @pid)
   RETURN (1)
ELSE
   RETURN (0)
GO

thanks
0
jamaje
Asked:
jamaje
2 Solutions
 
SQLMasterCommented:
Hi,

Instead of using a stored procedure, the best way will be to use a User Defined Function and catch the return value in the  recordset that will be passed to ASP page

Thanks
0
 
Anthony PerkinsCommented:
Something like this (untested):
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   .ActiveConnection = "your connection string goes here"
   .CommandText = "DoesRecordExist"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
   .Parameters.Append .CreateParameter("@pid", adInteger, adParamInput, 0,YourInputValueGoesHere)
   .Execute , , adExecuteNoRecords
   Response.Write .Parameters("@RETURN_VALUE").Value
End With
Set cmd = Nothing

Make sure you are including all the ADO constants (use adovbs.inc) or replace them with their corresponding values.

Anthony
0
 
fromapuCommented:
hi
Let me modify the stored procedure a bit.

CREATE PROCEDURE DoesRecordExist
   @pid int,
   @Return Smallint Output
AS

IF EXISTS(SELECT * FROM BasketItems WHERE ProductID = @pid)
  Select @RETURN = 1
ELSE
   Select @RETURN = 0
GO

Then the ASP code:

dim cmd, prmPid, prmReturn, pid
pid = request("pid")
Set cmd = Server.createobject("ADODB.Command")
With cmd
         Set .activeconnection = "Your Connection object's name"
         .commandType = "adcmdStoredProc"
         .CommandText = "DoesRecordExist"
         
         Set prmPID = .CreateParameter("@PID",adInteger, adParamInput,4,pid
          .Parameters.Append prmPID

         Set prmReturn= .CreateParameter("@Return",adSmallint, adParamOutput,2)
          .Parameters.Append prmReturn

          .Execute

          Response.write .Parameters("@Return").Value
End With

I think this would work
Bye
fromapu
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now