Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Output parameters in ASP

Posted on 2003-12-03
6
Medium Priority
?
1,081 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

715 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