Why is the following pie chart only displaying 1/3 of the sql count condition?

Question

Hi sorry to bother everyone again. Can someone please take a look at my code and correct me? I cannot seem to achieve the result i want in image in 3). My chart is displaying only 1/3 of the count conditions. How can i amend the following line so that i can read all of the count conditions to display the image in 3)?

private void BindChart()
{
    string query3 = string.Format("select count(case when tempValue < 24 then 1 end) as Cold, count(case when tempValue between 24 and 30 then 1 end) as Warm, count(case when tempValue > 30 then 1 end) as Hot FROM Datacentre");
    DataTable dt = GetData3(query3);

    string[] x = new string[] { "< 24 Degrees", "24 - 30 Degrees", "> 30 Degrees" };
    foreach (DataRow row in dt.Rows)
    {
        foreach (var value in x)
        {
            PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
            {
                Category = value,
                Data = Convert.ToDecimal(row["Cold"]) //reads 3 objects
            });//PROBLEM: but only displays total count of 18 for everything
        }
    }

    this.PieChart1.Enabled = true;
    this.PieChart1.Visible = true;
    this.PieChart1.DataBind();
}

1) Here is what i retrieved:

2) Here is my chart output:

3) This is the result what i want:

score 2 · Answer 1 · answered Jan 31 '16 at 20:05

You are only reading the cold part of the result returned. The SQL returns only one row so you need to read each column, you're currently only reading the Cold column.

for (var i=0; i < x.Length;i++)
    {
        PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
        {
            Category = value,
            Data = Convert.ToDecimal(row[i]) //reads 3 objects
        });//PROBLEM: but only displays total count of 18 for everything
    }

score 2 · Accepted Answer · edited May 23 '17 at 12:30

The problem is that in your code, you have hard coded "cold":

Data = Convert.ToDecimal(row["Cold"]) //reads 3 objects

And, you're looping through different rows, but you only have a single row:

foreach (DataRow row in dt.Rows)

If you only have a single row returning from your query, and you only have three categories, you could write this differently:

data = dt.Rows[0]
PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
        {
            Category = "< 24 Degrees",
            Data = Convert.ToDecimal(row["Cold"])
        });
PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
        {
            Category = "24 - 30 Degrees",
            Data = Convert.ToDecimal(row["Warm"])
        });
PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
        {
            Category = "> 30 Degrees",
            Data = Convert.ToDecimal(row["Hot"])
        });

Alternatively, you could use a loop, but this assumes your three columns in your data set are the same order as the three elements in your array x:

var x = new string[] { "< 24 Degrees", "24 - 30 Degrees", "> 30 Degrees" };
var row = dt.Rows[0]
for(var i=0; i < x.Length;i++)
{
    PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
        {
            Category = x[i],
            Data = Convert.ToDecimal(row[i])
        });
    }
}

To really make this generic, you could rewrite your query to return rows for each dataset with your Category and Value as columns:

Category       Value
--------------------
< 24 Degrees      18
24 - 30 Degrees   67
> 30 Degrees       2

And then you could loop through each row:

foreach (DataRow row in dt.Rows)
{
    PieChart1.PieChartValues.Add(new AjaxControlToolkit.PieChartValue
        {
            Category = row["Category"],
            Data = Convert.ToDecimal(row["Value"])
        });
    }
}

This would require a bit more complex query (see here, but then the underlying code that shows a pie chart could be generic for any pie charts you create.

thank you very much for helping! you've given quite a detailed answer. i like this one. votes up. — BlueTree, Jan 31 '16 at 20:21

Why is the following pie chart only displaying 1/3 of the sql count condition?

2 Answers2