1

I wanted to display the data from JSON file to the gridview. I've managed to decode the JSON file and I was trying to bind with the gridview.

However, an error pops out.

The error is: Newtonsoft.Json.JsonSerializationException: 'Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1

The JSON code:

{
 "value":{

"Status": 2,
    "AffectedSegments": [
      {

        "Line": "NEL",
        "Direction": "HarbourFront",
        "Stations": "NE9,NE8,NE7,NE6", 
      "MRTShuttleDirection": "HarbourFront"}
      ,
      {
        "Line": "EWL",
        "Direction": "Simei", 
     "Stations": "NE9,NE8,NE7,NE6", 
    "MRTShuttleDirection": "HarbourFront"}],
    "Message": [
      {
        "Content": "0901hrs : NEL "
        "CreatedDate": "2018-03-16 09:01:53"
      }
    ]
  }
}

The code:

    public DataTable jsonDataDiplay()
    {
        StreamReader sr = new StreamReader(Server.MapPath("TrainServiceAlerts.json"));
        string json = sr.ReadToEnd();
        var table = JsonConvert.DeserializeObject<DataTable>(json);
        //DataSet ds = JsonConvert.DeserializeObject<Wrapper>(json).DataSet;
        return table;
    }

The design page:

 <asp:GridView ID="GridView2" runat="server">
     <Columns>
         <asp:BoundField DataField="Line" HeaderText="Line" />
         <asp:BoundField DataField="Direction" HeaderText="Direction" />
          <asp:BoundField DataField="Stations" HeaderText="Stations" />
          <asp:BoundField DataField="MRTShuttleDirection" HeaderText="MRTShuttleDirection" />


     </Columns>
 </asp:GridView>

I'm not sure how to solve the error. Please, help me and advise me! i have added " besides NE. It was there from the start in my json file just tht i didnt copy correctly here.

Thank you in advance!

5 Answers 5

2

First of all: Your JSON sample is not valid:

"Message": [
  {
    "Content": "0901hrs : NEL  <- ", is missing 
    "CreatedDate": "2018-03-16 09:01:53"
  }
]

Next problem is the that you can not deserialize your json directly to a datatable. Your data sits deep inside the hierachical structure, so you have to do a bit more work to convert this:

public DataTable jsonDataDiplay()
{
    StreamReader sr = new StreamReader(Server.MapPath("TrainServiceAlerts.json"));
    string json = sr.ReadToEnd();
    dynamic table = JsonConvert.DeserializeObject(json);
    DataTable newTable = new DataTable();
    newTable.Columns.Add("Line", typeof(string));
    newTable.Columns.Add("Direction", typeof(string));
    newTable.Columns.Add("Stations", typeof(string));
    newTable.Columns.Add("MRTShuttleDirection", typeof(string));

    foreach (var row in table.value.AffectedSegments)
    {
        newTable.Rows.Add(row.Line, row.Direction, row.Stations, row.MRTShuttleDirection);
    }
    return newTable;
}
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for your time and help! your solution helps to solve my error. Have a good week ahead!
0

This is a sample code that you need i think.

        //Random json string, No fix number of columns or rows and no fix column name.   
        string myDynamicJSON = "[{'Member ID':'00012','First Name':'Vicki','Last Name':'Jordan','Registered Email':'vicki.j @tacinc.com.au','Mobile':'03 6332 3800','MailSuburb':'','MailState':'','MailPostcode':'','Engagement':'attended an APNA event in the past and ventured onto our online education portal APNA Online Learning','Group':'Non-member'},{'Member ID':'15072','First Name':'Vicki','Last Name':'Jordan','Registered Email':'vicki.j @tacinc.com.au','Mobile':'03 6332 3800','MailSuburb':'','MailState':'','MailPostcode':'','Engagement':'attended an APNA event in the past and ventured onto our online education portal APNA Online Learning','Group':'Non-member'}]";  

        //Using dynamic keyword with JsonConvert.DeserializeObject, here you need to import Newtonsoft.Json  
        dynamic myObject = JsonConvert.DeserializeObject(myDynamicJSON);  

        //Binding gridview from dynamic object   
        grdJSON2Grid.DataSource = myObject;  
        grdJSON2Grid.DataBind();  

        //Using DataTable with JsonConvert.DeserializeObject, here you need to import using System.Data;  
        DataTable myObjectDT = JsonConvert.DeserializeObject<DataTable>(myDynamicJSON);  

        //Binding gridview from dynamic object   
        grdJSON2Grid2.DataSource = myObjectDT;  
        grdJSON2Grid2.DataBind();  

Comments

0

first of all your json is not well formed, at least in the question, i can't edit it as it is only missing a ", at line 21. Now to get to your problem, DeserializeObject<>() can't chew everything you throw at it, most of the times you would create a standalone class and pass that as the type, but in your use case, I can tell that you want to display the first array so you can get it with this:

var jsonLinq = JObject.Parse(json);
        // Find the first array using Linq
        var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
        var trgArray = new JArray();
        foreach (JObject row in srcArray.Children<JObject>())
        {
            var cleanRow = new JObject();
            foreach (JProperty column in row.Properties())
            {
                // Only include JValue types
                if (column.Value is JValue)
                {
                    cleanRow.Add(column.Name, column.Value);
                }
            }
            trgArray.Add(cleanRow);
        }
        return JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());

Comments

0

NewtonSoft was expecting the first thing it finds in the json, to be an array, not a single object.

Expecting something like:

  [ <-- ARRAY STARTS
    {"a":"a"},
    {"a":"b"}
  ]

If your json will always look like what you posted, and it's actually only a small sub-part of it that you want to break down to a datatable, you'll need to dig that out first

Alternatively, you can use some service like quicktype.io to create a set of classes that represent your json, so you can parse it into those classes, and use that as your datasource

Comments

0

As a start you need to validate your JSON.

I used this site to validate it which gave a pretty decent error message on to where your problem is

https://jsonlint.com/

Error: Parse error on line 20:
...": [{            "Content": "0901hrs : NEL           "
----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '[', got 'undefined'

This should be your starting point on fixing this.

I used the validator and a bit of research and found your issue is the following

        "Content": "0901hrs: NEL",

        "CreatedDate": "2018-03-16 09:01:53"

YOUR json seems to be incorrectly formatted ", adding this sorted validation.

From there i would also recommend using objects in c# for reading this file

to create these objects id suggest using this site

http://json2csharp.com/

this will convert any working json to c# objects which you can then read into

after fixing the issue this is how it will look once you have the objects

public class AffectedSegment
{
    public string Line { get; set; }
    public string Direction { get; set; }
    public string Stations { get; set; }
    public string MRTShuttleDirection { get; set; }
}

public class Message
{
    public string Content { get; set; }
    public string CreatedDate { get; set; }
}

public class Value
{
    public int Status { get; set; }
    public List<AffectedSegment> AffectedSegments { get; set; }
    public List<Message> Message { get; set; }
}

public class RootObject
{
    public Value value { get; set; }
}

From here you should be able to read the json into rootobject

 StreamReader sr = new StreamReader(Server.MapPath("TrainServiceAlerts.json"));
        string json = sr.ReadToEnd();
        var table = JsonConvert.DeserializeObject<RootObject>(json);

and then to bind the item to the grid simply do this

MyGridView.DataSource = RootObject
MyGridView.DataBind()

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.