Return Decimal values problem !!!

[iquttaineh]

Hi,
I have a stored procedure that executed in SQL Server 2000 that returns a decimal SUM value, the stored procedure executed successfully in Query Analyzer and returns the correct value, but when calling it from ASP.Net (VB) Page it always returns ZERO !!!

I'm sure that my VB.net code is true because i used it before successfully.

my stored procedure code is:

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

CREATE PROCEDURE [spr_Total]
(
    @vchrClientNo varchar(50),
    @Total decimal(18,3) OUTPUT
)
AS
DECLARE
    @Total decimal(18,3)
 
BEGIN
    SELECT @Total = SUM(dcOutstandingBalance) 
                FROM bl_Accounts 
                WHERE vchrClientNo = @vchrClientNo
END
 
GO
 

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

my .net call function:

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

 Public Function GetTotalOutstandingBalance(ByVal vchrClientNo As String) As Decimal
        Dim Params(1) As SqlParameter
        Params(0) = New SqlParameter("@vchrClientNo", vchrClientNo)
        Params(0).SqlDbType = SqlDbType.NVarChar
        Params(1) = New SqlParameter("@Total", 0)
        Params(1).SqlDbType = SqlDbType.Decimal
        Params(1).Direction = ParameterDirection.Output
        SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, "spr_Total", Params)
        Return Params(1).Value
    End Function
 

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

Any help will be appriciated.

Regards,
Ishaq

[Plater]

You declare @total twice in your SQL procedure. Which means the output value is never getting populated because the code uses the more local version of the variable instead.