Can't access values of Output parameters...

[ShikkurDude]

I have a subroutine in VB.NET:

	Sub getNextApproversNameAndEmail(ByRef strNextApproversName As String, ByRef strNextApproversEmail As String, ByVal conn As SqlConnection, ByVal reqID As Integer, Optional ByRef approval_type As String = "")
		Dim cmGetNextApprover As New SqlCommand
		cmGetNextApprover.Connection = conn
		cmGetNextApprover.CommandType = CommandType.StoredProcedure
		cmGetNextApprover.CommandText = "dbo.Requistions_get_NextApprover"
		cmGetNextApprover.Parameters.Add("@RequistionId", reqID)

		'set up output parameters
		Dim paramApprovalType As New SqlParameter
		paramApprovalType.ParameterName = "@approvalType"
		paramApprovalType.SqlDbType = SqlDbType.VarChar
		paramApprovalType.Size = 50
		paramApprovalType.Direction = ParameterDirection.Output
		cmGetNextApprover.Parameters.Add(paramApprovalType)

		Dim paramPersonID As New SqlParameter
		paramPersonID.ParameterName = "@personID"
		paramPersonID.SqlDbType = SqlDbType.VarChar
		paramPersonID.Size = 50
		paramPersonID.Direction = ParameterDirection.Output
		cmGetNextApprover.Parameters.Add(paramPersonID)

		Dim paramPersonName As New SqlParameter
		paramPersonName.ParameterName = "@personName"
		paramPersonName.SqlDbType = SqlDbType.VarChar
		paramPersonName.Size = 50
		paramPersonName.Direction = ParameterDirection.Output
		cmGetNextApprover.Parameters.Add(paramPersonName)

		Dim paramEmail As New SqlParameter
		paramEmail.ParameterName = "@email"
		paramEmail.SqlDbType = SqlDbType.VarChar
		paramEmail.Size = 50
		paramEmail.Direction = ParameterDirection.Output
		cmGetNextApprover.Parameters.Add(paramEmail)

		Dim dr As SqlDataReader

		If conn.State = ConnectionState.Closed Then
			conn.Open()
		End If

		cmGetNextApprover.ExecuteScalar()

		strNextApproversName = cmGetNextApprover.Parameters("@personName").Value.ToString()
		strNextApproversEmail = cmGetNextApprover.Parameters("@email").Value.ToString
		approval_type = cmGetNextApprover.Parameters("@approvalType").Value.ToString()
	End Sub

That calls a SQL stored procedure:

CREATE    PROCEDURE Requistions_get_NextApprover
( @RequistionId		int
, @PersonID	SMALLINT	OUTPUT
, @PersonName	varchar(255)	OUTPUT
, @Email	varchar(255)	OUTPUT
, @ApprovalType	varchar(255)	OUTPUT
) 
AS
SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE	@RequistionStateId_Entry	INTEGER
,	@RequistionStateId_Requested	INTEGER
,	@RequistionStateId_OPSManager	INTEGER
,	@RequistionStateId_OPSDirector	INTEGER
,	@RequistionStateId_OPSVP	INTEGER
,	@RequistionStateId_OPSApprove	INTEGER
,	@RequistionStateId_ISApprove	INTEGER
,	@RequistionStateId_Denied	INTEGER

SET	@RequistionStateId_Entry	= 1
SET	@RequistionStateId_Requested	= 2
SET	@RequistionStateId_OPSManager 	= 15
SET	@RequistionStateId_OPSDirector	= 16
SET	@RequistionStateId_OPSVP	= 17
SET	@RequistionStateId_OPSApprove	= 3
SET	@RequistionStateId_ISApprove	= 4
SET	@RequistionStateId_Denied	= 14


DECLARE @OrganizationId_Charge smallint
,	@RequistionStateId_Next	smallint

----------------------------------------------------------------------
-- check that it's ready for approval

select	@OrganizationId_Charge  = OrganizationId_Charge
,	@RequistionStateId_Next = Requistions.RequistionStateId_Next
from 	Requistions
where	Requistions.RequistionId = @RequistionId

IF	ISNULL(@RequistionStateId_Next,-1) NOT IN (
	@RequistionStateId_OPSManager
,	@RequistionStateId_OPSDirector
,	@RequistionStateId_OPSVP
,	@RequistionStateId_OPSApprove
,	@RequistionStateId_ISApprove )
BEGIN 
	raiserror('Requistion is not ready for approval.',10,1) 
	return + 1
