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?

Jithu ThomasPosted Jan 9, 2025, 5:45 PM
Update the SP as per above, the issue comes when there is no data in the table, the SQL
SUMfunction still returnsNULLinstead of0. When you try to cast thisNULLto an integer (Convert.ToInt32), you encounter theDBNullexception.Marius VasilePosted Jan 9, 2025, 5:52 PM
Thanks, that did the trick