Solved

Output parameters in ASP

Posted on 2003-12-03
6
1,074 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Menu Trouble 8 30
PHP email variable syntax 3 44
What is the normal amount of queries 5 68
Snap images to the bottom of a div 5 62
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now