I am trying to parse the following complex JSON from the Zoho CRM API in VB

Question

I have the exact same issue as what's in this question

I was able to get the code to work in C#, however I'm writing my program in Visual Basic. I ran the code through a converter and got this :

Public Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim html As String = String.Empty
    Dim url As String = "https://crm.zoho.com/crm/private/json/Contacts/searchRecords?authtoken=0e0dd5f8153bc0a1299766b32d09e4f7&&scope=crmapi&fromIndex=1&toIndex=200&criteria=(Store:3110)"

    Dim request As HttpWebRequest = DirectCast(WebRequest.Create(url), HttpWebRequest)
    request.AutomaticDecompression = DecompressionMethods.GZip

    Using response As HttpWebResponse = DirectCast(request.GetResponse(), HttpWebResponse)
        Using stream As Stream = response.GetResponseStream()
            Using reader As New StreamReader(stream)
                html = reader.ReadToEnd()
            End Using
        End Using
    End Using


    Dim loContactList As New List(Of Contact)()
    Dim loContact As Contact = Nothing

    Dim respone As ResponseActual = JsonConvert.DeserializeObject(Of ResponseActual)(html)

    For Each row var In respone.Response.Result.Contacts.Row
        loContact = New Contact()

        Dim rowItem = row.FL.ToList()

        Try
            loContact.ContactID = rowItem.Where(Of FL)(Function(s, t) s.Val = "CONTACTID").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.OwnerID = rowItem.Where(Of FL)(Function(s, t) s.Val = "SMOWNERID").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.ContactOwner = rowItem.Where(Of FL)(Function(s, t) s.Val = "Contact Owner").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.FirstName = rowItem.Where(Of FL)(Function(s, t) s.Val = "First Name").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.LastName = rowItem.Where(Of FL)(Function(s, t) s.Val = "Last Name").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.Email = rowItem.Where(Of FL)(Function(s, t) s.Val = "Email").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.CreatorID = rowItem.Where(Of FL)(Function(s, t) s.Val = "SMCREATORID").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.CreatedBy = rowItem.Where(Of FL)(Function(s, t) s.Val = "Created By").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.ModifiedByID = rowItem.Where(Of FL)(Function(s, t) s.Val = "MODIFIEDBY").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.ModifiedBy = rowItem.Where(Of FL)(Function(s, t) s.Val = "Modified By").[Select](Function(x) x.Content).[Single]()
        Catch
        End Try
        Try
            loContact.CreatedTime = Convert.ToDateTime(rowItem.Where(Of FL)(Function(s, t) s.Val = "Created Time").[Select](Function(x) x.Content).[Single]())
        Catch
        End Try
        Try
            loContact.ModifiedTime = Convert.ToDateTime(rowItem.Where(Of FL)(Function(s, t) s.Val = "Modified Time").[Select](Function(x) x.Content).[Single]())
        Catch
        End Try
        Try
            loContact.LastActivityTime = Convert.ToDateTime(rowItem.Where(Of FL)(Function(s, t) s.Val = "Last Activity Time").[Select](Function(x) x.Content).[Single]())
        Catch
        End Try
        ListBox1.Items.Add(loContact.FirstName + " " + loContact.LastName)
        loContactList.Add(loContact)
    Next
End Sub
End Class
Public Class Contact
<JsonProperty(PropertyName:="CONTACTID")>
Public Property ContactID() As String
    Get
        Return m_ContactID
    End Get
    Set
        m_ContactID = Value
    End Set
End Property
Private m_ContactID As String
<JsonProperty(PropertyName:="SMOWNERID")>
Public Property OwnerID() As String
    Get
        Return m_OwnerID
    End Get
    Set
        m_OwnerID = Value
    End Set
End Property
Private m_OwnerID As String
<JsonProperty(PropertyName:="Contact Owner")>
Public Property ContactOwner() As String
    Get
        Return m_ContactOwner
    End Get
    Set
        m_ContactOwner = Value
    End Set
End Property
Private m_ContactOwner As String
<JsonProperty(PropertyName:="First Name")>
Public Property FirstName() As String
    Get
        Return m_FirstName
    End Get
    Set
        m_FirstName = Value
    End Set
End Property
Private m_FirstName As String
<JsonProperty(PropertyName:="Last Name")>
Public Property LastName() As String
    Get
        Return m_LastName
    End Get
    Set
        m_LastName = Value
    End Set