END

----------------------------------------------------------------------

select	 @PersonID = Persons.PersonId
,	@PersonName = Persons.FirstName + ' ' + Persons.LastName
, 	@Email	= Persons.Email 
FROM 	(SELECT	MIN(OrganizationPermutations.LevelCnt)
	FROM Requistions
	JOIN OrganizationPermutations
		on 	OrganizationPermutations.OrganizationId = Requistions.OrganizationId_Charge
	WHERE Requistions.RequistionId = @RequistionId
		AND OrganizationPermutations.LevelCnt
		=	CASE Requistions.RequistionStateId_Next
				WHEN @RequistionStateId_OPSManager THEN 0
				WHEN @RequistionStateId_OPSDirector THEN 1
				WHEN @RequistionStateId_OPSVP THEN 2
				ELSE NULL 
				END
	) as Org (LevelCnt) 
JOIN	OrganizationPermutations
	ON	OrganizationPermutations.OrganizationId = @OrganizationId_Charge
	AND	OrganizationPermutations.LevelCnt	= Org.LevelCnt
JOIN OrganizationPersonApprovers 
	ON	OrganizationPersonApprovers.OrganizationId = OrganizationPermutations.OrganizationId_Parent
	AND	current_timestamp >= OrganizationPersonApprovers.StartTs
	AND	current_timestamp <= OrganizationPersonApprovers.EndTs
JOIN Persons
	ON Persons.PersonId	= OrganizationPersonApprovers.PersonId

IF	@PersonName is NOT NULL
BEGIN
	set @ApprovalType = 'OPS'
	RETURN 0
END

IF	@RequistionStateId_Next NOT IN (3,4)  -- NOT OPSApproval
	RETURN 0

----------------------------------------------------------------------------------------------
-- Since all OPS approvals are completed, get IS Approver
SELECT @PersonID = Persons.PersonId
,	@PersonName = Persons.FirstName + ' ' + Persons.LastName
,	@Email	= Persons.Email 
,	@ApprovalType	= 'IS'
FROM Persons
JOIN PersonISApprovers
	ON PersonISApprovers.PersonId = Persons.PersonId
JOIN (SELECT MIN(Priority) FROM PersonISApprovers) AS PriorityISApprover (Priority)
	ON PriorityISApprover.Priority	= PersonISApprovers.Priority

RETURN 0


GO

I know the stored procedure works - because this test SQL code:

DECLARE @RequistionId int, @PersonID smallint, @PersonName varchar(255), @Email varchar(255), @ApprovalType varchar(255)
set @RequistionId = 1035

EXEC PurchIS.dbo.Requistions_get_NextApprover @RequistionId, @PersonID OUTPUT , @PersonName OUTPUT , @Email OUTPUT , @ApprovalType OUTPUT
SELECT 'PersonID = ' + cast( ISNULL(@PersonID,-1) as Varchar(10)) + '; Name = ' + ISNULL(@PersonName,'') + '; Email  = ' + ISNULL(@Email,'') + '; ApprovalType = ' + ISNULL(@ApprovalType,'')

Returns:[tt] PersonID = 3394; Name = Kelly Nygen; Email = Kelly.Nygen@advo.com; ApprovalType = IS[/tt]
...which is exactly correct. My 4 return parameters should contain exactly those values, but instead always come back NULL when I test for them at the end of the above-mentioned VB.NET subroutine:

...
strNextApproversName = cmGetNextApprover.Parameters("@personName").Value.ToString()
		strNextApproversEmail = cmGetNextApprover.Parameters("@email").Value.ToString
		approval_type = cmGetNextApprover.Parameters("@approvalType").Value.ToString()
...

What can I do to access the values of these output parameters?

Thanks so much!!!
E

 

[ca8msm]

Try setting your parameter directions as ParameterDirection.ReturnValue rather than ParameterDirection.Ouput and see if that makes a difference.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[LFCfan]

Hi,

leave the ParameterDirection.Output as it is, and once you have executed the SqlCommand, you then need to set the variables:

    ....
    cmGetNextApprover.ExecuteScalar()

    paramPersonID = Convert.ToInt32(cmdData.Parameters["@personID "].Value)

hope this helps

 

[ShikkurDude]

Sorry guys - the logic was bad in the stored proc. It's not going to return "IS" when I expected it to...

[ Question closed. ]

Thanks for your help!

E