Solved

Output parameters in ASP

Posted on 2003-12-03
6
1,079 Views
Last Modified: 2010-04-06
I want to retrieve the output paramater of a stored procedure using a direct call to the procedure across an ASP connection object.

e.g. objConn.spSetGallery Null, "Title", "Description", intOut - where intOut is the output parameter passed to the prcedure - this way is not working.

I realise you can use an ASP Command Object but I have already cut all the code and am looking for a get out of jail card.

-------------------------------------------------------


CREATE TABLE [dbo].[tblGallery] (
      [id] [int] IDENTITY (1, 1) NOT NULL ,
      [publishDate] [smalldatetime] NOT NULL ,
      [category] [int] NOT NULL ,
      [athlete] [int] NOT NULL ,
      [race] [int] NULL ,
      [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [description] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [orientation] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [photographer] [int] NULL ,
      [totalHits] [int] NOT NULL
) ON [PRIMARY]
GO


----------------------------

CREATE PROCEDURE spSetGallery

      @id int = NULL,      
      @publishDate smalldatetime,
      @category int,
      @athlete int,
      @race int,
      @title varchar(50),
      @description varchar(4000),
      @orientation char(1),
      @photographer int,
      @newid int OUTPUT

AS

      -- dont return rows affected
      SET NOCOUNT ON

      IF (@id IS NULL) OR (@id < 1)
      
            BEGIN
            INSERT INTO tblGallery ( [publishDate], [category], [athlete], [race],[title], [description], [orientation], [photographer] )
            VALUES(
                  @publishDate,
                  @category,
                  @athlete,
                  @race,
                  @title,
                  @description,
                  @orientation,
                  @photographer
            );
      
            SELECT @newid = @@IDENTITY;
            END
      
      ELSE

            BEGIN
            UPDATE
                  tblGallery
            SET
                  [publishDate] = @publishDate,
                  [category] = @category,
                  [athlete] = @athlete,
                  [race] = @race,
                  [title] = @title,
                  [description] = @description,
                  [orientation] = @orientation,
                  [photographer] = @photographer
      
            WHERE
                  [id] = @id;
      
            SELECT @newid = @id;
            END

      SET NOCOUNT OFF
GO



--------------------------------

             Dim m_objConn
            Set m_objConn =  Server.CreateObject("ADODB.Connection")            
                        
            If Response.IsClientConnected Then            
            
                  With m_objConn
                        .ConnectionString = "DRIVER={SQL Server};SERVER=127.0.0.1;DATABASE=MyDatabase;UID=sa;PWD=;"                                                             .ConnectionTimeout = 30
                        .Open
                  End With                                    
            
            End If
      
            '-------------------------------------------------------------------------------
            ' Adds a record and returns its id
            '-------------------------------------------------------------------------------
            Public Function AddRecord( ByVal intCategory, ByVal intAthlete, ByVal intRace, ByVal strOrientation, ByVal strTitle, ByVal strDate, ByVal strDescription, ByVal intPhotographer )

                  Dim intNewID: intNewID = -1

                  If HasConnection Then
                              
                        m_objConn.spSetGallery Null, strDate, intCategory, intAthlete, intRace, strTitle, strDescription, strOrientation, intPhotographer, intNewID
                        Response.Write "The IDENTITY value for tblGallery is: " + CStr(intNewID)
                        Response.Write "<br>It keeps returning -1 WHY?? Cant you use a direct call across an ASP Connection object AND retireve an output parameter"


                  End If            
                  
                  'return new id
                  AddRecord = intNewID
            
            End Function
      
---------------------------------

Now just run the function in ASP to test.

0
Comment
Question by:smacca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 

Author Comment

by:smacca
ID: 9866223
Test functions:

Dim intID
intID = AddRecord( 1, 1, 1, 'p', "title", "1/1/2003", "description", 1)
Response.Write "IDENTITY: " + CStr(intID) + "<BR>"
intID = AddRecord( 1, 1, 1, 'p', "title", "1/1/2003", "description", 1)
Response.Write "IDENTITY: " + CStr(intID) + "<BR>"
intID = AddRecord( 1, 1, 1, 'p', "title", "1/1/2003", "description", 1)
Response.Write "IDENTITY: " + CStr(intID) + "<BR>"
intID = AddRecord( 1, 1, 1, 'p', "title", "1/1/2003", "description", 1)
Response.Write "IDENTITY: " + CStr(intID) + "<BR>"
intID = AddRecord( 1, 1, 1, 'p', "title", "1/1/2003", "description", 1)
Response.Write "IDENTITY: " + CStr(intID) + "<BR>"
intID = AddRecord( 1, 1, 1, 'p', "title", "1/1/2003", "description", 1)
Response.Write "IDENTITY: " + CStr(intID) + "<BR>"
intID = AddRecord( 1, 1, 1, 'p', "title", "1/1/2003", "description", 1)
Response.Write "IDENTITY: " + CStr(intID) + "<BR>"


0
 
LVL 4

Expert Comment

by:c_swanky
ID: 9869663
I always used the Command Object to returned outputs from a stored proc.


Use this in you Public Function AddRecord


<%
                '-- Constants
       Const adCmdStoredProc   = 4
      Const adInteger         = 3
      Const adVarChar                = 200
      Const adParamInput      = 1
      Const adParamOutput     = 2
      Const adParamReturnValue = &H0004
      Const adNumeric             = 131
      Const adBoolean             = 11
      Const adDate                   = 7
 
 
       '================================================================
      ' (1)
      '================================================================
      sConn = "DRIVER={SQL Server};SERVER=127.0.0.1;DATABASE=MyDatabase;UID=sa;PWD=;"
      
      set oComm = Server.CreateObject("ADODB.Command")
            oComm.ActiveConnection = sConn      '<-- connection string here
            oComm.CommandText = "spSetGallery"      '<-- your stored proc name here
            oComm.CommandType = adCmdStoredProc            '<-- do NOT change (constant)

      with oComm
            '--INPUT: Form Values
             .Parameters.Append  .CreateParameter("@publishDate", adDate, adParamInput, , strDate)
             .Parameters.Append  .CreateParameter("@category", adInteger, adParamInput, , intCategory)
             .Parameters.Append  .CreateParameter("@athlete", adInteger, adParamInput, , intAthlete)
             .Parameters.Append  .CreateParameter("@race", adInteger, adParamInput, , intRace)
             .Parameters.Append  .CreateParameter("@title", adVarChar, adParamInput, 50, strTitle)
             .Parameters.Append  .CreateParameter("@description", adVarChar, adParamInput, 4000, strDescription)
            .Parameters.Append  .CreateParameter("@orientation", adVarChar, adParamInput, 1, strOrientation)
            .Parameters.Append  .CreateParameter("@photographer", adInteger, adParamInput, , intPhotographer)
            .Parameters.Append  .CreateParameter("@race", adInteger, adParamInput, , intNewID)


            '--OUTPUT: New Row Id
             .Parameters.Append  .CreateParameter(" @newid", adInteger, adParamOutput)
      
             .Execute

            AddRecord = .Parameters(" @newid")

---------------------------------
%>
0
 
LVL 4

Expert Comment

by:c_swanky
ID: 9869711
FORGOT TO ADD YOUR @ID  - Passing in ""  so you INSERT statement runs.

           .Parameters.Append  .CreateParameter("@id", adInteger, adParamInput, , "")


WILL LOOK LIKE THIS NOW:

          '--INPUT: Form Values
           .Parameters.Append  .CreateParameter("@id", adInteger, adParamInput, , "")
           .Parameters.Append  .CreateParameter("@publishDate", adDate, adParamInput, , strDate)
           .Parameters.Append  .CreateParameter("@category", adInteger, adParamInput, , intCategory)
           .Parameters.Append  .CreateParameter("@athlete", adInteger, adParamInput, , intAthlete)
           .Parameters.Append  .CreateParameter("@race", adInteger, adParamInput, , intRace)
           .Parameters.Append  .CreateParameter("@title", adVarChar, adParamInput, 50, strTitle)
           .Parameters.Append  .CreateParameter("@description", adVarChar, adParamInput, 4000, strDescription)
          .Parameters.Append  .CreateParameter("@orientation", adVarChar, adParamInput, 1, strOrientation)
          .Parameters.Append  .CreateParameter("@photographer", adInteger, adParamInput, , intPhotographer)
          .Parameters.Append  .CreateParameter("@race", adInteger, adParamInput, , intNewID)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:smacca
ID: 9871761
Can you pass NULL as a parameter input?
0
 
LVL 4

Expert Comment

by:c_swanky
ID: 9872443
Yes, NULL will work too.





0
 
LVL 4

Accepted Solution

by:
c_swanky earned 350 total points
ID: 9872691
          .Parameters.Append  .CreateParameter("@id", adInteger, adParamInput, ,NULL)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

When you work with shopping cart / ecommerce relates web sites, you need to pass the certain form post details to the payment gateway process page with required details for the products items you give to order. Also you may need to track the ordered…
I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

734 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