Object reference not set to an instance of an object Caused by DBNull.Value

Question

SqlCommand objsql = new SqlCommand();
.
.
objsql.Parameters.AddWithValue("@Param1", DBNull.Value);
.
.
.

I get an exceptional error:

"Object reference not set to an instance of an object"

If i do:

objsql.Parameters.AddWithValue("@PaymentMethodID", null);

I get the following error: The parameterized query '(@SupplierQuoteID int,@PaymentMethodID nvarchar(4000),@DueDate d' expects the parameter '@PaymentMethodID', which was not supplied."}

PaymentMethodID is a column in table that takes null.

This error happens in here:

string valHolder = null;
valHolder = objSqlCommand.ExecuteScalar().ToString(); 
singleValue = Convert.ToInt32(valHolder);

Once objSqlCommand.ExecuteScalar().ToString(); is executed, the error is thrown. The record gets inserted to the table BUT the ExecuteScalar() doesn't return any value! It should return the current latest auto-incremented pk, but it doesn't.

NOTE: all errors i have mentioned above are thrown when this line is executed

valHolder = objSqlCommand.ExecuteScalar().ToString(); 

Here is the error in full:

System.NullReferenceException was caught
  Message="Object reference not set to an instance of an object."
  Source="........"
  StackTrace:
       at ......DAL.ExecuteSQL(SqlCommand sqlCmd, String typeOfExecution) in C:\Users\....\Desktop\........\DAL.cs:line 136
  InnerException: 

What should i do?

Answer 1

Don't call ToString() directly on ExecuteScalar(). Call the objSqlCommand.ExecuteScalar() first, then test to see if that variable is nothing. More than likely that is what is happening.

If you are calling a stored procedure, make sure that Select SCOPE_IDENTITY() is the last line of your sproc or the last autonumber will not be returned.