"Object cannot be cast from DBNull to other types" in c#

Question

I have not come across this before, but the situation that this has arisen in I have been through multiple times.

For example I have this method:

mySqlCommand = mySqlConnect.CreateCommand();
mySqlCommand.CommandText = "SELECT EquipmentNumber, EquipmentType, P.AreaCode AS Location, EquipmentDsc FROM MajorEquipment ME"
  + " INNER JOIN PlantAreaCodes P ON ME.PACId = P.PACId WHERE"
  + "(@EquipNumber IS NULL OR EquipmentNumber LIKE @EquipNumber)"
  + " AND ((SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location) IS NULL OR ME.PACId = (SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location))"
  + " AND (@EquipType IS NULL OR EquipmentType LIKE @EquipType)"
  + " AND (@Comments IS NULL OR EquipmentDsc LIKE @Comments);";

mySqlCommand.Parameters.Add("@EquipNumber", MySqlDbType.VarChar);
mySqlCommand.Parameters.Add("@Location", MySqlDbType.VarChar);
mySqlCommand.Parameters.Add("@EquipType", MySqlDbType.VarChar);
mySqlCommand.Parameters.Add("@Comments", MySqlDbType.VarChar);

mySqlCommand.Parameters["@EquipNumber"].Value = MeModel.EquipmentNumber;
mySqlCommand.Parameters["@Location"].Value = MeModel.Location;
mySqlCommand.Parameters["@EquipType"].Value = MeModel.EquipmentType;
mySqlCommand.Parameters["@Comments"].Value = MeModel.Notes;

mySqlReader = mySqlCommand.ExecuteReader();

MajorEquipment_Controller.MeList = new List<MajorEquipment_Model>();

while (mySqlReader.Read())
{
    Console.WriteLine(mySqlReader["EquipmentDsc"].GetType());
    MajorEquipment_Controller.MeList.Add(new MajorEquipment_Model
    {
        EquipmentNumber = Convert.ToString(mySqlReader["EquipmentNumber"]),
        EquipmentType = Convert.ToString(mySqlReader["EquipmentType"]),
        Location = Convert.ToString(mySqlReader["Location"]),
        Notes = Convert.ToString(mySqlReader["EquipmentDsc"])
    });
}
mySqlReader.Close();
mySqlCommand.ExecuteNonQuery();

In this method, EquipmentDsc allows is System.DBNull, but I have never had a problem with it before.

However, in a separate program, I have a similar method:

mySqlCommand = mySqlConnect.CreateCommand();
mySqlCommand.CommandText = "SELECT * FROM IT_Requests;";

mySqlReader = mySqlCommand.ExecuteReader();

IT_Admin.Admin_Controller.List = new List<IT_Admin.Admin_Model>();

while (mySqlReader.Read())
{
    MessageBox.Show((mySqlReader["Name"].GetType()).ToString());
    IT_Admin.Admin_Controller.List.Add(new IT_Admin.Admin_Model
    {
        ReqID = Convert.ToInt32(mySqlReader["ReqID"]),
        Issue = Convert.ToString(mySqlReader["Issue"]),
        PC = Convert.ToString(mySqlReader["PC"]),
        Date = Convert.ToString(mySqlReader["ReqDate"]),
        Name = Convert.ToString(mySqlReader["Name"]),
        ReqType = Convert.ToString(mySqlReader["ReqType"]),
        Urgency = Convert.ToInt32(mySqlReader["Urgency"]),
        Description = Convert.ToString(mySqlReader["Description"]),
        PrevReport = Convert.ToBoolean(mySqlReader["PrevReport"]),
        Completed = Convert.ToBoolean(mySqlReader["Completed"]),
        Resolution = Convert.ToString(mySqlReader["Resolution"]),
        ResDate = Convert.ToString(mySqlReader["ResDate"])
    });
}

And as soon as it hits a null value, it spits out the "InvalidCastException" error.

Does anyone know what could be causing this?

Answer 1

All you have to do is check for DBNull in advance.

E.g.

if (!mySqlReader["ReqID"] is DBNull)
{ 
    ReqID = Convert.ToInt32(mySqlReader["ReqID"]);
}

An advice for future questions: Try shrinking your code sample down to the relevant point. In this case you provided some quite large samples.

Answer 2

Convert.ToString allows for NULL DB values because System.String itself is a nullable reference type, whereas System.Boolean is a non-nullable value type.

Do this for each nullable column you're reading from:

Object value = mySqlReader["columnName"];
if( value != DBNull.Value ) {
    destination = Convert.ToSomeStrongType( value );
} else {
    destination = null;
}