End Property
Private m_LastName As String
<JsonProperty(PropertyName:="Email")>
Public Property Email() As String
    Get
        Return m_Email
    End Get
    Set
        m_Email = Value
    End Set
End Property
Private m_Email As String
<JsonProperty(PropertyName:="SMCREATORID")>
Public Property CreatorID() As String
    Get
        Return m_CreatorID
    End Get
    Set
        m_CreatorID = Value
    End Set
End Property
Private m_CreatorID As String
<JsonProperty(PropertyName:="Created By")>
Public Property CreatedBy() As String
    Get
        Return m_CreatedBy
    End Get
    Set
        m_CreatedBy = Value
    End Set
End Property
Private m_CreatedBy As String
<JsonProperty(PropertyName:="MODIFIEDBY")>
Public Property ModifiedByID() As String
    Get
        Return m_ModifiedByID
    End Get
    Set
        m_ModifiedByID = Value
    End Set
End Property
Private m_ModifiedByID As String
<JsonProperty(PropertyName:="Modified By")>
Public Property ModifiedBy() As String
    Get
        Return m_ModifiedBy
    End Get
    Set
        m_ModifiedBy = Value
    End Set
End Property
Private m_ModifiedBy As String
<JsonProperty(PropertyName:="Created Time")>
Public Property CreatedTime() As DateTime
    Get
        Return m_CreatedTime
    End Get
    Set
        m_CreatedTime = Value
    End Set
End Property
Private m_CreatedTime As DateTime
<JsonProperty(PropertyName:="Modified Time")>
Public Property ModifiedTime() As DateTime
    Get
        Return m_ModifiedTime
    End Get
    Set
        m_ModifiedTime = Value
    End Set
End Property
Private m_ModifiedTime As DateTime
<JsonProperty(PropertyName:="Last Activity Time")>
Public Property LastActivityTime() As DateTime
    Get
        Return m_LastActivityTime
    End Get
    Set
        m_LastActivityTime = Value
    End Set
End Property
Private m_LastActivityTime As DateTime
End Class


Public Class ResponseActual

<JsonProperty("response")>
Public Property Response() As Response2
    Get
        Return m_Response
    End Get
    Set
        m_Response = Value
    End Set
End Property
Private m_Response As Response2
End Class

Public Class Response2

<JsonProperty("result")>
Public Property Result() As Result
    Get
        Return m_Result
    End Get
    Set
        m_Result = Value
    End Set
End Property
Private m_Result As Result

<JsonProperty("uri")>
Public Property Uri() As String
    Get
        Return m_Uri
    End Get
    Set
        m_Uri = Value
    End Set
End Property
Private m_Uri As String
End Class

Public Class Result

<JsonProperty("Contacts")>
Public Property Contacts() As Contacts
    Get
        Return m_Contacts
    End Get
    Set
        m_Contacts = Value
    End Set
End Property
Private m_Contacts As Contacts
End Class

Public Class Contacts

<JsonProperty("row")>
Public Property Row() As IList(Of Row)
    Get
        Return m_Row
    End Get
    Set
        m_Row = Value
    End Set
End Property
Private m_Row As IList(Of Row)
End Class

Public Class Row

<JsonProperty("no")>
Public Property No() As String
    Get
        Return m_No
    End Get
    Set
        m_No = Value
    End Set
End Property
Private m_No As String

<JsonProperty("FL")>
Public Property FL() As IList(Of FL)
    Get
        Return m_FL
    End Get
    Set
        m_FL = Value
    End Set
End Property
Private m_FL As IList(Of FL)
End Class

Public Class FL

<JsonProperty("content")>
Public Property Content() As String
    Get
        Return m_Content
    End Get
    Set
        m_Content = Value
    End Set
End Property
Private m_Content As String

<JsonProperty("val")>
Public Property Val() As String
    Get
        Return m_Val
    End Get
    Set
        m_Val = Value
    End Set
End Property
Private m_Val As String
End Class

I'm getting stuck on this line : For Each row var In respone.Response.Result.Contacts.Row

I know I can't have the var in there from C# but I can't figure out how to make this work.

just delete the `var` then it should work oob. If not make sure that `Option Infer On` is true for your code file/project. — Alex B., Dec 01 '16 at 12:53

Ňɏssa Pøngjǣrdenlarp · Answer 1 · 2016-12-01T15:48:33.683

The code you have can be simplified a great deal. The JSON classes are fine with one exception:

Public Class Row
    Public Property no As String
    Public Property FL As FL()
End Class

Since it is fixed, an array will do just fine.

But the contact class can be changed to do some of the work for you:

Public Class Contact
    Public Property ContactID As String
    Public Property OwnerID As String
    Public Property ContactOwner As String
    Public Property FirstName As String
    Public Property LastName As String
    Public Property Email As String
    Public Property CreatorID As String
    Public Property CreatedBy As String
    Public Property ModifiedByID As String
    Public Property ModifiedBy As String
    Public Property CreatedTime As DateTime
    Public Property ModifiedTime As DateTime
    Public Property LastActivityTime As DateTime

    Public Sub New(items As FL())

        ContactID = GetValue("CONTACTID", items)
        ContactOwner = GetValue("SMOWNERID", items)
        FirstName = GetValue("First Name", items)
        LastName = GetValue("Last Name", items)
        ...
        Dim tmp = GetValue("Created Time", items)
        CreatedTime = Convert.ToDateTime(tmp)

        tmp = GetValue("Modified Time", items)
        ModifiedTime = Convert.ToDateTime(tmp)

    End Sub

    ' the key corresponds to `val` in the json
    Private Function GetValue(key As String, items As FL()) As String
        Dim value = items.FirstOrDefault(Function(q) q.val = key)
        If value Is Nothing Then
            Return ""
        Else
            Return value.content
        End If
    End Function

    Public Overrides Function ToString() As String
        Return String.Format("{0}, {1}", LastName, FirstName)
    End Function
End Class

AutoProperites are used to cut down on the tedious boilerplate code
The constructor accepts an FL() and uses it to look up prop values itself
A GetValue() helper function does the actual look up so you Dont Repeat Yourself
The linq methods cribbed were a bit...crude. A little checking prevents all those empty Try/Catch blocks
The ToString() override provides the display for the ListBox, the list created is used as a DataSource so you dont have to make copies of bits of the data for the control.

Also, I am not sure the property mapping you seemed to want to use match are correct. For instance, CONTACTID and Contact Owner seem mixed up, and for some things I cant tell where they are supposed to come from like SMCREATORID.

Usage:

' form/class level list
Private contacts As List(Of Contact)

Elsewhere:

' using NEW here, empties the list from previous runs
contacts = New List(Of Contact)

Dim jstr = ...the json from whereever ...
Dim jobj = JsonConvert.DeserializeObject(Of ResponseActual)(jstr)

For Each r As Row In jobj.response.result.Contacts.row
    ' each contact item creates itself
    ' from the FL array
    contacts.Add(New Contact(r.FL))
Next

ListBox1.DataSource = contacts

Thanks so much for your help! I'm getting an error "An unhandled exception of type 'System.InvalidCastException' occurred in WindowsApplication1.exe Additional information: Unable to cast object of type 'System.Collections.Generic.List`1[WindowsApplication1.FL]' to type 'WindowsApplication1.FL[]'." at contacts.Add(New Contact(r.FL)) — Graham Ballard, Dec 01 '16 at 15:05
Sorry, the `FL` property in the `Row` class property is: `Public Property FL As FL()` you arent adding/removing from it so it doesnt need to be `IList` an array will do fine. You could also just tack `.ToArray()` on the new contact call for it to work. — Ňɏssa Pøngjǣrdenlarp, Dec 01 '16 at 15:11
Awesome, that worked! Will the override cause me to not be able to work with the other fields? The ones listed are only a sample, there are many more and I need to work with them all. For example I added email in, but it's throwing an error An unhandled exception of type 'System.NullReferenceException' occurred in WindowsApplication1.exe Additional information: Object reference not set to an instance of an object. when it reaches a record with a blank email at `Dim value = items.FirstOrDefault(Function(q) q.val = key).content` — Graham Ballard, Dec 01 '16 at 15:38
I answered my own question about the override. Still have the error when a field is empty though — Graham Ballard, Dec 01 '16 at 15:45
a) you can still use other properties (not fields) as normal b) Ack - I was too confused by the naming to map all the properties...see the (really) small change to `GetValue()` to allow for missing data - cant get the content for a null object. — Ňɏssa Pøngjǣrdenlarp, Dec 01 '16 at 16:24

I am trying to parse the following complex JSON from the Zoho CRM API in VB

1 Answers1