CODEWITHSUNDEEP
c#sql-server-2005stored-procedures
Eddy Whitaker
Eddy Whitaker

Reputation: 141

Getting a value stored procedure C#

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.

Upvotes: 4

Views: 528

Answers (5)

Dan McClain
Dan McClain

Reputation: 11920

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

Upvotes: 4

Ray
Ray

Reputation: 46565

Change your stored procedure to select the value instead of using a parameter. Then you can continue to use ExecuteScalar.

The stored proc would be something like this:

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

    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
END

Another thing to consider, if you're only calling this from an app, then the stored proc is kind of useless. You don't need SQL Server to do this calculation, and it's simpler to do in code.

Upvotes: 1

Thomas
Thomas

Reputation: 1563

SqlParameter daysParameter = new SqlParameter("@days", extendedDays);
    daysParameter .Direction = ParameterDirection.Output;
    _params3.Parameters.Add(daysParameter );

should do the trick

Upvotes: 0

Ade Stringer
Ade Stringer

Reputation: 2661

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.

Upvotes: 3

Diego
Diego

Reputation: 36126

_params3.Direction = System.Data.ParameterDirection.Output;

exec the proc

extendedDays = Convert.ToInt32(_params3.Value);

Upvotes: 1

Related Questions