dcsimg
Results 1 to 6 of 6

Thread: VB.NET 2019 JsonToDataTable

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    195

    VB.NET 2019 JsonToDataTable

    Name:  112.jpg
Views: 98
Size:  44.0 KB

    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"

    Code:
    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}
    Last edited by ChenLin; Dec 2nd, 2019 at 07:38 PM.
    QQ: 289778005

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,450

    Re: VB.NET 2019 JsonToDataTable

    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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    195

    Re: VB.NET 2019 JsonToDataTable

    Quote Originally Posted by jmcilhinney View Post
    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.
    QQ: 289778005

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,450

    Re: VB.NET 2019 JsonToDataTable

    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.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,563

    Re: VB.NET 2019 JsonToDataTable

    ...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 &amp; 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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    195

    Re: VB.NET 2019 JsonToDataTable

    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!
    QQ: 289778005

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width