Marius Vasile

Marius Vasile

  • 589
  • 1.9k
  • 150.8k

Failed to convert null value

Jan 9 2025 5:11 PM

I have a chart source from

protected void GetDataChart()
{
    int currentYear = DateTime.Now.Year;
    string spec = Session["Specialitate"].ToString();
    SqlConnection conn = new SqlConnection(connString);
    {
        string sqlQuery = "SELECT "+
        "SUM(CASE When DepCauzator LIKE 'A1.%' Then 1 Else 0 End) A1,"+
        "SUM(CASE When DepCauzator LIKE 'A2.%' Then 1 Else 0 End) A2,"+
        "SUM(CASE When DepCauzator LIKE 'A6.%' Then 1 Else 0 End) A6,"+
        "SUM(CASE When DepCauzator LIKE 'A8.%' Then 1 Else 0 End) A8,"+
        "SUM(CASE When DepCauzator LIKE 'A19%' Then 1 Else 0 End) A19,"+
        "SUM(CASE When DepCauzator LIKE 'A20%' Then 1 Else 0 End) A20,"+
        "SUM(CASE When DepCauzator LIKE 'A21%' Then 1 Else 0 End) A21,"+
        "SUM(CASE When DepCauzator LIKE 'A22%' Then 1 Else 0 End) A22,"+
        "SUM(CASE When DepCauzator LIKE 'A23%' Then 1 Else 0 End) A23 "+
        "FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND tc.Specialitate = @Spec";
        
        using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@CurrentYear", currentYear);
            cmd.Parameters.AddWithValue("@spec", spec);
            conn.Open();
            using (SqlDataAdapter da = new SqlDataAdapter())
            {
                DataTable resultTable = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(resultTable);

                if (resultTable.Rows.Count > 0)
                {
                    DataRow row = resultTable.Rows[0];

                    Series series = new Series
                    {
                        IsValueShownAsLabel = true,
                        ChartType = SeriesChartType.Column,
                        Palette = ChartColorPalette.BrightPastel,
                        Font = new Font(Chart1.Font.Name, 10, FontStyle.Regular),
                        //PostBackValue = "#VALX",
                        
                    };

                    series.Points.AddXY("DO", Convert.ToInt32(row["A1"]).ToString());
                    series.Points.AddXY("DE", Convert.ToInt32(row["A2"]).ToString());
                    series.Points.AddXY("SPFIC", Convert.ToInt32(row["A6"]).ToString());
                    series.Points.AddXY("DDMSM", Convert.ToInt32(row["A8"]).ToString());
                    series.Points.AddXY("DSMRU", Convert.ToInt32(row["A19"]).ToString());
                    series.Points.AddXY("DRSM-PSI", Convert.ToInt32(row["A20"]).ToString());
                    series.Points.AddXY("DPS", Convert.ToInt32(row["A21"]).ToString());
                    series.Points.AddXY("DDI", Convert.ToInt32(row["A22"]).ToString());
                    series.Points.AddXY("SITSC", Convert.ToInt32(row["A23"]).ToString());

                    Chart1.Series.Add(series);
                }

                Chart1.Titles.Add("Deficiente de " + spec);
                Chart1.Legends[0].Enabled = false;
                Chart1.ChartAreas["ChartArea1"].BackColor = Color.WhiteSmoke;

                Chart1.Series[0].Font = new Font(Chart1.Font.Name, 10, FontStyle.Regular);
                Chart1.Legends[0].Font = new Font(Chart1.Font.Name, 10, FontStyle.Regular);
                Chart1.Titles[0].Font = new Font(Chart1.Font.Name, 14, FontStyle.Regular);

                Chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;
                Chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
                Chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;

                ViewState["ChartMode"] = "Departament";
            }
        }
        DivCheck.Attributes["class"] = DivCheck.Attributes["class"].Replace("hidden", "mb-2 mt-4").Trim();
    }
}

but on an empty table (no data) I got the following error Object cannot be cast from DBNull to other types.

But the query should result in all data being 0 the chart should be just empty.

What am I doing wrong, please?


Answers (2)