Null reference exception in my method. How to fix it?

Question

Here is my code. I think the problem is with my declare @EmployeeId.

string strCommand = "insert into Employee (FirstName, LastName, Birthday, Inn)" +
                    " values (@FirstName, @LastName, @Birthday, @Inn)" +
                    " declare @EmployeeId int = @@identity";

SqlCommand command = new SqlCommand(strCommand, connection);

command.Parameters.AddWithValue("@FirstName", employee.FirstName);
command.Parameters.AddWithValue("@LastName", employee.LastName);
command.Parameters.AddWithValue("@Birthday", employee.Birthday);
command.Parameters.AddWithValue("@Inn", employee.INN);

SqlParameter parameter = new SqlParameter("@EmployeeId", System.Data.SqlDbType.Int);
parameter.Direction = System.Data.ParameterDirection.Output;
//command.Parameters.Add(parameter); //???

try
{
    connection.Open();
    command.ExecuteNonQuery();

    employee.EmployeeId = (int)parameter.Value; // Here I get the exception
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
    Console.WriteLine(e.StackTrace);
    // new Exception("Employee Not Insertes");
}
finally 
{
    connection.Close();
}

return employee;

this only one method that is not working at all. Sorry for my English.

I know what is null reference Exception. I have not got any value from parameter.Value. I think in my code below is some mistakes. I does not answer my question. — Alex, Sep 29 '20 at 16:33
You might also check out this Question: https://stackoverflow.com/questions/20381500/retrieve-output-parameter-of-sql-statement — Jamiec, Sep 29 '20 at 16:33
or this one: https://stackoverflow.com/questions/44263511/how-to-retrieve-out-parameter-using-text-command — Jamiec, Sep 29 '20 at 16:34
`.Value` returned a null but the code is trying to cast this into an int `(int)null` isn't valid. The reason is that the *SQL query* never sets any output parameter. In fact, there is no output parameter, all parameters are input parameters — Panagiotis Kanavos, Sep 29 '20 at 16:34
In short, `parameter` never receives a value so `parameter.Value` is null — Panagiotis Kanavos, Sep 29 '20 at 16:35
If you wanted to return the new ID one way would be to use `select @@IDENTITY` and execute the query with `ExecuteScalar`. A better way would be to use an `OUTPUT` clause to return the new IDs directly ie `INSERT ... OUTPUT inserted.ID VALUES ...`. No need for an additional SELECT — Panagiotis Kanavos, Sep 29 '20 at 16:36

Joel Coehoorn · Accepted Answer · 2020-09-29T17:00:14.310

0

I'd do it like this:

string sql = "
    insert into Employee(FirstName,LastName,Birthday,Inn)
        output inserted.Id -- or inserted.employeeid, if that's the column name
        values(@FirstName,@LastName,@Birthday,@Inn);";

// You should create a new connection object for most queries. Really.
// Read this for more info why: https://softwareengineering.stackexchange.com/q/142065/8057
// You should also wrap that connection in a using block
using (var connection = new SqlConnection("connection string here"))
using (var command = new SqlCommand(sql, connection))
{
    //You should avoid AddWithValue(). In certain situation it can cause a severe performance penalty
    //Instead, you should use an explicit type and length
    command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 15).Value =  employee.FirstName;
    command.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = employee.LastName;
    command.Parameters.Add("@Birthday", SqlDbType.DateTime).Value = employee.Birthday);
    command.Parameters.Add("@Inn", SqlDbType.Int).Value = employee.INN; //not sure what INN represents. Some Number?

    connection.Open();           
    employee.EmployeeId = (int)command.ExecuteScalar(); 
}
return employee;

Notice this allowed me to completely remove the line with the exception. For that matter, it allowed me to remove exception handling here entirely. Instead, it can move to the calling code -- where it belongs -- because the finally block is no longer needed to close the connection.

edited Sep 29 '20 at 17:00

answered Sep 29 '20 at 16:52

Joel Coehoorn

362,140
107
528
764

Now I have System.InvalidCastException: 'Specified cast is not valid.' next to employee.EmployeeId = (int)command.ExecuteScalar(); Inn is number but in my program the type is string so I changed to SqlDbType.NVarChar, 20 – Alex Sep 29 '20 at 17:54
Doesn't matter what your C# program type is. You want it to match the column in the database. But for the specific error, it looks like employee.EmployeeId is not an `int`. You could fix this by changing the cast, but the better fix is changing the Employee class to declare that field as an int. – Joel Coehoorn Sep 29 '20 at 18:15
the problem is not in Employeeid because I have problem even like this int b =(int)command.ExecuteScalar(); there is something wrong with command.ExecuteScalar(); In database i have int for EmployeeId. (int)command.ExecuteScalar() should return an object but it returns me an exception. – Alex Sep 29 '20 at 18:28
Hmmm... I think this might be possible if no row was inserted, no ID created. In that case, you'd get back NULL or DBNull.Value, which won't cast to int. You should use your debugger to see what is actually returned. – Joel Coehoorn Sep 29 '20 at 18:32
it returns an exception. but a row is inserted. – Alex Sep 29 '20 at 18:33
Alex, an exception is never returned; exceptions are thrown. In this case, the exception is caused the (int) cast, which doesn't happen until after the function call is finished. That ExecuteScalar() call returns something, and you can use the debugger to find out what. – Joel Coehoorn Sep 29 '20 at 18:41
it returned a number. – Alex Sep 29 '20 at 18:51
What kind of number? And was it really a string of digits that happens to look like a number? Or was it a number outside the bounds for a int? – Joel Coehoorn Sep 29 '20 at 18:52
It is working like this int b =Convert.ToInt32(command.ExecuteScalar()); – Alex Sep 29 '20 at 18:54
but it is not working like this (int)(command.ExecuteScalar()); Why? – Alex Sep 29 '20 at 18:59
That sounds like the database is generating a text string that only happens to look like a number, rather than really generating a number. `Convert.ToInt32()` can handle that situation, but an int cast `(int)` will not. – Joel Coehoorn Sep 29 '20 at 20:38

Null reference exception in my method. How to fix it?

1 Answers1