Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Output parameters in ASP

Posted on 2003-12-03
6
Medium Priority
?
1,083 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 1400 total points
ID: 9872691
          .Parameters.Append  .CreateParameter("@id", adInteger, adParamInput, ,NULL)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
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).
Suggested Courses

773 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