How to pass a parameter using vba to an asp.net web api?

Question

I'm trying to use vba to post to a web api. The problem is that the parameter is blank in the web api. The web api is written in asp. I called the api from c# and it works but I can't get it to work from vba.

Here is the vba call to the api:

Sub Test()

    Dim http As New MSXML2.XMLHTTP60
   
    http.Open "POST", "http://10.5.72.172:108/api/vbaemail", True

    http.SetRequestHeader "Content-type", "application/json"
    
    Call http.send("abc")
   
End Sub

Here's my simple web api.

public void Post([FromBody] string strJson)
{
    SqlConnection cnnCom = null;
    SqlCommand cmdCom = null;
    string strSql = null;

    //write parameter to table
    cnnCom = new SqlConnection(CF.Get_Connection_String(CF.DatabaseName.COMMON));
    cnnCom.Open();
    strSql =
        "INSERT INTO Test( " +
        "F1) " +
        "VALUES( " +
        "'" + strJson + "')";
    cmdCom = new SqlCommand(strSql, cnnCom);
    cmdCom.ExecuteNonQuery();
    cnnCom.Dispose();
}

Here is the working c# call to the web api:

 static void Main(string[] args)
{
    HttpClient hc = new HttpClient();
    Task<HttpResponseMessage> task1 = null;

    //call web api
    task1 = hc.PostAsJsonAsync<string> 
        ("http://10.5.72.172:108/api/vbaemail", "abc");
}

see if this works for you https://gist.github.com/six519/5ed917850f402b94ee6b — Sowmyadhar Gourishetty, Jul 16 '21 at 18:25
How come you don't see any vb code there? Can you check here once https://gist.githubusercontent.com/six519/5ed917850f402b94ee6b/raw/611957db29d49800790da9b1ddd06ac2ed9cc239/JsonPost.vb — Sowmyadhar Gourishetty, Jul 16 '21 at 18:34
Does this answer your question? [Pass Parameters in VBA HTTP Post Request](https://stackoverflow.com/questions/18111884/pass-parameters-in-vba-http-post-request) — Seeds, Jul 16 '21 at 18:35
@CaiusJard - Since Json is a string it should work. That gave me an idea though, I tried passing in """abc""" and it worked. Apparently it's taking whatever is between the quotes which isn't going to work in Json for example "{"x":"abc"}" passes {. I'm guessing "Application/Json" is not a valid parameter to SetRequestHeader. Going to try playing around with other SetRequestHeader params. — Madison320, Jul 19 '21 at 14:47
Well.. json is a string, but the converse that "a string is json" (which is what youre claiming by setting a content-type of json) isn't necessarily true... — Caius Jard, Jul 19 '21 at 15:06
@CaiusJard I'm confused as to how this works. Even if I specify json I would've thought it would still pass a non json string to the web api and then try to deserialize it inside the web api. Is it checking to make sure the string is in json format before passing it to the web api? — Madison320, Jul 20 '21 at 14:06
Important note: A web API should *never* accept unvalidated string input and concatenate it with SQL; that is a good way to get hacked. See [Why do we always prefer using parameters with SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — John Wu, Jul 20 '21 at 14:22
If I was where you are, I think I'd probably open up Wireshark and look at the data going down the wire from c# vs vba and work out the difference — Caius Jard, Jul 20 '21 at 16:09

score 0 · Answer 1 · answered Jul 16 '21 at 18:42

0

http.Open "POST", "http://10.5.72.172:108/api/vbaemail", True

instead True set False

answered Jul 16 '21 at 18:42

Shaybakov

618
7
9

(For the benefit of other readers, True means asynchronous, False means synchronous) – Caius Jard Jul 16 '21 at 19:02
Same thing, the parameter passed to the web api is blank, also I get "operation aborted" during the send command. – Madison320 Jul 17 '21 at 15:53

score 0 · Accepted Answer · answered Jul 21 '21 at 13:51

I was able to get it to work by surrounding the string with single quotes. The problem is I can't use single quotes anywhere in the body being passed to the web api, although there's probably a way to escape them. In our case it's not a big deal, we're just trying to patch some old vba programs that are going away eventually.

Sub Test()

    Dim http As New MSXML2.XMLHTTP60
   
    http.Open "POST", "http://10.5.72.172:108/api/vbaemail", True

    http.SetRequestHeader "Content-type", "application/json"
    
    Call http.send("'" + strJson + "'")  
   
End Sub

How to pass a parameter using vba to an asp.net web api?

2 Answers2