Issue with Converting JSON to VB.NET datatable

Question

following is me JSON response text. I validated response and there is no error. I am trying to convert it to data table but it gives me null or error.

JSON Response:

    {
  "data": {
    "b2b": [
      {
        "inv": [
          {
            "itms": [
              {
                "num": 1,
                "itc": {
                  "tx_cs": 0,
                  "elg": "ip",
                  "tx_i": 180
                },
                "itm_det": {
                  "csamt": 0,
                  "rt": 18,
                  "txval": 1000,
                  "iamt": 180
                }
              }
            ],
            "val": 1000,
            "inv_typ": "R",
            "flag": "N",
            "updby": "S",
            "pos": "27",
            "idt": "24-07-2017",
            "rchrg": "N",
            "cflag": "U",
            "inum": "191001",
            "chksum": "52d0e920428464d85721bfcd7f3bfb4f16fd00d93a9df7d6a6f0814bed716c28"
          },
          {
            "itms": [
              {
                "num": 1,
                "itc": {
                  "tx_cs": 0,
                  "elg": "ip",
                  "tx_i": 18
                },
                "itm_det": {
                  "csamt": 0,
                  "rt": 18,
                  "txval": 100,
                  "iamt": 18
                }
              }
            ],
            "val": 100,
            "inv_typ": "R",
            "flag": "N",
            "updby": "S",
            "pos": "27",
            "idt": "24-07-2017",
            "rchrg": "N",
            "cflag": "U",
            "inum": "191002",
            "chksum": "aaa1efcf335549b58059c9f3d03807d7c41b007022216f8a90db12c60cd2b9ef"
          }
        ],
        "cfs": "N",
        "ctin": "1225586"
      }
    ]
  },
  "header": {
    "email": "[email protected]",
    "gstin": "65656451",
    "retperiod": "072017",
    "gst_username": "sampleaccount",
    "state_cd": "27",
    "ip_address": "192.168.2.200",
    "txn": "s4f5sdf54sdf5s4df5",
    "client_id": "removedfortest",
    "client_secret": "removedfortest",
    "authorization": "Basic a4s5df45asdf54as5d4f",
    "ret_period": "072017"
  },
  "status_cd": "1",
  "status_desc": "request succeeds"
}

Following are the classes i defined

Public Class Itc
        Public Property tx_cs As Integer
        Public Property elg As String
        Public Property tx_i As Integer
    End Class

    Public Class ItmDet
        Public Property csamt As Integer
        Public Property rt As Integer
        Public Property txval As Integer
        Public Property iamt As Integer
    End Class

    Public Class Itm
        Public Property num As Integer
        Public Property itc As Itc
        Public Property itm_det As ItmDet
    End Class

    Public Class Inv
        Public Property itms As Itm()
        Public Property val As Integer
        Public Property inv_typ As String
        Public Property flag As String
        Public Property updby As String
        Public Property pos As String
        Public Property idt As String
        Public Property rchrg As String
        Public Property cflag As String
        Public Property inum As String
        Public Property chksum As String
    End Class

    Public Class B2b
        Public Property inv As Inv()
        Public Property cfs As String
        Public Property ctin As String
    End Class

    Public Class Data
        Public Property b2b As B2b()
    End Class

    Public Class Header
        Public Property email As String
        Public Property gstin As String
        Public Property retperiod As String
        Public Property gst_username As String
        Public Property state_cd As String
        Public Property ip_address As String
        Public Property txn As String
        Public Property client_id As String
        Public Property client_secret As String
        Public Property authorization As String
        Public Property ret_period As String
    End Class

    Public Class Example
        Public Property data As Data
        Public Property status_cd As String
        Public Property status_desc As String
        Public Property header As Header
    End Class

and then i am trying to do following:

Following return me Null in datatable

Dim table as datatable = JsonConvert.DeserializeObject(Of RootObject(Of DataTable))(responsetext).Table

also tried: following does not allow me to type in rootofTable.data

    Dim rootOfList = JsonConvert.DeserializeObject(Of RootObject(Of List(Of data)))(responsetext)
