Add a POST example

[PreciseAsteroid]

the get samples were very helpful but I am having issues processing a post request. I assume having a sample in hand would make things easier

[timhall]

@supermania I hadn't noticed that they were almost all GET request examples, sorry about that. I'll add some more examples shortly. For now, here is a detailed POST example:

Dim Client As New WebClient
Client.BaseUrl = "https://api.testing.com/"

Dim Request As New WebRequest
Request.Resource = "pages"
Request.Method = WebMethod.HttpPost

' Formatting
' ---------- '
' Simple: Request.Format sets four things:
'         1. Content-Type header
'         2. Accept header
'         3. Request Body conversion
'         4. Response Data conversion
Request.Format = WebFormat.Json

' Medium: Set separate request and response format
Request.RequestFormat = WebFormat.Json ' Set Content-Type and request converter
Request.ResponseFormat = WebFormat.Json ' Set Accept and response converter

' Advanced: Set separate everything
Request.RequestFormat = WebFormat.Json
Request.ContentType = "application/json"
Request.ResponseFormat = WebFormat.Json
Request.Accept = "application/json"

' Body
' ---- '
' a. Add body as string
Request.Body = "{""a"":123,""b"":[456, 789]}"

' b. Add parameters (converted with RequestFormat)
Request.AddBodyParameter "a", 123
Request.AddBodyParameter "b", Array(456, 789)
' -> Request.Body (for json): "{""a"":123,""b"":[456, 789]}"

' c. Add body as dictionary (or Collection or Array)
Dim Body As New Dictionary
Body.Add "a", 123
Body.Add "b", Array(456, 789)
Set Request.Body = Body
' -> Request.Body (for json): "{""a"":123,""b"":[456, 789]}"

' Add other things common to all Requests
Request.AddCookie "cookie", "abc"
Request.AddHeader "header", "def"
Request.AddQuerystringParam "querystring", "ghi"
' etc.

' To debug the request:
' 1. From VBA, Open Immediate Window (View > Immediate Window or Ctrl + g)
' 2. Type: RestHelpers.EnableLogging = True
' 3. Run request

' Make the request
Dim Response As WebResponse
Set Response = Client.Execute(Request)

' POST https://api.testing.com/pages?querystring=ghi
' header: def
' User-Agent: Excel Client v3.1.4 (https://github.com/timhall/Excel-REST)
' Content-Type: application/json
' Accept: application/json
' Content-Length: 23
' Cookie: cookie=abc
'
' {"a":123,"b":[456,789]}

' Handle the response
If Response.StatusCode = WebStatusCode.Created Then
    ' ...
End If

Let me know if you have any specific issues that you'd like an example for.

[PreciseAsteroid]

thanks @timhall ! I will try it out soon (sorry for the delay...just saw the notification today)

[alecoronel]

Hi,
I have a very simple POST example that I am unable to run successfully.
In a VBA module I have the following code:

    Dim rrRestResponse As RestResponse
    Dim rcRestClient As RestClient

    Set rcRestClient = New RestClient

    rrRestResponse = rcRestClient.PostJSON("http://localhost:55379/api/product", myJSONString) 

The Web.Api method gets invoked and successfully (according to response logging by Excel-REST etc) returns the HTTP Status code 201, but I keep getting a "Object variable of With block variable not set" error in VBA as soon as I leave the line that calls RestClient.PostJSON.

I cannot verify from where it is thrown. Have tried to debug the VBA code (Excel-REST), but I am only able to see that the error is thrown as soon as the Class_Terminate Seub of the RestRequest class has been executed (unclear by "who" - in the Call stack I can see - I guess it is just an automatic call when the object has been derefrenced as is being destroyed).

Please let me know if you have any idea about what the problem might be. It has been driving me nuts for some time now. Oh, and GET-requests give the exact same error.

With best regards,
acoronel

[timhall]

Hi @alecoronel, that happens when Set is missing for setting the value of an Object. Change the last line to the following and it should work:

Set rrRestResponse = rcRestClient.PostJSON("http://localhost:55379/api/product", myJSONString) 

[alecoronel]

