Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON to Datatable: Error converting Value

I have this JSON, which is valid according to JSONbeautify.com :

{
    "date_check": "2023-04-05",
    "updated_datas": [
        {
            "FIELD_NAME": "BLN_ADS_LINKS_DETECTED",
            "NEW_VALUE": false,
            "OLD_VALUE": true
        },
        {
            "FIELD_NAME": "ADS_LINKS_DETECTED",
            "NEW_VALUE": null,
            "OLD_VALUE": "?"
        }
    ]
}

I’d like to display it in a datatable.
I use this function:

 Public Function JsonStringToDataTable(ByVal json As String) As DataTable
        Dim jsonLinq = JObject.Parse(json)
        Dim srcArray = jsonLinq.Descendants().Where(Function(d) TypeOf d Is JArray).First()
        Dim trgArray = New JArray()

        For Each row As JObject In srcArray.Children(Of JObject)()
            Dim cleanRow = New JObject()

            For Each column As JProperty In row.Properties()

                If TypeOf column.Value Is JValue Then                   
                    cleanRow.Add(column.Name, column.Value)
                    End If
            Next

            trgArray.Add(cleanRow)
        Next

        Return JsonConvert.DeserializeObject(Of DataTable)(trgArray.ToString(), New JsonSerializerSettings With {.NullValueHandling = NullValueHandling.Ignore})
End Function

I got this exception:
Error converting value "?" to type 'System.Boolean'. Path '[1].OLD_VALUE', line 10, position 20.
I don’t understand: why a boolean?

Tags:

2

Answers


  1. 0

    Json.NET will use the first row to determine the datatype for column, so it will process OLD_VALUE as bool for this input json. Since you are already processing json manually you can try forcing all data being strings. Not fluent with VB but here is working snippet in C# to give you the idea (main idea is to use ToString on prop value when adding it to the new object – row.Add(prop.Name, prop.Value.ToString()), but there is some other stuff also):

    var json = ...; // string from question
var jToken = JObject.Parse(json)["updated_datas"];
var jArray = new JArray();
foreach (var child in jToken.Children<JObject>())
{
    var row = new JObject();
    foreach (var prop in child.Children<JProperty>())
    {
        row.Add(prop.Name, prop.Value.ToString());
    }
    jArray.Add(row);
}

var table = jArray.ToObject<DataTable>();

    Or you can create custom DataTableConverter which will infer correct type, like in this answer

    Login or Signup to reply.
  2. 0

    the easiest way to fix json would be to use a string replacement

     json = json.Replace("false",""false"").Replace("true",""true"")

    also try this instead of your tonns of code

    Dim dt As DataTable = JObject.Parse(json)("updated_datas").ToObject( Of DataTable)()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search