How can I handle parsed JSON property/value not existing in VB.Net?

1

I shall get to the point and it is probably an easy answer for someone. The JSON being returned from HubSpot may or may not include a property such as phone and address because it is not filled out in HubSpot. This causes an error in the For Each block below:

System.NullReferenceException
HResult=0x80004003
Message=Object reference not set to an instance of an object.
Source=HubSpotGetAllCompanies

How can I handle it so that if there is no property for telephone for example, I can put in a DBNull value instead.

Thanks in advance!

Imports System.ComponentModel
Imports System.IO
Imports System.Net
Imports Newtonsoft.Json
Imports Newtonsoft.Json.Linq

Module Index

Sub Main()

    Dim counter = 0
    Dim offset As String = String.Empty
    Dim hasmore As Boolean = False
    Dim hapikey = "xxx"
    Dim hubSpot As New Dal.HubSpot.Pull


    'Create Table
    Dim companiesTable As DataTable = New DataTable()
    companiesTable.Columns.Add("PortalID")
    companiesTable.Columns.Add("CompanyID")
    companiesTable.Columns.Add("Company")
    companiesTable.Columns.Add("Website")
    companiesTable.Columns.Add("Address1")
    companiesTable.Columns.Add("City")
    companiesTable.Columns.Add("Country")
    companiesTable.Columns.Add("Postcode")
    companiesTable.Columns.Add("Telephone")
    companiesTable.Columns.Add("Ref")
    companiesTable.Columns.Add("VatCode")

    'Create Values


    'Loop as you can only return so many companies at once (250 is limit I believe)
    Do
        Dim url As String = String.Format("https://api.hubapi.com/companies/v2/companies/paged?hapikey={0}&properties=name&properties=website&properties=address&properties=city&properties=country&properties=zip&properties=phone&limit=10{1}", hapikey, offset)
        Dim httpWebRequest = CType(WebRequest.Create(url), HttpWebRequest)
        httpWebRequest.ContentType = "application/json"
        httpWebRequest.Method = "GET"
        Dim httpResponse = CType(httpWebRequest.GetResponse(), HttpWebResponse)

        Using streamReader = New StreamReader(httpResponse.GetResponseStream())
            Dim result = streamReader.ReadToEnd()

            Dim jObject As JObject = JObject.Parse(result)
            Dim jhasmore As JToken = jObject("has-more")
            Dim joffset As JToken = jObject("offset")
            Dim jcompanies As JToken = jObject("companies")

            If jhasmore.ToString().ToLower() = "true" Then
                hasmore = True
            Else
                hasmore = False
            End If

            offset = String.Format("&offset={0}", joffset.ToString())

            For Each item In jcompanies.ToList()
                Dim portalId = item("portalId").ToString()
                Dim companyId = item("companyId").ToString()
                Dim company = item("properties")("name")("value").ToString()
                Dim website = If(item("properties")("website")("value").ToString(), DBNull.Value)
                Dim address1 = If(item("properties")("address")("value").ToString(), DBNull.Value)
                Dim city = If(item("properties")("city")("value").ToString(), DBNull.Value)
                Dim country = If(item("properties")("country")("value").ToString(), DBNull.Value)
                Dim postcode = If(item("properties")("zip")("value").ToString(), DBNull.Value)
                Dim telephone = If(item("properties")("phone")("value").ToString(), DBNull.Value)
                Dim ref = DBNull.Value
                Dim vatCode = DBNull.Value

                companiesTable.Rows.Add(portalId, companyId, company, website, address1, city, country, postcode, telephone, ref, vatCode)

            Next

        End Using

        counter += 1
    Loop While hasmore

    hubSpot.GetAllHubSpotCompanies(companiesTable)

End Sub
json
vb.net
parsing
json.net
asked on Stack Overflow Jul 26, 2018 by Toby

1 Answer

0

Try using the Null Propagation Operator ? between any token accessors that may not be filled:

Dim telephone = If(item("properties")("phone")?("value").ToString(), DBNull.Value)

If a particular token is Nothing, it will return Nothing to the If and set the variable to DBNull.Value

answered on Stack Overflow Jul 26, 2018 by A Friend

User contributions licensed under CC BY-SA 3.0