Thanks! Actually after posting the question, I changed the implementation to use your more elaborate Http POST example above, and it worked like a charm. But I did not get why my simple approach did not work. Thanks for your explanation, I do feel quite ashamed though ;-) Too used to C# syntax ...

Really like your framework, incredibly useful in the application I am working on now!

[sssh]

Can any body tell me where I can get the RestClient, RestResponse, RestRequest files used in the above examples?

[alecoronel]

They were all upgraded and renamed in the new version, change the names
using "Web" instead of "Rest", get them from the download section. Works
just as well as before!
Den 28 mar 2015 14:50 skrev "AmeliePeter" notifications@github.com:

Can any body tell me where I can get the RestClient, RestResponse,
RestRequest files used in the above examples?

—
Reply to this email directly or view it on GitHub
#63 (comment).

[sssh]

Thanks @alecoronel for the quick reply. I am trying to POST a URL encoded JSON string a web service. So I tried to use Request.AddBody method as shown in Tim's example. But I cannot find Request.AddBody method in my WebRequest class. Could you please tell me how to resolve this?

[alecoronel]

I'm not by the computer right now, but did you try just Request.Body
instead?
Den 29 mar 2015 16:19 skrev "AmeliePeter" notifications@github.com:

Thanks @alecoronel https://github.com/alecoronel for the quick reply. I
am trying to POST a URL encoded JSON string a web service. So I tried to
use Request.AddBody method as shown in Tim's example. But I cannot find
Request.AddBody method in my WebRequest class.

—
Reply to this email directly or view it on GitHub
#63 (comment).

[timhall]

@ameliepeter I've updated the example for v4.0 to use the updated class names and the Body approach. To set the body, you can now use Body = (String or Array) or Set Body = (Dictionary or Collection).

[Codefornewbies]

Hi I am trying to run this POST code, but it is showing errors:

Function Creditrating(CompName As String) As String
    Dim companyname As String
    Dim strQuery As String
    Dim googleService As New MSXML2.XMLHTTP
    Dim rspText As String
    companyname = URLEncode(CompName)

    strQuery = "https://api.infoconnect.com/v1/companies/search?apikey=4iRztNp5euNzjK3i69xlnpIMa3K6AZCv"

    googleService.Open "POST", strQuery, False
    googleService.setRequestHeader "Content-Type", "application/json"
    googleService.setRequestBody "Fields": ["Id","Companyname","Assets","BankAsset",     "BigBusinessSegmentation","BusinessStatus"] "companyname": Doran Mechanical
    rspText = googleService.responseText

    Creditrating = "NA"

    If InStr(1, rspText, """CreditRatingScore"":""") Then
        Creditrating = Split(Split(rspText, """CreditRatingScore"":""")(1), Chr(34))(0)
    End If
End Function

I am writing this to display Fields - (ID, companyname, assets, bankasset, BigBusinessSegmentation, BusinessStatus). Please help

[timhall]

Hi @Codefornewbies, here's your code with VBA-Web:

Function CreditRating(CompanyName As String) As String
    Dim Client As New WebClient
    Client.BaseUrl = "https://api.infoconnect.com/v1/"

    Dim Request As New WebRequest
    Request.Resource = "companies/search"
    Request.Method = WebMethod.HttpPost
    Request.Format = WebFormat.Json
    Request.AddQuerystringParam "apikey", "4iRztNp5euNzjK3i69xlnpIMa3K6AZCv"
    Request.AddBodyParameter "CompanyName", CompanyName
    Request.AddBodyParameter "Fields", Array("Id", "CompanyName", "CreditRatingScore", "Assets", "BankAsset", "BigBusinessSegmentation", "BusinessStatus")

    Dim Response As WebResponse
    Set Response = Client.Execute(Request)

    ' Example Response:
    ' [{"ETag":"...","Id":"...","Links":[...],"CompanyName":"Doran Mechanical",...,"CreditRatingScore":"B"}]
    CreditRating = Response.Data(1)("CreditRatingScore")
End Function

It looks like the setRequestBody in your code is not valid json, which code be causing your error. As far as the display fields, casing is important (e.g. your code uses Companyname where it should be CompanyName) and from my testing it doesn't always return values for the requested fields (e.g. didn't return Assets for "Doran Mechanical").