Dim table As DataTable = rootOfTable.data

None of these are returning me values to datatable. Dataset stay null.

This is what i want

Please help to resolve.

Thanks

a datatable is essentially a flat, 2-dimensional structure. Your JSON data has multiple layers. It's unclear even what actual result you would expect to see in the DataTable, let alone how you imagine .NET is going to guess how it should interpret your data into a set of flat rows and columns. What result do you want, exactly? Please show an example. — ADyson
– ADyson, Commented Sep 16, 2019 at 9:32
P.S. if your code throws an error, you should always tell people what that error is. We can't guess, and it won't always be obvious just from reading the code. And clearly it's very hard to suggest a fix for an unknown error. Don't make it hard for people to help you, instead, tell them exactly what you are trying to achieve, and exactly what is going wrong when you try. Those two pieces of information are both missing from your question. Thanks. — ADyson
– ADyson, Commented Sep 16, 2019 at 9:34
As ADyson said, inv has many itms you have to treat it like an array like itms.num.ItmDet.csamt(index) let's say 0 to get the first csamt occurrence, 1 for second and so on.. — OctaCode
– OctaCode, Commented Sep 16, 2019 at 9:46
did you check this? stackoverflow.com/questions/41892086/… — OctaCode
– OctaCode, Commented Sep 16, 2019 at 9:56
You can't just say datatable=JSON, you have to set where each part of json has to go. — CruleD
– CruleD, Commented Sep 16, 2019 at 11:39

CruleD · Accepted Answer · 2019-09-16 12:19:07Z

There are at least 4 different ways it could be done (that I can think of) here is one example, I think it should be enough for you.

You basically just set which part of json goes into what column.

Imports Newtonsoft.Json.Linq

Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim JsonP As JObject = JObject.Parse(TextBox1.Text)
    Dim SetPointer As JToken = JsonP("data")("b2b")(0)("inv")

    For Each item In SetPointer
        Dim NewDR As DataRow = TempDT.NewRow
        NewDR("val") = item("val")
        NewDR("inv_typ") = item("val")
        NewDR("flag") = item("flag")
        NewDR("updby") = item("updby")
        NewDR("pos") = item("pos")
        NewDR("idt") = item("idt")
        NewDR("rchrg") = item("rchrg")
        NewDR("cflag") = item("cflag")
        NewDR("inum") = item("inum")
        NewDR("chksum") = item("chksum")
        NewDR("itms_num") = item("itms")(0)("num")
        NewDR("itms_itc_cs") = item("itms")(0)("itc")("tx_cs")
        TempDT.Rows.Add(NewDR)
    Next
    DataGridView1.DataSource = TempDT
End Sub

Dim TempDT As New DataTable
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    TempDT.Columns.Add("val")
    TempDT.Columns.Add("inv_typ")
    TempDT.Columns.Add("flag")
    TempDT.Columns.Add("updby")
    TempDT.Columns.Add("pos")
    TempDT.Columns.Add("idt")
    TempDT.Columns.Add("rchrg")
    TempDT.Columns.Add("cflag")
    TempDT.Columns.Add("inum")
    TempDT.Columns.Add("chksum")
    TempDT.Columns.Add("itms_num")
    TempDT.Columns.Add("itms_itc_cs")
End Sub
End Class

I didn't do everything, it's simply a demonstration of concept.

Use json visualizer. https://jsonformatter.curiousconcept.com/ then as you navigate trough it you write out the name if it's {}, number if it's an array [] .

Like you can totally write out ("data")("b2b")(0)("inv")(0)("itms")(0)("itc")("tx_cs") to get value from it. But it would be easier to navigate to somewhere closer and then just write a part of it like ("itms").

And you can't write a fixed path for arrays most of the time as they have dynamic amount of members most of the time, so instead of (0) you will have to do (x) and loop over items.

Thanks a lot @CruleD , your detailed code perfectly helped me to understand and resolved my issue.

Collectives™ on Stack Overflow

Issue with Converting JSON to VB.NET datatable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related