VB.NET 2019 JsonToDataTable

[ChenLin]

WindowsApp1.zip

Hello everyone, I have encountered difficulties in converting JSON to a table. Can anyone help me?
The reason for the error is that JSON contains a ampersand character, because JSON is separated by ampersands, it cannot be replaced directly, and the position and content are not fixed.
thank you all!

edit:The image is so big, why can't I see it clearly?
The JSON at the error is: "yy1,6yy"

Public Function JsonToDataTable(ByVal strJson As String) As DataTable
        Try
            strJson = strJson.Substring(strJson.IndexOf("[") + 1)
            strJson = strJson.Substring(0, strJson.IndexOf("]"))
            Dim rg As Regex = New Regex("(?<={)[^}]+(?=})")
            Dim mc As MatchCollection = rg.Matches(strJson)
            Dim tb As DataTable = Nothing

            For i As Integer = 0 To mc.Count - 1
                Dim strRow As String = mc(i).Value
                Dim strRows As String() = strRow.Split(","c)
                If tb Is Nothing Then
                    tb = New DataTable With {.TableName = ""}
                    For Each str As String In strRows
                        Dim dc As DataColumn = New DataColumn()
                        Dim strCell As String() = str.Split(":"c)
                        dc.ColumnName = strCell(0).ToString().Replace("""", "").Trim()
                        tb.Columns.Add(dc)
                    Next
                    tb.AcceptChanges()
                End If
                Dim dr As DataRow = tb.NewRow()
                For r As Integer = 0 To strRows.Length - 1
                    dr(r) = strRows(r).Split(":"c)(1).Trim().Replace("，", ",").Replace("：", ":").Replace("/", "").Replace("""", "").Trim()
                Next
                tb.Rows.Add(dr)
                tb.AcceptChanges()
            Next
            Return tb
        Catch ex As Exception
            MsgBox(ex.Message & vbNewLine & sInfo, 48, "JsonToDataTable")
        Finally
        End Try
    End Function

JSON:
{"result": {"record": 0,"pcount": 0,"rows": [{"purchOrderId": "78eb","middlePackage": 10,
"batchNum": "110006","batchGroup": 0,"prodDate": "2019-11","prodDate2": "2019-11-30",
"dueDate": "2020-06","dueDate2": "2020-06-30","inStoreDate": null,"storeNum": 2000.00,
"hightestPrice": 36.000,"price": 50.000,"tradePrice": null,"unit": "he","cfy": 0},
{"purchOrderId": "1a3","middlePackage": 1,"batchNum": "16","batchGroup": 1,"prodDate": "2015-07",
"prodDate2": "2015-07-31","dueDate": "2020-06","dueDate2": "2020-06-30","inStoreDate": null,
"storeNum": 0.000,"hightestPrice": 368.00,"price": 35.98,"tradePrice": null,"unit": "pp","cfy": 0}]},"statusCode": 200,"message": null}

[jmcilhinney]

Don't post pictures and links to entire projects. Post the relevant code directly, formatted appropriate. Post error messages directly. Post JSON content directly, or as a link to a file if it's very large. Of course, even if your JSON is very large, we don;t need all your JSON. We just need an example that demonstrates the issue.

[ChenLin]

Don't post pictures and links to entire projects. Post the relevant code directly, formatted appropriate. Post error messages directly. Post JSON content directly, or as a link to a file if it's very large. Of course, even if your JSON is very large, we don;t need all your JSON. We just need an example that demonstrates the issue.

Thank you jmcilhinney, the parsing code and JSON are posted.

[jmcilhinney]

You shouldn't really be parsing JSON manually like that. There are libraries that are tried and tested that will do the parsing for you, the most well-known being Json.NET.

[techgnome]

...because JSON is separated by ampersands, it cannot be replaced directly...

Now that simply is not true. Even in the JSON snip that you posted, there isn't a single ampersand that separates anything. JSON stands for JavaScript Object Notation and in its basic format it takes on the form of "key":"value" pairs. In some cases that value is a complex object, so it gets wrapped in {} to show it it's a larger object....if it's part of an array, then it uses [] ... bu there isn't anything that uses an ampersand for a separation of anything in JSON. At best an & has been expanded out to & for encoding purposes, but that's it.

However, if one were to use a proper JSON parser, this wouldn't be an issue since it would be able to take care of this for you and you wouldn't need to worry about it in the firs place.

-tg

[ChenLin]

I try to use newtonsoft. JSON to solve this problem, but there are still some mistakes: because my JSON often has some special characters.

Finally, my solution is to transfer these JSON characters in the form of parameters to SQL server, and use the database to solve it. So far, there are no more errors.

Thank you!