POST error using VBA (MS Access) for API to Neto site-CodePudding

I have an access database which I'm using to create a json POST API to a website utilising Neto (https://developers.neto.com.au/documentation/engineers/api-documentation). I am new to APIs, but have been researching for several months and making progress in understanding how it works. I have managed to get a 200 status response from the request which would indicate the header info (including authentication is correct) but error in relation to the body (I believe). Code as per below:

Dim reader As New XMLHTTP60
Dim username As String, APIkey As String
Dim strJson As String
 
strJson = "{" & _
  "'Filter': {" & _
    "'OrderStatus': 'Pick'," & _
    "'OutputSelector': [" & _
      "'OrderID'," & _
      "'ShippingOption'," & _
    "]," & _
  "}" & _
"}"

username = "xxx"
APIkey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

reader.Open "POST", "https://www.yoursite.co.nz/do/WS/NetoAPI", False 
reader.setRequestHeader "NETOAPI_USERNAME", username
reader.setRequestHeader "NETOAPI_KEY", APIkey 
reader.setRequestHeader "Content-Type", "application/json"
reader.setRequestHeader "Accept", "application/json"
reader.setRequestHeader "NETOAPI_ACTION", "GetOrder"
reader.send strJson

Debug.Print reader.Status

Debug.Print reader.responseText

error message:

{"CurrentTime":"2021-09-17 02:42:50","Ack":"Error","Messages":[{"Error":{"Message":"JSON Error","SeverityCode":"Error"},"Warning":{"Message":"Warning","SeverityCode":"Warning","Description":"'\"' expected, at character offset 1 (before \"'Filter': {'OrderSta...\")"}}]}

Initially i am just trying to retrieve 2 pieces of data (OrderID and ShippingOption) for any orders with status of Pick. i have tried replacing all ' with "" as i've seen in other posts along with a few other variations but with no luck.

Any help would be appreciated. Thanks

CodePudding user response：

You are using single quote instead of double quotes in your JSON
The value of Filter is a collection enclosed with [ ] so I believe you need to wrap Pick as well (despite there's only 1 value).
You have an extra , after ShippingOption when it's the last value so remove that.

Try this instead:

strJSON = "{" & _
  """Filter"": {" & _
    """OrderStatus"": [""Pick""]," & _
    """OutputSelector"": [" & _
      """OrderID""," & _
      """ShippingOption""" & _
    "]" & _
  "}" & _
"}"

Above will produce the JSON below:

{"Filter": {"OrderStatus": ["Pick"],"OutputSelector": ["OrderID","ShippingOption"]}}