0

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?

Mark
  • 691
  • 7
  • 20
  • put a break in the code and step through it. You will likely get a better error message – KeithL Aug 20 '18 at 17:48
  • 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 Aug 20 '18 at 18:35
  • @ImaginaryHuman072889 Unfortunetley splitting the `if` statement didn't change anything - still the same error – Mark Aug 21 '18 at 12:04

1 Answers1

1

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")
Peter B
  • 22,460
  • 5
  • 32
  • 69
  • HA! It really was that simple - I managed to overcome using the method I posted but this is really what I needed. I cant believe I missed something so trivial. Thanks so much! – Mark Aug 22 '18 at 15:22