How to include parameters in Json - VBA

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Dear Experts
Thank you for the valuable help you have rendered to me from inception to now, I’m saying thank you to you all.

Now I still have one more issue which is now 80% done but still require your help. I have done a Json code integration with the help of this site , all I need for now is to include a parameter on the API so that only the specific data can be pulled using “GET”. The VBA code which is working is below and the Json data it is pulling.

Private Sub CmdEmp_Click()
Dim http As Object
    Dim JSON As Object
    Dim i As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set http = CreateObject("MSXML2.XMLHTTP")
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Contact")
    http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
    http.send
    Set JSON = ParseJson(http.responseText)
    i = 2
    For Each Item In JSON
        With rs
            .AddNew
            ![ID] = Item("id")
            ![FirstName] = Item("name")
            ![UserName] = Item("username")
            ![Email] = Item("email")
            ![City] = Item("address")("city")
            ![Phone] = Item("phone")
            ![WebSite] = Item("website")
            ![Company] = Item("company")("name")
            .Update
        End With
        i = i + 1
    Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set JSON = Nothing
    Set http = Nothing

    MsgBox ("complete")
End Sub 

Open in new window


Below is the data I’m populating in Ms Access table, for example instead of getting all the users, I want to populate only the selected user ID (txtUserID) from a form. Please note the above code is on onclick event behind the form called frmEmployees which also have the control called (txtUserID).
Here people are required select the user Id and click the command button to populate the exactly required data. Below is the Json data which is required to be populated.

[
  {
    "id": 1,
    "name": "Leanne Graham",
    "username": "Bret",
    "email": "Sincere@april.biz",
    "address": {
      "street": "Kulas Light",
      "suite": "Apt. 556",
      "city": "Gwenborough",
      "zipcode": "92998-3874",
      "geo": {
        "lat": "-37.3159",
        "lng": "81.1496"
      }
    },
    "phone": "1-770-736-8031 x56442",
    "website": "hildegard.org",
    "company": {
      "name": "Romaguera-Crona",
      "catchPhrase": "Multi-layered client-server neural-net",
      "bs": "harness real-time e-markets"
    }
  },
  {
    "id": 2,
    "name": "Ervin Howell",
    "username": "Antonette",
    "email": "Shanna@melissa.tv",
    "address": {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": "-43.9509",
        "lng": "-34.4618"
      }
    },
    "phone": "010-692-6593 x09125",
    "website": "anastasia.net",
    "company": {
      "name": "Deckow-Crist",
      "catchPhrase": "Proactive didactic contingency",
      "bs": "synergize scalable supply-chains"
    }
  },
  {
    "id": 3,
    "name": "Clementine Bauch",
    "username": "Samantha",
    "email": "Nathan@yesenia.net",
    "address": {
      "street": "Douglas Extension",
      "suite": "Suite 847",
      "city": "McKenziehaven",
      "zipcode": "59590-4157",
      "geo": {
        "lat": "-68.6102",
        "lng": "-47.0653"
      }
    },
    "phone": "1-463-123-4447",
    "website": "ramiro.info",
    "company": {
      "name": "Romaguera-Jacobson",
      "catchPhrase": "Face to face bifurcated interface",
      "bs": "e-enable strategic applications"
    }
  },
  {
    "id": 4,
    "name": "Patricia Lebsack",
    "username": "Karianne",
    "email": "Julianne.OConner@kory.org",
    "address": {
      "street": "Hoeger Mall",
      "suite": "Apt. 692",
      "city": "South Elvis",
      "zipcode": "53919-4257",
      "geo": {
        "lat": "29.4572",
        "lng": "-164.2990"
      }
    },
    "phone": "493-170-9623 x156",
    "website": "kale.biz",
    "company": {
      "name": "Robel-Corkery",
      "catchPhrase": "Multi-tiered zero tolerance productivity",
      "bs": "transition cutting-edge web services"
    }
  }
]

Open in new window


Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
I am under the impression that is nothing that you can do on your side...the server has to provide that functionality ....so it would a 2 step operation...1 "POST" to send the IDs you want and a response from the server that points to "specific" URL and then you perform the "GET"...but instead of what you have it would be something like : (fake)
http.Open "GET", "http://jsonplaceholder.typicode.com/users/312141232", False

Open in new window

At least this is how is done in other cases.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Chris,

  The part your missing here in your question is what the API is for the call and what it expects for the parameter.  

  Also I know you have most of this working at this point, so it's probably a little late for this, but you would have been better off to use:

https://github.com/VBA-tools/VBA-Web

  There's nothing wrong with what you've done already, but that lib would have made things a little easier for you.  Here's one of the examples from that lib:

