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?