Problem retrieving an OUTPUT variable from SQL Stored Procedure via ASP (Classic VBSCRIPT)

Question

I'm new here and this is my first post. I created a stored procedure in SQL Server that has (4) input parameters and (1) output parameter. The stored procedure is used to insert or delete a record into / from a table. The output parameter is assigned a message to be returned to the ASP page to provide feedback as to what happened in the stored procedure. ie. "Record Inserted", "Record Already Exists", or "Record Deleted".

When I run the code from the web page, records are added and deleted as they should be. The only problem I seem to be having in getting the output parameter to return a value.

When I execute the stored procedure from SQL Server Management Studio, The output parameter is displayed.

I've searched the internet and checked my code against other examples. I am not sure why else I wouldn't get the returned values. Any help would be greatly appreciated.

CREATE PROC My_Stored_Procedure
    -- Add the parameters for the stored procedure here

    @ContactID AS INT = '0', /* Contact ID relates to br_UID from the dbo.Reporting_Contacts table */
    @FacNum AS CHAR(4) = '0866', /* Facility Number from the Reporting Admin Page on Intranet */
    @EmployeeID AS CHAR(8) = '10023258', /* EmployeeID from the Reporting Admin Page on Intranet */
    @Delete AS CHAR(1) = 'N', /* Delete flag value to be passed from webpage on Intranet */
    @OutputMessage AS NVARCHAR(200) = 'test' OUTPUT /* To send back sucuess or error messages */

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Set variables for use in Stored Procedure


    -- Insert statements for procedure here

IF  @Delete = 'N'

        BEGIN


IF @EmployeeID  NOT IN (SELECT HGISSN FROM Main)

BEGIN

Select * FROM dbo.Reporting_Contacts

SET @OutputMessage = 'Contact does not exist as an employee.'

END


IF @EmployeeID IN (SELECT employeeID FROM dbo.Reporting_Contacts WHERE EmployeeID = @EmployeeID AND FacilityNumber = @FacNum)


BEGIN

SELECT @OutputMessage = 2

PRINT @OutputMessage 

SET @OutputMessage =  'This Person is already a contact.'


END

IF @EmployeeID IN (SELECT HGISSN FROM dbo.EMP_Table) AND @EmployeeID NOT IN (SELECT employeeID FROM dbo.Reporting_Contacts WHERE EmployeeID = @EmployeeID AND FacilityNumber = @FacNum)

BEGIN

INSERT INTO dbo.Reporting_Contacts (employeeID, FacilityNumber, ContactLevel)

VALUES (@EmployeeID, @FacNum, 'S')

SET @OutputMessage = 'New Contact has been added.'

END

END


ELSE IF @Delete = 'Y'

        BEGIN


DELETE FROM dbo.Reporting_Contacts WHERE br_UID = @ContactID


SET @OutputMessage = 'Record Deleted'


END



END

SELECT @OutputMessage

GO

This is my VBScript:

Dim addContacts__ContactID

addContacts__ContactID = "0"

if(Request("ContactID") <> "") then addContacts__ContactID = Request("ContactID")



Dim addContacts__FacNum

addContacts__FacNum = LEFT(Request.Form("FacilityNumber"),4)

if(Request("FacNum") <> "") then addContacts__FacNum = Request("FacNum")



Dim addContacts__EmployeeID

addContacts__EmployeeID = Request.Form("EmployeeID")

if(Request("EmployeeID") <> "") then addContacts__EmployeeID = Request("EmployeeID")



Dim addContacts__Delete

addContacts__Delete = "N"

if(Request("Delete") <> "") then addContacts__Delete = Request("Delete")




Dim strReturnMessage


set addContacts = Server.CreateObject("ADODB.Command")

addContacts.ActiveConnection = MY_STRING

addContacts.CommandText = "My_Stored_Procedure"

addContacts.CommandType = 4 'Evaluate as a stored procedure

addContacts.CommandTimeout = 0

addContacts.Prepared = true

addContacts.Parameters.Append addContacts.CreateParameter("@RETURN_VALUE", 3, 4)

addContacts.Parameters.Append addContacts.CreateParameter("@ContactID", 3, 1,10,addContacts__ContactID)

addContacts.Parameters.Append addContacts.CreateParameter("@FacNum", 129, 1,4,addContacts__FacNum)

addContacts.Parameters.Append addContacts.CreateParameter("@EmployeeID", 129, 1,8,addContacts__EmployeeID)

addContacts.Parameters.Append addContacts.CreateParameter("@Delete", 129, 1,1,addContacts__Delete)

addContacts.Parameters.Append addContacts.CreateParameter("@OutputMessage", 200, 2,200)


strReturnMessage =  addContacts.Parameters("@OutputMessage")

Answer 1

You are using the same variable as an integer and a string. The stored procedure has one OUTPUT parameter defined - @OutputMessage. This variable is modified and returned by the stored procedure. It is this variable you should be capturing. The code in your stored procedure is confused. Do you want @OutputMessage to be a string or an integer? As an example, see these adjacent lines in your SP:

SELECT @OutputMessage = 2

PRINT @OutputMessage  

SET @OutputMessage =  'This Person is already a contact.'

The first line is treating @OutputMessage as an integer (and what is with using the SELECT statement when a SET statement would suffice?) Then you print, no complaints. Then you set it to a text value (at least it is with a SET statement this time).

Look back to the definition of the SP and it defines the parameter as: @OutputMessage AS NVARCHAR(200) = 'test' OUTPUT It is not an integer.

The VB script seems to expect an integer as you try to convert the result using cint, and yet the default value for the variable in the SP is "test".

With cmd
    .NamedParameters = true 'Add this line to make things easier to read
    .CommandText = "sproc_login" 
    '.Parameters.Append  .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue)  'This is useless and should be removed.
    .Parameters.Append  .CreateParameter("@username", adVarChar, adParamInput,50,username) 
    .Parameters.Append  .CreateParameter("@password", adVarChar, adParamInput,50,password) 

    .Parameters.Append (cmd.CreateParameter ("@OutputMessage",adInteger,adParamOutput)) 'This is the variable being returned so get it.
    .execute 
    'strResponse = CInt(.Parameters("@Output").value ) 'Confused - @OutputMessage is defined as NVARCHAR(200) - not INTEGER.
strResponse = .Parameters("@OutputMessage").value 'This is much better.
End With 

The code in the area "with Response" is also confused and should be re-written.

By clearing up the confusion with using an integer and a string, the code can work.

Answer 2

try something like this

Function LoginUser()
    Dim cmd
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = dbconn
    With cmd
        .CommandText = "sproc_login"
        .Parameters.Append  .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue)
        .Parameters.Append  .CreateParameter("@username", adVarChar, adParamInput,50,username)
        .Parameters.Append  .CreateParameter("@password", adVarChar, adParamInput,50,password)
        .execute
        strResponse = CInt(.Parameters(0).value )
    End With
    With Response
        If strResponse > 0 Then
            .Write strSuccess
        Else
            .Write strFailure
        End If
    End With
    Set cmd = Nothing
End Function

++example use only modify as needed++