Function GetDirections(Origin As String, Destination As String) As String
    ' Create a WebClient for executing requests
    ' and set a base url that all requests will be appended to
    Dim MapsClient As New WebClient
    MapsClient.BaseUrl = "https://maps.googleapis.com/maps/api/"
    
    ' Create a WebRequest for getting directions
    Dim DirectionsRequest As New WebRequest
    DirectionsRequest.Resource = "directions/{format}"
    DirectionsRequest.Method = HttpGet
    
    ' Set the request format -> Sets {format} segment, content-types, and parses the response
    DirectionsRequest.Format = Json
    
    ' (Alternatively, replace {format} segment directly)
    DirectionsRequest.AddUrlSegment "format", "json"
    
    ' Add parameters to the request (as querystring for GET calls and body otherwise)
    DirectionsRequest.AddQuerystringParam "origin", Origin
    DirectionsRequest.AddQuerystringParam "destination", Destination
    
    ' Force parameter as querystring for all requests
    DirectionsRequest.AddQuerystringParam "sensor", "false"
    
    ' => GET https://maps.../api/directions/json?origin=...&destination=...&sensor=false
    
    ' Execute the request and work with the response
    Dim Response As WebResponse
    Set Response = MapsClient.Execute(DirectionsRequest)
    
    If Response.StatusCode = WebStatusCode.Ok Then

Open in new window



Note that adding a parameter was simply:

    DirectionsRequest.AddQuerystringParam "origin", Origin

 The thing to keep in mind is that you are simply sending a request as a string and it's just a matter of formatting it correctly.

 What API is this? or post the API call that you want to use.

Jim.
I'm still using the converter by Gitub and correctly referenced , that is why I'm able to get the data from the internet to populate the table called contact. All I need is to put a parameter on the below string:

http.Open "GET", "http://jsonplaceholder.typicode.com/users/ID =1", False 

Open in new window


The code is not picking the data below:

{
    "id": 1,
    "name": "Leanne Graham",
    "username": "Bret",
    "email": "Sincere@april.biz",
    "address": {
      "street": "Kulas Light",
      "suite": "Apt. 556",
      "city": "Gwenborough",
      "zipcode": "92998-3874",
      "geo": {
        "lat": "-37.3159",
        "lng": "81.1496"
      }
    }

Open in new window



As you can see the ID = 1 as per Json , how do I place in the code properly so that the above data is picked? Here I just want to reference an Identifier with an ID as per Json which is ID = 1

Regards

Chris
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
RTM. We cannot know, how that API is implemented. When it's a RESTful API, then the URL should be

http://jsonplaceholder.typicode.com/users/1

Open in new window

When it supports a query interface, then it's maybe

http://jsonplaceholder.typicode.com/users?ID=1

Open in new window

But this is mere guessing.

p.s. you should make yourself familiar with the concepts of RESTful web services before using them.
Senior Developer
Commented:
btw, do yourself a favor and take a look at the separation of concerns principle.. e.g.

Private Sub CmdEmp_Click()

  On Local Error GoTo LocalError

  Dim db As DAO.Database
  Dim rs As DAO.Recordset

  Set db = CurrentDb
  Set rs = db.OpenRecordset("Contact")
  For Each Item In RetrieveUsers
    rs.AddNew
    rs![ID] = Item("id")
    rs![FirstName] = Item("name")
    rs![UserName] = Item("username")
    rs![Email] = Item("email")
    rs![City] = Item("address")("city")
    rs![Phone] = Item("phone")
    rs![WebSite] = Item("website")
    rs![Company] = Item("company")("name")
    rs.Update
  Next Item

  rs.Close
  Set rs = Nothing
  Set db = Nothing
  MsgBox ("Done.")
  Exit Sub

LocalError:
  'ToDo: Logging.
  MsgBox _
    "Error while saving users" & vbCrLf & _
    "Number: " & Err.Number & vbCrLf & _
    "Description: " & Err.Description

End Sub

Private Function RetrieveUsers() As Object

  On Local Error GoTo LocalError

  Dim http As Object

  Set http = CreateObject("MSXML2.XMLHTTP")
  http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
  http.send
  Set RetrieveUsers = ParseJson(http.responseText)
  Set http = Nothing
  Exit Function

LocalError:
  'ToDo: Logging.
  MsgBox _
    "Error while retrieving users" & vbCrLf & _
    "Number: " & Err.Number & vbCrLf & _
    "Description: " & Err.Description
  Set http = Nothing

End Sub

Open in new window

This way you can do proper error handling and logging.
Top Expert 2014

Commented:
You have a space character in your URL.

ste5an's first example (RESTful) is correct.  If you wanted user ID = 3, your URL would be:
http://jsonplaceholder.typicode.com/users/3

Open in new window


Although an example from the site implies that you could use ?userId=3, it doesn't seem to work.
Top Expert 2014

Commented:
This will work.  The "id" needs to be lower case, matching the JSON field name
http://jsonplaceholder.typicode.com/users/?id=3

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial