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
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
User contributions licensed under CC BY-SA 3.0