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?

What sql statement could have parameter equal to null, can you show me your select statement or something similar? — Waleed A.K.
– Waleed A.K., Commented Jul 12, 2010 at 18:54
As Jeremy (below) and Waleed (above) suggest, it seems your C# is fine, and the problem lies in your SQL. — James Curran
– James Curran, Commented Jul 12, 2010 at 19:08
I just got your answers, before i read them, here is my code: <br/><br/> INSERT INTO SupplierInvoice ( SupplierQuoteID, PaymentMethodID, DueDate ) VALUES ( @SupplierQuoteID , @PaymentMethodID , @DueDate ); <br/><br/> The code is generated via reflection. — Beginner_Pal
– Beginner_Pal, Commented Jul 13, 2010 at 11:41
Problem fixed - here is the correct statement: INSERT INTO SupplierInvoice ( SupplierQuoteID, PaymentMethodID, DueDate ) VALUES ( @SupplierQuoteID , @PaymentMethodID , @DueDate ); SELECT SuInvoiceID FROM SupplierInvoice WHERE SuInvoiceID = @@IDENTITY — Beginner_Pal
– Beginner_Pal, Commented Jul 13, 2010 at 11:55

Jeremy · Accepted Answer · 2010-07-12 18:54:07Z

6

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.

answered Jul 12, 2010 at 18:54

Jeremy

4,8582 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Beginner_Pal Over a year ago

Fixed. The sql statement had to be: INSERT INTO SupplierInvoice ( SupplierQuoteID, PaymentMethodID, DueDate ) VALUES ( @SupplierQuoteID , @PaymentMethodID , @DueDate ); SELECT SuInvoiceID FROM SupplierInvoice WHERE SuInvoiceID = @@IDENTITY

Jeremy Over a year ago

I believe this will work as well and is simpler. INSERT INTO SupplierInvoice ( SupplierQuoteID, PaymentMethodID, DueDate ) VALUES ( @SupplierQuoteID , @PaymentMethodID , @DueDate ); SELECT @@IDENTITY

khany Over a year ago

Nope. SELECT @@IDENTITY didn't work but SELECT SuInvoiceID FROM SupplierInvoice WHERE SuInvoiceID = @@IDENTITY did.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related