Cannot perform runtime binding on null reference C# Excel

Question

I have found many questions similar to this, but none that solve my issue. I am using SSIS and a C# script to read and modify the styling of an Excel workbook.

I get the following error "Cannot perform runtime binding on null reference". I understand what the error means; essentially you cannot use/reference something that is null. But I thought I was checking for all NULLs within my IF statement.

The code I'm using:

     int rows = xlWorkSheetTY.UsedRange.Rows.Count - 1;
     Excel.Range rge = xlWorkSheetTY.get_Range("J2:J" + rows, System.Type.Missing);
     foreach (Excel.Range item in rge.Cells)
     {
         if (item != null && (item.Value2 != null || item.Value2 != "" || item.Value2 != "NULL"))
         {
              decimal result = (decimal)0.00;
              if (decimal.TryParse(item.Value2.ToString(), out result))
                        {
                            if (result <= 20)
                            {
                                item.Interior.Color = Excel.XlRgbColor.rgbRed;
                                item.Font.Color = Excel.XlRgbColor.rgbWhite;
                            }
                        }
          }
      }

Anyone have any suggestions as to why I am getting this error and what I can do to rectify?

UPDATE: I have inserted a try catch statement to try and find some more details about why it was failing. It failed on a specific row every time. I have viewed this row in the raw Excel file and the data is empty.

How can I prevent it from parsing this field without doing what I already have - checking for nulls ect?

put a break in the code and step through it. You will likely get a better error message — KeithL
Could it be as simple as splitting up the conditions in your if statement? I.e. first only check if (item != null) and then have a nested if to check the other conditions if the object exists. — ImaginaryHuman072889
@ImaginaryHuman072889 Unfortunetley splitting the if statement didn't change anything - still the same error — Mark

Peter B Peter B · Accepted Answer · 2018-08-21T14:56:05

You seem to have an error in your logic:

if (item != null && (item.Value2 != null || item.Value2 != "" || item.Value2 != "NULL"))

Suppose that we have e.g. item.Value2 == null, then the condition item.Value2 != "" returns true, and it will enter the if-block, causing the error as you described.

In fact I think you should use only && and no ||:

if (item != null && item.Value2 != null && item.Value2 != "" && item.Value2 != "NULL")

Cannot perform runtime binding on null reference C# Excel

1 Answers