difference between DE serializing a JSON string into a dataset and DE serializing to a data table, error thrown

Question

In vb.net i have the deserialization to a dataset working without a problem, i can later save to SQL. Deserialization to dataset Not sure why i cant similarly deserialize to a datatable, should be easy enough Deserialization to datatable

I get the follow error, " Unexpected JSON token when reading Data Table. Expected Start Array."

EDITED** Below is the problem code sample that is DE serializing to a data table.

 Imports System
 Imports Newtonsoft.Json
 Imports System.Data

Public Module Module1
  Public Sub Main()
    

    Dim jsonString As String = "{""rows"":[{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Imbalance (S44)"",""value"":""7.7"",""unit"":""""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Temp (S12)"",""value"":""76.0"",""unit"":""°F""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Flowrate (D6)"",""value"":""0.466"",""unit"":""g/h""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Flowrate (D4)"",""value"":""1.107"",""unit"":""g/h""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Bleach (R13)"",""value"":""1.0"",""unit"":""""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Retrans (A34)"",""value"":""6.7"",""unit"":""%""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Total Cl (S23)"",""value"":""1.341"",""unit"":""ppm""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.ACIDPID (A31)"",""value"":""25.7"",""unit"":""%""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.pH to PI (A33)"",""value"":""56.9"",""unit"":""%""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Cond (S11)"",""value"":""1355.0"",""unit"":""µS/cm""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.pH2 (S32)"",""value"":""7.91"",""unit"":""pH""},{""id"":""2141994"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:25:30.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:25:30.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Cond (S11)"",""value"":""1352.0"",""unit"":""µS/cm""}]}"

    Console.WriteLine(jsonString)
    
    Dim dt As DataTable
    dt  = JsonConvert.DeserializeObject(of DataTable)(jsonString)
    
    dt.TableName = "rows"
    
    Console.WriteLine("")
    Console.WriteLine("## " + dt.TableName + " ##")
    Console.WriteLine("-------------------")
    Console.WriteLine("")
    
    For Each r as DataRow in dt.Rows
    Console.WriteLine("id: " & r("id").ToString())
    Console.WriteLine("source: " & r("source").ToString())
    Console.WriteLine("type: " & r("type").ToString())
    Console.WriteLine("time: " & r("time").ToString())
    Console.WriteLine("timeOffset: " & r("timeOffset").ToString())
    Console.WriteLine("timeWithOffset: " & r("timeWithOffset").ToString())
    Console.WriteLine("creationTime: " & r("creationTime").ToString())
    Console.WriteLine("creationTimeOffset: " & r("creationTimeOffset").ToString())
    Console.WriteLine("creationTimeWithOffset: " & r("creationTimeWithOffset").ToString())
    Console.WriteLine("tagname: " & r("tagname").ToString())
    Console.WriteLine("value: " & r("value").ToString())
    Console.WriteLine("unit: " & r("unit").ToString())
    Console.WriteLine("")
    ' + " - " + r("id").ToString())
    next
    
  End Sub
End Module

Please [edit] your question to include a [mcve] in the question itself, rather than as external links. From [ask]: *Help others reproduce the problem... if your problem is with code you've written, you should include some...Include just enough code to allow others to reproduce the problem... If it is possible to create a live example of the problem that you can link to ... then do so - but also **copy the code into the question itself**. Not everyone can access external sites, and the links may break over time.* — dbc, May 01 '23 at 20:04
That being said, the JSON for a `DataTable` is an **array** of datarow objects, while the JSON for a `DataSet` is a JSON object with array-valued properties for each DataTable. There's an example in [this answer](https://stackoverflow.com/a/7650977) by Frank to [How to convert json into datatable?](https://stackoverflow.com/q/7641004). — dbc, May 01 '23 at 20:06
Not actually too sure what the question is. A DataSet has a tables property which is a collection of DataTables — Hursey, May 01 '23 at 21:01
post has been edited per dbc request. due to character limit the json string was shortened but should still produce the error. thank you for explaining the difference between the two de serialization methods. — Edgar, May 02 '23 at 14:08

Serge · Accepted Answer · 2023-05-01T22:02:17.337

1

you can only deserialize array to data table

Dim jsonArr As JArray = JObject.Parse(json)("rows")

Dim dt As DataTable  = jsonArr.ToObject(of DataTable)()

// or 

Dim ds As DataSet = JsonConvert.DeserializeObject(of DataSet)(jsonString)
        
Dim dt As DataTable = ds.Tables("rows")

edited May 01 '23 at 22:02

answered May 01 '23 at 21:18

Serge

40,935
4
18
45

ok, that is what i didn't understand, i simply did the latter of your suggestion. thank you Serge. – Edgar May 02 '23 at 13:56

difference between DE serializing a JSON string into a dataset and DE serializing to a data table, error thrown

1 Answers1