Object cannot be cast from DBNULL to other types exception when populating CartesianChart

Question

I'm getting the following exception when trying to load my CartesianChart

Object cannot be cast from DBNULL to other types

I have the following query that is returning a few NULLs

Here is my SQL statement:

   SELECT DATENAME(MONTH, d.OPENED) AS MonthValue, 
   AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) 
   ELSE 0 END) AS SmallCommercialIndust_avg, 
   AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) 
   ELSE 0 END) AS Residential_avg
   FROM hb_Disputes d
   WHERE YEAR(d.OPENED) = YEAR(GETDATE())
   GROUP BY DATENAME(MONTH, d.OPENED)
   ORDER BY MIN(d.OPENED);

Here is the output:

MonthValue  SmallCommercialIndust_avg     Residential_avg
----------------------------------------------------------
January             0                         0
February            0                         0
March               NULL                      0
April               0                         0
May                 0                         NULL
June                0                         NULL
July                73                        0
August              123                       0
September           0                         136
October             166                       0
November            169                       0
December            0                         NULL

Here is my WPF code to load my CartesianChart:

        private void ChartValues()
    {
        // Defines the variable for differnt lines.
        List<double> allValues = new List<double>();
        List<double> someValues = new List<double>();


        try
        {
            SqlConnection connection = new SqlConnection("Data Source=WINDOWS-B1AT5HC\\SQLEXPRESS;Initial Catalog=CustomerRelations;Integrated Security=True;");


            string selectQuery = ("SELECT DATENAME(MONTH, OPENED) AS MonthValue, SUM(CASE WHEN d .ASSGNTO = 'E099255' AND d .REV_CLS = 2 THEN 1 ELSE 0 END) AS SmallCommercialIndust, AVG(CASE WHEN d .ASSGNTO = 'E099255' AND d.REV_CLS = 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) ELSE 0 END) AS SmallCommercialIndust_avg, SUM(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN 1 ELSE 0 END) AS Residential, AVG(CASE WHEN d.ASSGNTO = 'E099255' AND d.REV_CLS <> 2 THEN DATEDIFF(day, d.DATERSLVD, d.OPENED) ELSE 0 END) AS Residential_avg FROM hb_Disputes AS d WHERE(YEAR(OPENED) = YEAR(GETDATE())) GROUP BY DATENAME(MONTH, OPENED) ORDER BY MIN(OPENED)");
            connection.Open();
           using  SqlCommand command = new SqlCommand(selectQuery, connection);

            SqlDataReader sqlReader = command.ExecuteReader();

            while (sqlReader.Read())
            {

                {
                    // Select the values from the columns selected 
                    allValues.Add(Convert.ToDouble(sqlReader["SmallCommercialIndust_avg"]));
                    someValues.Add(Convert.ToDouble(sqlReader["Residential_avg"]));
                }
                // Starts new line series.
                SeriesCollection = new SeriesCollection
                {
                    new LineSeries
                    {
                        Title = "Residential",
                        Values = new ChartValues<double>(allValues),
                        LineSmoothness = 1, //0: straight lines, 1: really smooth lines

                    },
                    new LineSeries
                    {
                        Title = "Small Commercial Indust",
                        Values = new ChartValues<double>(someValues),
                        LineSmoothness = 1, //0: straight lines, 1: really smooth lines

                    }
                    };

                    Labels = new[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
                    YFormatter = value => value.ToString("N");

                    DataContext = this;
            }
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }            
    }

Answer 1

You need to check for DBNull before assigning a variable

    // Check for DBNull and then assign the variable
    if (sqlReader["SmallCommercialIndust_avg"] != DBNull.Value)
    SmallCommercialIndustValues.Add(Convert.ToInt32(sqlReader["SmallCommercialIndust_avg"]));

    // Check for DBNull and then assign the variable
    if (sqlReader["Residential_avg"] != DBNull.Value)
    ResidentialValues.Add(Convert.ToInt32(sqlReader["Residential_avg"]));