A first chance exception of type 'System.InvalidCastException' occurred in mscorlib.dll Object cannot be cast from DBNull to other types

Question

Hi all I am reading data from sql server using stored procdure, and everything looks excpet when there is a null data in the database I get cannot cast DBNull to other types. I am using .net MVC3 following is my code:

public static List<XYZFactorsModel> SelectGridItems(string sidx, string sord, int page, int rows, String rid, String process, String detail_table, String jobGroup, String date)

    {


       const string spName = "dbo.p___GetXYZMonitorJobDetails";
        List<XYZFactorsModel> XYZFactorDetailGridCollection;

        string connectionString = ConfigurationManager.ConnectionStrings["AdvItemsContext"].ConnectionString;
        if (string.IsNullOrEmpty(connectionString))
            return null;
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            SqlCommand sqlCommand = new SqlCommand(spName, sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            //sqlCommand.Parameters.Add("@jobDate", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@jobGroup", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@jobName", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@detailTable", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@filterBatchControl", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@filterDate", SqlDbType.VarChar);
            sqlCommand.Parameters.Add("@filterTime", SqlDbType.VarChar);

            //sqlCommand.Parameters["@jobDate"].Value = date2;
            sqlCommand.Parameters["@jobGroup"].Value = jobGroup;
            sqlCommand.Parameters["@jobName"].Value = process;
            sqlCommand.Parameters["@detailTable"].Value = detail_table;
            sqlCommand.Parameters["@filterBatchControl"].Value = rid;
            sqlCommand.Parameters["@filterDate"].Value = date;
            sqlCommand.Parameters["@filterTime"].Value =date;

            sqlConnection.Open();
            XYZFactorDetailGridCollection = FillGridEntity(sqlCommand);
        }
        return XYZFactorDetailGridCollection;
    }

    private static List<XYZFactorsModel> FillGridEntity(SqlCommand sqlCommand)
    {
        List<MBSFactorsModel> thegrid = new List<MBSFactorsModel>();
        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
        {
            while (sqlDataReader.Read())
            {
               try
                {

                    //if(sqlDataReader["last_factor"] != System.DBNull.Value)
                   //{
                   //    
                   //    thegrid.Add(new XYZFactorsModel
                   //    {
                   //        last_factor = Convert.ToDateTime(sqlDataReader["last_factor"])
                   //    });
                   //}
                   //else
                   //{
                   //    System.Diagnostics.Debug.WriteLine("null value was found in last factor field");
                  // }

                    thegrid.Add(new XYZFactorsModel
                    {
                        abc = sqlDataReader["abc"].ToString(),
                        abc2 = sqlDataReader["abc2"].ToString(),
                        abc3 = sqlDataReader["abc3"].ToString(),
                        abc4 = sqlDataReader["abc4"].ToString(),
                        abc5 = Convert.ToDecimal(sqlDataReader["abc5"]),
                 last_factor = Convert.ToDateTime(sqlDataReader["last_factor"]),

                    });


                }
                catch (Exception ex)
                {

                    System.Diagnostics.Debug.WriteLine(ex.Message);
                }

            }
        }
        return thegrid;
    }
}

Eric J. Eric J. · Accepted Answer · 2012-12-12T22:12:11

You need to check SqlDataReader.IsDBNull before assigning to a non-nullable type. If that returns true you need to decide how you want to initialize your non-nullable variable. After all, things like int and decimal cannot have a null value assigned to them.

Gets a value that indicates whether the column contains non-existent or missing values.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.isdbnull.aspx

A first chance exception of type 'System.InvalidCastException' occurred in mscorlib.dll Object cannot be cast from DBNull to other types

3 Answers