Input string was not in a correct format from null data from DataTable

Question

I am creating an add-on program for SAP Business One that reads data from an Excel sheet and then loads the data to a Matrix. The Excel sheets will always have the same columns.

However, some columns may not be populated at all or some cells may be empty.

In my test Excel sheet, the column "DocEntry" is empty.

I get the following error at the line oDT.SetValue("hDocEntry", i, int.Parse(dt.Rows[i]["DocEntry"].ToString())); in below code because of null values:

Input string was not in a correct format

How do I filter the dt datatable such that no null values are brought forward to oDT?

i.e. nulls for strings are set to string. Empty and zeros for integers/decimals.

Any help appreciated.

Code:

System.Data.DataTable dt = new System.Data.DataTable();

OleDbCommand cmd = new OleDbCommand(sql, myConnection);
cmd.CommandType = CommandType.Text;

OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

adapter.Fill(dt);

if (dt != null)
{
    if (_form.DataSources.DataTables.Count.Equals(0))
    {
        _form.DataSources.DataTables.Add("dtData");
    }
    else
    {
        _form.DataSources.DataTables.Item("dtData").Clear();
    }

    oDT = _form.DataSources.DataTables.Item("dtData");

    // Get matrix                       
    _aphMatrix.Clear();

    oDT.Columns.Add("hNum", SAPbouiCOM.BoFieldsType.ft_Integer, 10);
    oDT.Columns.Add("hRecordKey", SAPbouiCOM.BoFieldsType.ft_Integer, 10);
    oDT.Columns.Add("hDocEntry", SAPbouiCOM.BoFieldsType.ft_Integer, 10);
    oDT.Columns.Add("hCardCode", SAPbouiCOM.BoFieldsType.ft_AlphaNumeric, 10);
    oDT.Columns.Add("hComments", SAPbouiCOM.BoFieldsType.ft_AlphaNumeric, 20);
    oDT.Columns.Add("hDocCurr", SAPbouiCOM.BoFieldsType.ft_AlphaNumeric, 4);
    oDT.Columns.Add("hDocDate", SAPbouiCOM.BoFieldsType.ft_Date);                          

    //Create SAP datatables rows
    oDT.Rows.Add(dt.Rows.Count);

    _form.Freeze(true);

    // Iterate through the datatable rows
    for (int i = 0; i <= dt.Rows.Count - 1; i++)
    {
        oDT.SetValue("hNum", i, i + 1);
        oDT.SetValue("hRecordKey", i, int.Parse(dt.Rows[i]["RecordKey"].ToString()));
        oDT.SetValue("hDocEntry", i, int.Parse(dt.Rows[i]["DocEntry"].ToString()));
        oDT.SetValue("hCardCode", i, dt.Rows[i]["CardCode"].ToString());
        oDT.SetValue("hComments", i, dt.Rows[i]["Comments"].ToString());
        oDT.SetValue("hDocCurr", i, dt.Rows[i]["DocCurrency"].ToString());
        oDT.SetValue("hDocDate", i, dt.Rows[i]["DocDate"]);                              
    }

    // Bind the data to the matrix columns
    _aphMatrix.Columns.Item("hNum").DataBind.Bind("dtData", "hNum");
    _aphMatrix.Columns.Item("hRecordKey").DataBind.Bind("dtData", "hRecordKey");
    _aphMatrix.Columns.Item("hDocEntry").DataBind.Bind("dtData", "hDocEntry");
    _aphMatrix.Columns.Item("hCardCode").DataBind.Bind("dtData", "hCardCode");
    _aphMatrix.Columns.Item("hComments").DataBind.Bind("dtData", "hComments");
    _aphMatrix.Columns.Item("hDocCurr").DataBind.Bind("dtData", "hDocCurr");
    _aphMatrix.Columns.Item("hDocDate").DataBind.Bind("dtData", "hDocDate");                           

    _aphMatrix.AutoResizeColumns();
    _aphMatrix.LoadFromDataSource();

    // Close the OLEdb connection
    if (myConnection != null)
    {
        myConnection.Close();
        myConnection.Dispose();
    }

    // Clear the datatable
    oDT.Clear();

    _form.Freeze(false);
}                 

Answer 1

first check is it null

if(dt.Rows[i]["DocEntry"]!= DBNull.Value)
{
   oDT.SetValue("hDocEntry", i, int.Parse(dt.Rows[i]["DocEntry"].ToString()));
}