X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Jason Parsing Problem, Access 2016    
post Nov 16 2019, 03:33 PM

Posts: 179
Joined: 11-January 08

Hi Guys
I have been using the code below to tease out the calendar ID and Name from a long Jason string for the last five years without problem.
This week, I had problems with MS Office and had to do a full repair.
Since then this code breaks at the line that says. strID = Left(strInput, InStr(1, strInput, ",") - 1)

I have checked it on lots of the databases I have done which use the same code and they all break at the same place.

This is an example of the Jason string from the Debug.Print line

StrInput = {"subcalendars":[{"id":189213,"name":"Andy Walker","active":true,"color":17,"overlap":true,"readonly":false,"creation_dt":"2014-04-09T11:58:48+00:00",

Private Sub Form_Open(Cancel As Integer)
       'Debug.Print Me.OpenArgs
   PopulateListbox (Me.OpenArgs)

End Sub

Private Sub PopulateListbox(ByVal strInput As String)

'      'Debug.Print " StrInput = " & strInput        *** The Jason string is definitely in strInput ***
    Dim strID As String, strName As String, strCalKey As String
    strCalKey = Forms!TeamupDetails.TeamupKey

'       'Clear the listbox of any pre-existing values

    While Me.List18.ListCount > 0
        Me.List18.RemoveItem 0

'       'Loop through the JSON data.
    While InStr(1, strInput, "id" & Chr(34) & ":") > 0

'       'Look for id": and grab the value immediately
'       'after that, up till the point where you see a comma.

        strInput = Mid(strInput, InStr(1, strInput, "id" & Chr(34) & ":") + 4)
        strID = Left(strInput, InStr(1, strInput, ",") - 1)

'       'Skip over "name":"

        strInput = Mid(strInput, InStr(1, strInput, ",") + 9)     '*** Breaks here ***

'       'Grab the name data up until the next quotation mark

        strName = Left(strInput, InStr(1, strInput, Chr(34)) - 1)
        Me.Text22 = strCalKey
        Me.List18.AddItem strID & "; " & strName

End Sub

Everything looks ok to me but it breaks with, Run-time error '5': Invalid procedure call or argument.

Any thoughts anyone, or a better way of teasing them out.

Many thanks for viewing.
Go to the top of the page
post Nov 16 2019, 04:50 PM

Posts: 179
Joined: 11-January 08

Problem Solved.

On inspection of the Jason string I found that TeamUp Calendar people in their infinite wisdom added a few more, ' id": ' strings into their Jason which were being picked up by my InStr() count.

I have changed the line to find, {"id": instead of id":

strInput = Mid(strInput, InStr(1, strInput, "{" & Chr(34) & "id" & Chr(34) & ":") + 6)

And it all works, for now.

This is what happens if you use non standard Jason Parsing.

Now I'll just wait for them to move the ID and Name to a different place on the string.

Thank you for viewing.

Go to the top of the page
post Nov 16 2019, 05:22 PM

UtterAccess VIP
Posts: 2,242
Joined: 4-June 18
From: Somerset, UK

Just in case you're interested, see JSON Analyse & Transform for Access

Colin (Mendip Data Systems)
Website, email
Go to the top of the page
post Nov 16 2019, 05:47 PM

Posts: 179
Joined: 11-January 08

Thank you Colin, I will.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    3rd April 2020 - 03:51 PM