Object cannot be cast from DBNull to other types. Error when a null value is read by the Reader

Question

int topID = 0;
string TopIDQuery = "Select TopID from tbl_Organisation where OrganisationID=@OrgID";

paramet[0] = new MySqlParameter("@OrgID", MySqlDbType.Int32);
paramet[0].Value = OrgID;

reader = server.ExecuteReader(CommandType.Text, TopIDQuery, paramet);

while (reader.Read())
{
    topID = Convert.ToInt32(reader["TopID"]);
}

reader.Close();

I am reading the topID from the table, when the TopID is null I want to leave the topID as 0, but since it is null it is throwing a error, how can I handle this error when the topID is null

score 7 · Accepted Answer · answered Nov 03 '11 at 16:43

7

Change your reading code to:

while (reader.Read())
{
    if(reader.IsDBNull(reader.GetOrdinal("TopID")))
       topID = 0;
    else
       topID = Convert.ToInt32(reader["TopID"]);
}

answered Nov 03 '11 at 16:43

marc_s

732,580
175
1,330
1,459

CS1502: The best overloaded method match for 'System.Data.Common.DbDataReader.IsDBNull(int)' has some invalid arguments same error – Mark Nov 03 '11 at 16:46

James Michael Hare · Answer 2 · 2011-11-03T16:49:20.750

4

Call IsDBNull() on the reader to check the column before attempting to convert it:

   using (reader = server.ExecuteReader(CommandType.Text, TopIDQuery, paramet))
   {
       while (reader.Read())
       {
           var column = reader.GetOrdinal("TopID");

           if (!reader.IsDBNull(column))
              topID = Convert.ToInt32(reader[column]);
           }
       }
   }

Or, compare against DBNull.Value:

   var value = reader["TopID"];

   if (value != DBNull.Value)
   {
       topID = Convert.ToInt32(value);
   }

edited Nov 03 '11 at 16:49

answered Nov 03 '11 at 16:42

James Michael Hare

37,767
9
73
83

The best overloaded method match for 'System.Data.Common.DbDataReader.IsDBNull(int)' has some invalid arguments error how to solve it – Mark Nov 03 '11 at 16:44

score 1 · Answer 3 · answered Nov 03 '11 at 16:47

1

Use the test:

int columnNr = reader.GetOrdinal("TopID");
if (!reader.IsDBNull(columnNr)) {
    topID =  reader.GetInt32(columnNr);
}

answered Nov 03 '11 at 16:47

Olivier Jacot-Descombes

104,806
13
138
188

If you determine the column number once before the loop, then the code will be somewhat faster. – Olivier Jacot-Descombes Nov 03 '11 at 16:49

score 1 · Answer 4 · answered Nov 03 '11 at 16:49

1

The problem is that you are trying to set int with a null, not in the read. you can do this to set an alternate value if the one you are trying to put in is null:

    topID = Convert.ToInt32(reader["TopID"]) ?? 0;

Another alternative is that you can use nullable integers by initializing topID like this:

int? topID = 0

but doing this will still require checking for null elsewhere, along with some other things to have to handle so I would recommend using the double question mark on your value set as I showed first.

answered Nov 03 '11 at 16:49

Nikkoli

502
5
13

Operator '??' cannot be applied to operands of type 'int' and 'int' – Mark B Apr 10 '12 at 22:11
Correct, but it can be used when reader["TopId"] is capable of returning a null because instead of throwing a null reference, the Convert.ToInt32 will provide a null output which is invalid on the topID he is setting. – Nikkoli Apr 11 '12 at 14:38

score 0 · Answer 5 · edited Apr 16 '14 at 13:07

Here is the code in which we Autogenerate any id from ms access database on page load of any Registration Form

void Number()
{
    con.Open();
    int id;
    cmd = new OleDbCommand("select max(ID) from Entry", con);
    string value = (cmd.ExecuteScalar().ToString());
    string max;
    if (value != null)
    {      
        max = "0"+value;
        id = Convert.ToInt32(max);
        lblNumber.Text = "Acronym of Reg form like for Emp for Employee" 
                        + Convert.ToString((id + 1));

    }
    con.Close();
}

score 0 · Answer 6 · edited May 23 '17 at 12:34

Use a generic extension method on your reader (see similar SO question)

topID = reader.GetFieldValueOrDefaultIfDBNull<Int32>("TopID");

Implement this extension method on your reader class (MySql, Sql, Oracle, etc.)

public static T GetFieldValueOrDefaultIfDBNull<T>(this SqlDataReader reader, string indexName)
{
    if (reader.IsDBNull(reader.GetOrdinal(indexName)))
    {
        return default(T); // returns the default value for the type
    }
    else
    {
        return reader.GetFieldValue<T>(reader.GetOrdinal(indexName));
    }
}

score 0 · Answer 7 · answered Nov 03 '11 at 16:54

You need to check to see if the value is DBNull before trying to retrieve it. Also, I recommend using GetInt32() over Convert.ToInt32() because it has been stated that internal optimization of GetInt32() may change where it will be faster than using Convert.ToInt32(). I learned this a long time ago and do not have a reference article for you to look at. At the time I learned this GetInt32() was internally using Convert.ToInt32()

while(reader.Read()) {
    int TopIDIndex = reader.GetOrdinal("TopID");
    topID = reader.IsDBNull(TopIDIndex)
        ? 0
        : reader.GetInt32(TopIDIndex);
}

score 0 · Answer 8 · answered Nov 03 '11 at 17:24

I like to use DataTable rather than a DataReader. I hate repetitive boilerplate, so I added some extension methods to to the DataRow class to encapsulate the downcast logic and make for more readable code:

DataTable dt = ExecStoredProcedure() ;
foreach ( DataRow dr in dt )
{
  int       id            = dr.CastAsInt("id") ;
  dateTime? dtLastUpdated = dr.CastAsDateTimeNullable("last_update_date") ;
  int?      col3          = dr.CastASIntNullable(3) ;
}

Here the code for the downcasting a column value from a DataRow to an int/int?:

#region downcast to int

public static int CastAsInt( this DataRow row , int index )
{
    return toInt( row[index] ) ;
}
public static int CastAsInt( this DataRow row , string columnName )
{
    return toInt( row[columnName] ) ;
}

public static int? CastAsIntNullable( this DataRow row , int index )
{
    return toIntNullable( row[index] );
}
public static int? CastAsIntNullable( this DataRow row , string columnName )
{
    return toIntNullable( row[columnName] ) ;
}

#region conversion helpers

private static int toInt( object o )
{
    int value = (int)o;
    return value;
}

private static int? toIntNullable( object o )
{
    bool hasValue = !( o is DBNull );
    int? value    = ( hasValue ? (int?) o : (int?) null ) ;
    return value;
}

#endregion conversion helpers

#endregion downcast to int

I sure hanging similar extension methods off a DataReader would be fairly simple.

Object cannot be cast from DBNull to other types. Error when a null value is read by the Reader

8 Answers8