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

Question

I have an exception in my code. I have already tried to change my int64 to int32 but that doesn't change it.

In the database, the cells that represent "column_ID" have datatype NUMBER.

The problem is at line 7 in this code:

private void dataGridView_ArticleINVIA_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
    if (e.RowIndex >= 0 && e.RowIndex <= (sender as DataGridView).Rows.Count - 1)
    {
        try
        {
            Int64 id_riga = Convert.ToInt64((sender as DataGridView).Rows[e.RowIndex].Cells["column_ID"].Value);
            //Exception thrown here:
            int id_macchina = Convert.ToInt32((sender as 
                DataGridView).Rows[e.RowIndex].Cells["column_Machine"].Value);
            FormRecipeNote PopUpNote = new FormRecipeNote(id_macchina, 
                "MODIFICA", id_riga, 0);
            PopUpNote.ShowDialog(this);
            PopUpNote.Dispose();
         }
         catch (Exception Exc)
         {
             FormMain.loggerSoftwareClient.Trace(this.Name + " " + Exc);
         }
         //DataGrid_ArticleINVIA();
    }
}

the error is:

System.InvalidCastException: Object cannot be cast from DBNull to other types.
   at System.DBNull.System.IConvertible.ToInt64(IFormatProvider provider)
   at System.Convert.ToInt64(Object value)
   at Software_Client.FormSendReceiveRecipe.dataGridView_ArticleINVIA_CellDoubleClick(Object sender, DataGridViewCellEventArgs e)

Can someone help me resolve this?

Answer 1

As the error message says, the value of the cell is DBNull.Value and it can't convert from that to whatever you want it to be (in this case a long or an int). You need to check for DBNull before converting/casting the number:

Int64 id_riga = 0;
object value = (sender as DataGridView).Rows[e.RowIndex].Cells["column_ID"].Value;
if(value != DBNull.Value) 
    id_riga = Convert.ToInt64(value);

Because this adds some annoying overhead, if you do this much you'll probably want to make a helper method that does it for you.

public static long? getLongFromDB(object value)
{
    if (value == DBNull.Value) return null;
    return Convert.ToInt64(value);
}

Then your code can be:

Int64 id_riga = getLongFromDB((sender as DataGridView).Rows[e.RowIndex].Cells["column_ID"].Value)
    .GetValueOrDefault();

Answer 2

You can change the problematic line to :

int id_macchina = ((sender as DataGridView).Rows[e.RowIndex].Cells["column_Machine"].Value as int?).GetValueOrDefault();

This will give the default value of int (0) to the variable id_macchina if the value of the column column_Machine is null in the database (will be DBNull in C#)

Try this:

object aa = DBNull.Value;
int a = (aa as int?).GetValueOrDefault();

It will work! the value will be 0 if the column is DBNull.

* EDIT *

But try to use something else, this may hide some bugs, such as changing the data type, if the type is long and not int in the database, will always assign 0 to the variable, it happened with me in the past, thanks to @hvd to to indicate to that in his comment.

Answer 3

I'm a bit confused by some of your comments. Like for example, in your code the comment indicates that the line containing ToInt32 is where the error occurs, but the error message clearly shows that the error is thrown from within ToInt64.

Also, you said that the value in the database is NOT null. If that is true, you would not get the DBNull value, so it may be worthwhile to expand your code a bit so that you can so you can place some breakpoints and inspect the values in the cells directly before trying to convert them.

I've posted some example code below that you can use, including a conversion helper function that you might find useful.

//--------- inside the try block of your event handler -----------//
        var row = (sender as DataGridView).Rows[e.RowIndex];
        object objId = row.Cells["column_ID"].Value;
        object objMachine = row.Cells["column_Machine"].Value;
        // place a breakpoint on the line below, 
                    // so you can inspect the "obj..." values above to see
                    // if they are DBNull or not.

        var id_riga = objId.FromDb<Int64>();
        var id_macchina = objMachine.FromDb<Int32>();
//-----------continue your code here---------//

//----outside the form class, possibly in a separate file -----/
static class DbObjectExtensions {
    public static T FromDb<T>(this object dbObj, T defaultValueIfNull = default(T)) where T : IConvertible {
        if (Convert.IsDBNull(dbObj))
            return defaultValueIfNull;
        if (dbObj is T)
            return (T)dbObj;
        return (T)Convert.ChangeType(dbObj, typeof(T));
    }
}

I also want to clear up a comment I saw above that said "...it's not null, it's DBNull. The two are different". That statement is true in general, in C#, however it's actually misleading in the context it was written. It was written in response to the previous comment that said "[I] checked on database the value and is not NULL". For clarification, a NULL in a database field IS represented by DBNull in most .Net database providers. So in fact, a NULL value in the database IS usually the same as DBNull in C#. So if the OP is correct in saying that the database value is NOT NULL, then it does follow that the cell value should not be DBNull.

Of course it is still true that null is not the same as DBNull, so checking to see if an object is null does not determine whether it is DBNull or not, (but that is not what the OP said).

As a final note, the extension method I wrote, FromDB, includes an optional parameter for a default return value in the case that the object passed is DBNull. This can be used as follows:

//assuming -1 isn't a valid 
var id_riga = objId.FromDb<Int64>(-1); id
if (id_riga == -1) { /*handle case for null db value*/ }

Depending on your own personal preferences the above paradigm might be easier to understand than the Nullable version described in other answers.