Getting a value stored procedure C#

Question

I have tried a variety of ways, but no luck. Using Microsoft Visual Studio and SQL Server 2005 here is the c# code and sql code

List<SqlParameter>  _params3 = new List<SqlParameter>();
_params3.Add(new SqlParameter("@startdate", txtDateFrom.Text));
_params3.Add(new SqlParameter("@enddate", txtDateTo.Text));
_params3.Add(new SqlParameter("@days", extendedDays));

extendedDays = Convert.ToInt32(DAL.executeStoredProcedureScalar(
                   "Time_Difference_Calc", _params3));

SQL code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Time_Difference_Calc]
    -- Add the parameters for the stored procedure here
    @startdate datetime,
    @enddate datetime,
    @days int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    set @days = (Select RawDays
    - NumWeeks * 2
    + CASE WHEN StartWD = 1 THEN 1 ELSE 0 END
    - CASE WHEN EndWD = 7 THEN 1 ELSE 0 END
    + 1
    AS Result
    FROM
    (SELECT Datepart(dw,@startdate) as StartWD,
    Datepart(dw,@enddate) as EndWD,
    DateDiff(d,@startdate,@enddate) as RawDays,
    DateDiff(wk,@startdate,@enddate) as NumWeeks
    ) A)

    --SET @ReturnValue = @days
    RETURN @days
END

I can run it on the database just fine..put 2 dates in.. works fine for what I need..

But whenever I actually run it in the page.. always get

System.NullReferenceException: Object reference not set to an instance of an object."
source error : return cmd.ExecuteScalar().ToString();

Any help is appreciated. Pretty much the idea is I just want to get extended days equal to whatever the stored procedure returns.

Answer 1

You need to set the @day's parameter's Direction property to ParameterDirection.Output

Also, you will have to get the value out of the @day SqlParamater.Value property

Answer 2

Not being able to see the actual code that invokes the SQL, it's hard to be certain, but I'm pretty sure you ought to be calling ExecuteNonQuery for this - you're not actually selecting any return values, hence the null reference exception when your code is trying to retrieve a scalar value.

If your stored procedure had a section in it along the lines of SELECT TOP 1 foo FROM bar WHERE baz = @quux; then ExecuteScalar (or your equivalent of it) would be approriate. However, you're returning the value as an output parameter, so it's a non-query.

Note: the other answers are correct about needing to set the parameter direction, but this is why you're getting the NullReferenceException.