How to handle null values when adding to a collection.(Mvc, C#)

Question

I am reading an excel file and adding the data column by column to a collection. Once it encounters a null value it blows out on the line below:

exampleDataList.Add(new PersonalData {firstname = col1Value.ToString(),lastname = col2Value.ToString(), address=col3Value.ToString(), salary=col4Value.ToString() });

[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
    const int startRow = 1;

    if (file != null && Path.GetExtension(file.FileName) == ".xlsx")
    {
        IList<PersonalData> exampleDataList = new List<PersonalData>();
        using(var excel = new ExcelPackage(file.InputStream))
        {
            //open and read the xlsx file.                   
                //Get the work book in the file
            ExcelWorkbook workBook = excel.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        //Get the first worksheet
                        ExcelWorksheet currentWorkSheet = workBook.Worksheets.First();


                        for (int rowNumber = startRow + 1; rowNumber <= currentWorkSheet.Dimension.End.Row; rowNumber++)
                        // read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
                        {
                            object col1Value = currentWorkSheet.Cells[rowNumber, 1].Value;
                            object col2Value = currentWorkSheet.Cells[rowNumber, 2].Value;
                            object col3Value = currentWorkSheet.Cells[rowNumber, 3].Value;
                            object col4Value = currentWorkSheet.Cells[rowNumber, 4].Value;                             

                            if ((col1Value != null && col2Value != null))
                            {
                                exampleDataList.Add(new PersonalData {firstname = col1Value.ToString(),lastname = col2Value.ToString(), address=col3Value.ToString(), salary=col4Value.ToString() });
                            }                                  

                        }

                        int myresultcount = WriteToDb(exampleDataList);
                    }

                }                  

        }
    }

    return RedirectToAction("Index");        
}

Here is my class

public class PersonalData
{
    public int Id { get; set; }
    public string  firstname { get; set; }
    public string lastname { get; set; }
    public string address { get; set; }
    public string salary { get; set; }
}

Error message am getting : object reference not set to an instance of an object

How can I correct this? What am I doing wrong. My goal is to write this to a database at the end of the day.

D Stanley · Accepted Answer · 2013-07-02 18:18:35Z

Check for nulls in columns 3 and 4:

if ((col1Value != null && col2Value != null && col3Value != null && col4Value != null))
{
    exampleDataList.Add(new PersonalData {firstname = col1Value.ToString(),lastname = col2Value.ToString(), address=col3Value.ToString(), salary=col4Value.ToString() });
}

Or if you want an empty string instead of NULL for columns 3 and 4:

if ((col1Value != null && col2Value != null))
{
    exampleDataList.Add(new PersonalData {
            firstname = col1Value.ToString(),
            lastname  = col2Value.ToString(), 
            address   = col3Value == null ? "" : col3Value.ToString(), 
            salary    = col4Value == null ? "" : col4Value.ToString() });
}

to simplify the NULL check you could leverage the behavior of + that uses empty strings for null values and automatically calls .ToString() for objects:

if ((col1Value != null && col2Value != null))
{
    exampleDataList.Add(new PersonalData
        {
            firstname = col1Value.ToString(),
            lastname  = col2Value.ToString(), 
            address   = "" + col3Value, 
            salary    = "" + col4Value
        });
}

sll · Accepted Answer · 2013-07-02 18:07:33Z

1

I believe you forgot checking value3 and value4 for null, or if these values allowed to be null just check them for null before doing ToString()

if (col1Value != null && col2Value != null && 
    col3Value != null && col4Value != null)
{
    exampleDataList.Add(new PersonalData 
     {
          firstname = col1Value.ToString(),
          lastname = col2Value.ToString(), 
          address = col3Value.ToString(), 
          salary = col4Value.ToString() 
     });
}

OR

if (col1Value != null && col2Value)
{
    exampleDataList.Add(new PersonalData 
    {
       firstname = col1Value.ToString(),
       lastname = col2Value.ToString(), 
       address = col3Value != null ? col3Value.ToString() : String.Empty, 
       salary = col4Value.ToString() != null ? col4Value.ToString() : String.Empty
    });
}

answered Jul 2, 2013 at 18:07

sll

62.7k22 gold badges109 silver badges157 bronze badges

1 Comment

Baba Over a year ago

Thanks. I want to provide for nulls. There will be some columns in the excel sheet that will be null. I just didn't want to be inserting null into the database and that explains why I am not testing col3Value and Col4Value. They are designed to accept null in the database. col1value and col2value cannot accept nulls in the database.

axeloide · Accepted Answer · 2013-07-02 18:20:56Z

You are missing some validation of the colXValue objects, which you should do before attempting to call the ToString() method on them.

It depends on how you want to deal with null values.

If you want to ignore any record which has null at any of the columns, then just extend your:

 if ((col1Value != null && col2Value != null))

to look like:

 if (    col1Value != null 
      && col2Value != null
      && col3Value != null
      && col4Value != null)

You could also want to convert null-values into empty strings, in which case you could do something like this:

 object col1Value = currentWorkSheet.Cells[rowNumber, 1].Value;
 object col2Value = currentWorkSheet.Cells[rowNumber, 2].Value;
 object col3Value = currentWorkSheet.Cells[rowNumber, 3].Value;
 object col4Value = currentWorkSheet.Cells[rowNumber, 4].Value;
 if (col1Value == null) col1Value = string.Emtpy;
 if (col2Value == null) col2Value = string.Emtpy;
 if (col3Value == null) col3Value = string.Emtpy;
 if (col4Value == null) col4Value = string.Emtpy;
 exampleDataList.Add(new PersonalData {firstname = col1Value.ToString(),lastname = col2Value.ToString(), address=col3Value.ToString(), salary=col4Value.ToString() });

Collectives™ on Stack Overflow

How to handle null values when adding to a collection.(Mvc, C#)

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related