"Object cannot be cast from DBNull to other types"

12
votes

When my website gets to the following bit of code, it falls down with an exception as follows:

System.InvalidCastException: Object cannot be cast from DBNull to other types.

For the interests of brevity, I'm showing only the relevant code (it's a 4000+ LOC file I've been given).

if (dr["STAGE"] is DBNull)
{
    dto.Stage = 1; // This is the line throwing the exception, according to stack trace
}
else
{
    dto.Stage = Convert.ToInt32(dr["STAGE"]);
}

Here, dr is a DataRow object that is the result of a query to a database, dto is a basic class that just holds some properties, of which dto.Stage is an int member.

I've looked at other questions with the same error message, but most of them seem to suggest "Check if it's DBNull", which I'm already doing.

So can someone suggest a solution?

5
c#datarow
I am not sure but it seems the error is caused by dto.Stage = Convert.ToInt32(dr["STAGE"]); try using DBNULL.value - Kamran Pervaiz
@Kamran - but it's not reaching that point, the exception is thrown on the line containing dto.Stage = 1. - TZHX
Ignore the exact line number in the stack-trace; the numbers can be slightly off - I see that all the time. Run it in a debugger with break-points instead, or just add extra logging while you nail it down. - Marc Gravell
hehe, there's a reason I use dapper... to avoid all this fun! - Marc Gravell
if the error is being thrown when you are trying to set dto.Stage = 1, then its possible that dto is null? - tardomatic

5 Answers

5
votes

Use == instead of is

if (dr["STAGE"] == DBNull.Value)
{

}
2
votes

Use this slightly more efficient approach

int stageOrdinal = dr.GetOrdinal("STAGE");
while (dr.Read()) {
     dto = new DataTransferObject();
     if (dr.IsDBNull(stageOrdinal)) {
         dto.Stage = 1;
     } else {
         dto.Stage = dr.GetInt32(stageOrdinal);
     }
     //TODO: retrieve other columns.
     dtoList.Add(dto);
}

Accessing the columns by their index is faster than accessing them by name. The index of a column can be retrieved with the GetOrdinal method of the DataReader. This is best done before the loop.

1
votes

Use the System.Data.DataRow.IsNull function instead.

if(dr.IsNull("Stage"))
{
 ...
}
0
votes

Below is an example of a nullable data type which you can use to avoid DBNull errors. The example below is not a genuine solution for the issue you have, but is an example of how you can go about solving it. Think of it as learning to fish, instead of being given a fish.

I pulled this from http://msdn.microsoft.com/en-us/library/1t3y8s4s.aspx

class NullableExample
{
    static void Main()
    {
        int? num = null;
        if (num.HasValue == true)
        {
            System.Console.WriteLine("num = " + num.Value);
        }
        else
        {
            System.Console.WriteLine("num = Null");
        }

        // y is set to zero
        int y = num.GetValueOrDefault();

        // num.Value throws an InvalidOperationException if num.HasValue is false
        try
        {
            y = num.Value;
        }
        catch (System.InvalidOperationException e)
        {
            System.Console.WriteLine(e.Message);
        }
    }
}
0
votes

@MarcGravell had the right of it in his comment:

Ignore the exact line number in the stack-trace; the numbers can be slightly off - I see that all the time. Run it in a debugger with break-points instead, or just add extra logging while you nail it down

@Sergey's answer is just plain wrong.