ExecuteScalar returns null or DBNull (development or production server)

Question

I'm trying to add a column to an existing DataRow in C#. Afterwards the column will be filled with a single value from my database.

DataRow dr already exists and column "COLNAME" also exists.
comTBP is my SqlCommand.

dr["COLNAME"] = Convert.ToInt32(comTBP.ExecuteScalar());

This all works fine if there is a value in my database and ExecuteScalar() can get that value. If I test this code on my development server (local) it also works if ExecuteScalar() return null or DBNull and the value of my new column is 0. But the problem appears if I deploy my code to the production server. If I do everything the same, with the same database it throws an Exception with a message that it can't convert DBNull to Int32.
My question is why does this error appear on the production server and not on my local development server?

Answer 1

Clearly in production you have either a NULL returned from the command execution or something different in the connectionstring or whatever; as a general rule you should always test for DBNull before casting/converting directly to another type the result of ExecuteScalar.

Check Rein's answer here (and vote him up) for his nice suggested solution:

Unable to cast object of type 'System.DBNull' to type 'System.String`

Answer 2

ExecuteScalar returns DBNull for null value from query and null for no result. Maybe on your development server it never occured (null result from query).

Answer 3

Use the ISNULL() function in your SQL.

ISNULL ( check_expression , replacement_value )

ie...

SELECT ISNULL(SUM(Price),0) FROM Order