Solved

Output parameters in ASP

Posted on 2003-12-03
6
1,076 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
The viewer will learn how to count occurrences of each item in an array.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

803 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