Object reference was not set to an instance of an object SQL Server in C#

Question

I have this procedure in SQL Server and I tried it in SQL Server Management Studio, it works good with my database but in c# it does not work:

Create Procedure BorrowIsCorrect
    @ProductName nvarchar(100),
    @PersonId nvarchar(50),
    @HBD bit,
    @count int output
as
Begin
    select @count = count(*) 
    from BorrowTable
    where Person_Identity = @PersonId 
      and Product_Name = @ProductName 
      and H_B_D = @HBD

    return @count
end

This is my code in c#:

internal bool BorrowIsCorrect(Borrow borrow)//checks if some person has     some specific product and has not Delivered it yet
{
    using (SqlConnection conn2 = new SqlConnection(_ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            conn2.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "BorrowIsCorrect";

            cmd.Connection = conn2;

            SqlParameter Output = new SqlParameter("@count", SqlDbType.Int);
            Output.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(Output);
            cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = borrow.Product.Productname;
            cmd.Parameters.Add("@PersonId", SqlDbType.NVarChar).Value = borrow.Person.Id;
            cmd.Parameters.Add("@HBD", SqlDbType.Bit).Value = borrow.Has_been_received;

            //int result = (int)(cmd.ExecuteScalar());
            return ((int)(cmd.ExecuteScalar()) == 1) ? true : false;
        }
    }
}

But this code in C# throws an exception

Object reference was not set to an instance of an object

How can I fix it ?

Thanks

When I debug it, all is fine till this line:

 return ((int)(cmd.ExecuteScalar()) == 1) ? true : false;

or I tried this line of code instead of the above one:

object result = (int)(cmd.ExecuteScalar());

The result variable is set to no value because exception occurs just when executing cmd.ExecuteScalar()

Edited:

I figure out that the result value is calculated correctly but not returned into an object as you see here:

enter image description here

Here after executing cmd.ExecuteScalar() in cmd.parameters we see four variables (correct) now if we expand count (the result and returned value form proc) as you see below its value is 1 (then result is correct) but the object that cmd.ExecuteScalar() returns is null.

enter image description here

Check you `borrow` variable and its `Person or Product` internal class instances — Steve, Feb 05 '15 at 22:21
none of borrow and person and product are null.i tried this function not using StoredProcedure and it worked good but using procedure not work!! — ako, Feb 05 '15 at 22:24
whay downvote?i know about nullreference exceptions but this one seems a little bit different.and found no answer in the StackOverflow link — ako, Feb 05 '15 at 22:27
here:return ((int)(cmd.ExecuteScalar()) == 1) ? true : false; when executing query — ako, Feb 05 '15 at 22:30
[Bad SQL Habits: Using SELECT or RETURN instead of OUTPUT](https://sqlblog.org/2009/10/09/bad-habits-to-kick-using-select-or-return-instead-of-output). You're trying to use all three in a single statement! — Dour High Arch, Feb 05 '15 at 22:33
@Steve: i splited it but not work yet. the exception is just when query executes and result is not seted to any value even null — ako, Feb 05 '15 at 22:37
Reading your comments above then there is nothing in your code that could give this exception. Of course I assume that you have checked all the values with the debugger before executing the call to ExecuteScalar. The last thing that comes to mind is to clean the solution and rebuild everything. — Steve, Feb 05 '15 at 22:44
no .i checked it many times and none of my objects(borrow,person,product) are null — ako, Feb 05 '15 at 22:50
Then edit your question to show at what point exactly the exception is thrown, what variable it points to and what the full stack trace is. — CodeCaster, Feb 05 '15 at 22:53

score 0 · Answer 1 · answered Feb 06 '15 at 00:43

0

finally i found a tricky way for solving my problem(i think it is not a standard way):

cmd.ExecuteScalar();
int result=cmd.Parameters[0].Value;

answered Feb 06 '15 at 00:43

ako

179
7

2

That's not a tricky way - it's just the way to access **output parameters** from a `SqlCommand`! You could also use a name: `int result=cmd.Parameters["@count"].Value;` to get the value .... – marc_s Feb 06 '15 at 06:10

Object reference was not set to an instance of an object SQL Server in C#

1 Answers1