Getting DBnull exception for datatable

July 4, 2023

I am setting values from excel to one of the column using datatable. In one of my columns, the datatable is null. I am getting this error:

object-cannot-be-cast-from-dbnull-to-other-types

Here is the code

ObjChangrePrePostHoto.Latitude = Convert.ToDecimal(dtHotoData.Rows[j]["Current Latitude"]);

Full code is below

for (int j = 0; j < dtHotoData.Rows.Count; j++)
{
    ObjChangrePrePostHoto.ChangeRequestID = ChangeRequestID;
    ObjChangrePrePostHoto.SAPID = dtHotoData.Rows[j]["Current SAPID"].ToString();
    ObjChangrePrePostHoto.State = dtHotoData.Rows[j]["R4GSTATE_CODE"].ToString();

    //  ObjChangrePrePostHoto.CRNO = dtHotoData.Rows[j]["Description as per CR"].ToString();
    ObjChangrePrePostHoto.HotoStatus = dtHotoData.Rows[j]["UPDATED_STATUS"].ToString();
    ObjChangrePrePostHoto.CreatedBy = CurrentUserName;
    ObjChangrePrePostHoto.CreatedDate = DateTime.Now;
    ObjChangrePrePostHoto.IPNonIP = dtHotoData.Rows[j]["IP_NONIP_SITE"].ToString();
    ObjChangrePrePostHoto.SrNo = dtHotoData.Rows[j]["Sr No"].ToString();
    ObjChangrePrePostHoto.SiteType = dtHotoData.Rows[j]["Site Type"].ToString();
    ObjChangrePrePostHoto.CRCategory = dtHotoData.Rows[j]["CR Category"].ToString();
    ObjChangrePrePostHoto.Remarks = dtHotoData.Rows[j]["Remarks"].ToString();
    ObjChangrePrePostHoto.CRJustifications = dtHotoData.Rows[j]["CR Justifications"].ToString();
    ObjChangrePrePostHoto.Latitude = Convert.ToDecimal(dtHotoData.Rows[j]["Current Latitude"]);
    ObjChangrePrePostHoto.Latitude = Convert.ToDecimal(dtHotoData["ReleaseId"] != System.DBNull.Value ? Convert.ToDecimal(dtHotoData["ReleaseId"]) : 0;
    ObjChangrePrePostHoto.Longitude = Convert.ToDecimal(dtHotoData.Rows[j]["Current Longitude"]);

    if (ObjChangrePrePostHoto.Save())
    {
        ChangeRequestID = ObjChangrePrePostHoto.ChangeRequestID;
    }
    else
    {
        ClientScript.RegisterStartupScript(Page.GetType(), "erroralert", "alert('Could not save the data for Site Addition flow');", true);
        return false;
    }
}

>Solution :

The error you are facing occurs because you are trying to convert a null value to decimal type. An "object-cannot-be-converted-from-dbnull-to-other-types exception" is thrown when you try to cast DBNull to another data type.

To solve this problem, you can check if the column value is DBNull before doing the conversion. You can use the Convert.IsDBNull() function to do this check. If the value is DBNull, you can assign a default value (for example, 0) to the Latitude property or take any other appropriate action according to your application’s logic.

Here is an example of how you can do this check:

// ...

object latitudeValue = dtHotoData.Rows[j]["Current Latitude"];
if (latitudeValue != DBNull.Value)
{
    ObjChangrePrePostHoto.Latitude = Convert.ToDecimal(latitudeValue);
}
else
{
    // Null value, assign a default value
    ObjChangrePrePostHoto.Latitude = 0;
}

// ...

In the example above, we first store the column value in a latitudeValue variable. Then we check if the value is different from DBNull.Value. If it is different, we can convert it to decimal. Otherwise, we assume that the value is null and assign 0 to the Latitude property.

Be sure to apply this logic to any columns that might contain null values before doing any type conversions.