ExecuteScalar returns null or DBNull (development or production server)

M

3

11

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?

Mindexpanding answered 28/10, 2011 at 9:6 Comment(1)

Most likely different data on production and development. – Giraudoux 28/10, 2011 at 9:35

M

10

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`

Mendicant answered 28/10, 2011 at 9:15 Comment(9)

Convert.ToInt32 from null results in 0. – Involuted 28/10, 2011 at 9:16

Then either he does not say all the truth or you are wrong ;-) I had similar issues in the past and solved by always checking for DBNULL before converting/casting – Mendicant 28/10, 2011 at 9:18

I know it returns NULL in production.. it also returns NULL in development because it's the same DB. My question was why it returns NULL in development and DBNull in production? In development the casting from NULL to Int32 will result in 0 but in production it tries to cast from DBNull to Int32 and throws an exception – Mindexpanding 28/10, 2011 at 9:19

Well its written in documentaion : Return Value: A 32-bit signed integer equivalent to value, or zero if value is Nothing. – Involuted 28/10, 2011 at 9:19

AFAIK null in C# is NOT DbNull.Value !! – Mendicant 28/10, 2011 at 9:21

I know... and that is my question?? In development, return is NULL and in production return is DBNull.. – Mindexpanding 28/10, 2011 at 9:22

are connection strings and all layers like .NET versions, service packs, db drivers, OS and all everything the same and are you connecting to same exact SQL Server machine and database and table and stored procedure? Somewhere there must be a difference! Release vs Debug build.... ?! anyway you have the solution on how to handle this in the answer I linked above. – Mendicant 28/10, 2011 at 9:24

I guess the problem is like you say with the versions. Development is Windows 7 and production is a Windows Server 2008 R2. Release vs Debug can't be the problem because I also tried a released version on my local pc and the connection string is the same to the same SQL Server-machine. I think my only (and most secure) solution is to use a method like you suggested above and check for DBNull or NULL-values. – Mindexpanding 28/10, 2011 at 9:40

yes you should always check for null and DBNull... before using the resulting object, not only when converting to Int32 – Mendicant 28/10, 2011 at 9:41

I

19

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).

Involuted answered 28/10, 2011 at 9:27 Comment(0)