JSON string from SQL Table - Bad values in the chart

Question

I trying to convert data from the table to JSON string. I use this code:

public string mies()
{
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(dod.conect))
    {
        using (SqlCommand cmd = new SqlCommand("select miesiac from Sales.dbo.Month", con))
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
        }
    }
    StringBuilder jsonString = new StringBuilder();
    if (dt.Rows.Count > 0)
    {  
        jsonString.Append("\"");
        jsonString.Append("[");
        for (int j = 0; j < dt.Columns.Count; j++)
        { 
             for (int i = 0; i < dt.Rows.Count; i++)
                if (i < dt.Rows.Count - 1)
                {
                     jsonString.Append("'" +  dt.Rows[i][j].ToString() + "'" +  ",");
                }
                else if (i == dt.Rows.Count - 1)
                {
                     jsonString.Append("'" +  dt.Rows[i][j].ToString() + "'");
                }
        }
        jsonString.Append("]");
        jsonString.Append("\"");
    }
    return jsonString.ToString();
}

Result this string is:

 "['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-7','2017-08','2017-09','2017-10','2017-11','2017-12','2018-01','2018-02','2018-03','2018-04']"

Then assign this string to the variable and pass it to the chart Javascript code:

       var Month= document.getElementById("<%= HiddenFieldMonth.ClientID %>").value;
       Highcharts.chart('Wykresliniowy', {
          chart: {
              type: 'line'
          },
          title: {text: 'Monthly Average Temperature' },
          subtitle: {text: 'Source: WorldClimate.com' },
          xAxis: { categories: JSON.parse(Month) },
          yAxis: {title: { text: 'Temperature (°C)' } },
          plotOptions: {  line: {  dataLabels: {enabled: true
                  },  enableMouseTracking: false
              }
          }, series: [{
              name: 'Berlin', data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8, 5, 10, 56, 10]
          }, { name: 'London', data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8,5,10,56,10]
          }]
      });

My chart have bad xAxis:enter image description here

I need like this: enter image description here

What I doing wrong? Please help me :(

score 2 · Answer 1 · answered Apr 17 '18 at 19:40

2

Don't do this yourself. There are libraries to do the heavy lifting for you. JSON.NET is the perfect such a library. See how simple it is.

answered Apr 17 '18 at 19:40

Bozhidar Stoyneff

3,576
1
18
28

score -1 · Answer 2 · answered Apr 17 '18 at 19:32

-1

Seems to me like the problem is your string

"['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-7','2017-08','2017-09','2017-10','2017-11','2017-12','2018-01','2018-02','2018-03','2018-04']"

is not valid JSON, because of the '. Replace them with ":

["2017-01"…])

answered Apr 17 '18 at 19:32

Daniel Bang

715
6
21

Why the downvote? Try: `JSON.parse("['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-7','2017-08','2017-09','2017-10','2017-11','2017-12','2018-01','2018-02','2018-03','2018-04']")` in your console. It gives you: `VM161:1 Uncaught SyntaxError: Unexpected token ' in JSON at position 1` – Daniel Bang Apr 17 '18 at 20:25
I don't know who gives you downvote. It's definitely not me :). But if I replace ' on " then the graph does not show up. I don't know why. – Marcin Wolanski Apr 18 '18 at 06:35

JSON string from SQL Table - Bad values in the chart

2 Answers2