Solved

Pass Return Code to ASP Page

Posted on 2003-10-23
5
261 Views
Last Modified: 2012-06-21
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
Comment
Question by:jamaje
5 Comments
 
LVL 3

Accepted Solution

by:
SQLMaster earned 50 total points
ID: 9606439
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 9606931
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
 

Expert Comment

by:fromapu
ID: 9618947
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
How can i use insert and update together by merging two tables? 9 30
Database Integrity 1 48
Use column to search string column 2 9
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question