Results 1 to 5 of 5

Thread: [RESOLVED] How do I get returns from Stored Procedures in Classic ASP

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2008
    Location
    Scotland
    Posts
    68

    Resolved [RESOLVED] How do I get returns from Stored Procedures in Classic ASP

    Good evening

    I'm trying to execute a stored procedure which will take in a bunch of values as variables. Everything's working as expected so far, but I need to know how I would go about getting the value that the Stored Proc is returning when it's complete.

    The procedure in question is creating a new record in a database table, and returning the identity value. However I can't get my head around how to capture that identity value so that it can be presented to the end user.

    The code that I'm using is as follows:

    Set SQLDB=Server.CreateObject("ADODB.Connection")
    SQLDB.Open Session("ODBC")

    If Request.QueryString("Action") = "New" THEN
    NewSystemName = "'" & Request.Form("Field_SystemName") & "'"
    NewControlURL = "'" & Request.Form("Field_ControlURL") & "'"

    If Request.Form("Field_Visible") = "on" THEN
    NewVisible = 1
    ELSE
    NewVisible = 0
    END IF

    cmd = "exec dbo.ihsp_Dashboard_Admin_AddSystems " & NewSystemName & ", " & NewControlURL & ", " & NewVisible & ", '" & Session("Username") & "'"
    SQLDB.Execute(cmd)

    END IF

    After the SQLDB.Execute(cmd) line, I would expect to put something in there to interrogate the command and retrieve the return value. However everything I've tried has been unsuccessful. However, the Stored Procedure is working inasmuch as a new record is being created.

    TIA

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,882

    Re: How do I get returns from Stored Procedures in Classic ASP

    create a command object, set the commandtext to the name of the stored procedure... then add a parameter, with the input/output type of RETURNVALUE ... then add the rest of your parameters.... once done, command.Execute .... at that point your parameter that you marked as a return value, will have the value RETURNed by the SProc.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2008
    Location
    Scotland
    Posts
    68

    Re: How do I get returns from Stored Procedures in Classic ASP

    Ok, so I think I've managed to get the parameters configured properly. I'm not clear on how I code the front-end page so that it catches the value being returned by the Stored Procedure.

    The full code for the stored procedure is as follows:

    Code:
    Use ESS_Dashboard
    Go
    Create Procedure dbo.ihsp_Dashboard_Admin_AddSystems
    
    @SystemName varchar (50),
    @ControlURL varchar (500),
    @Visible bit = 0,
    @CreatedBy varchar (50),
    @SystemID INT OUTPUT 
    
    As
    
    Begin
    	Set NoCount On
    
    	Insert into dbo.Systems
    		(
    		SystemName,
    		ControlURL,
    		Visible,
    		CreatedBy,
    		CreatedOn
    		)
    	Values
    		(
    		@SystemName,
    		@ControlURL,
    		@Visible,
    		@CreatedBy,
    		GetDate()
    		)
    
    	Select	@SystemID = @@IDENTITY
    
    	Set NoCount Off
    End
    The code that I've written in the ASP page is as follows:
    Code:
        NewSystemName = Request.Form("Field_SystemName")
        NewControlURL = Request.Form("Field_ControlURL")
        If Request.Form("Field_Visible") = "on" THEN NewVisible = 1 ELSE NewVisible = 0
    
        set objCommand = Server.CreateObject("ADODB.command")
        conn = Session("ODBC")
        objCommand.ActiveConnection = conn
        objCommand.CommandText = "ihsp_Dashboard_Admin_AddSystems"
        objCommand.CommandType = adCmdStoredProc
        objCommand.Parameters.Append objCommand.CreateParameter ("@SystemName", adChar, adParamInput, 50, NewSystemName)
        objCommand.Parameters.Append objCommand.CreateParameter ("@ControlURL", adChar, adParamInput, 500, NewControlURL)
        objCommand.Parameters.Append objCommand.CreateParameter ("@Visible", adBoolean, adParamInput, , NewVisible)
        objCommand.Parameters.Append objCommand.CreateParameter ("@CreatedBy", adChar, adParamInput, 50, Session ("UserName"))
        objCommand.Parameters.Append objCommand.CreateParameter ("@SystemID", adInteger, adParamOutput)', , NewRecordID)
    
        NewRecordID = objCommand.Parameters("@SystemID")
        objCommand.Execute
    I'm reasonably sure that I'm doing something silly, but I've been staring at this for about 3 days now, so I'm frankly stumped.

    TIA

  4. #4
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,137

    Re: How do I get returns from Stored Procedures in Classic ASP

    Try swapping these two lines

    NewRecordID = objCommand.Parameters("@SystemID")
    objCommand.Execute

    should be

    objCommand.Execute
    NewRecordID = objCommand.Parameters("@SystemID")

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2008
    Location
    Scotland
    Posts
    68

    Re: How do I get returns from Stored Procedures in Classic ASP

    And just like that, it works and makes a whole lot more sense to boot.

    Many thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width