Solved Saving one field from Json String received in MS Access (1 Viewer)

[nector]

Sorry people I thought this was going to be very easy, but I seem to run into trouble again, I do not seem to be able to grab the resultCd field from the received Json string could it be there is an error again in my code:

Json string received

{"resultCd":"000","resultMsg":"It is succeeded","resultDt":”20231120193115","data”: null}

VBA Code

Dim Request As Object
Dim stUrl As String
Dim Response As String
Dim requestBody As String
stUrl = "http://localhost:8080/xxxxxxxxxxxxxxxxxxxxxxxxxx"
Set Request = CreateObject("MSXML2.XMLHTTP")
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .send requestBody
        Response = .responsetext
    End With
If Request.Status = 200 Then
MsgBox Request.responsetext, vbInformation, "Internal Audit Manager"
End If

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Json As Object
Set rst = db.OpenRecordset("select resultCd FROM [tblCustomerInvoice] WHERE [InvoiceID] = " & Me.CboDocument, dbOpenDynaset)
Set Json = JsonConverter.ParseJson(Request.responsetext)
'Process data.
rst.Edit
rst![resultCd] = Json("resultCd")
rst.Update

 

[tvanstiphout]

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Json As Object
Set rst = db.OpenRecordset("select resultCd FROM [tblCustomerInvoice] WHERE [InvoiceID] = " & Me.CboDocument, dbOpenDynaset)

You declare "db", but never set its value before using it.
Add:
set db = currentdb
before the "set rst" line.

 

[Josef P.]

Separate tasks in the code, then it is easier to find the error.

1. Get Json string from web server => check string
2. read required value from Json dictionary => check output from Json("resultCd")
3. save value with recordset (perhaps the string still needs to be converted to a date here)

Note:
You often show similar code. Is it always the same procedure you are experimenting with or do you repeat these lines of code in every procedure so that you have a better chance of introducing errors?

Even if it is like a fight against windmills :

#Const DEBUGCODE = true

Private Sub YourProcedure()

    Const Url As String = "http://localhost:8080/xxxxxxxxxxxxxxxxxxxxxxxxxx"
    Dim strData As String
    strData = .. ???

    Dim JsonString As String
    JsonString = RequestJsonString(Url, strData)

#If DEBUGCODE Then
    Debug.Print "JSON string: "; JsonString
    Stop
#End If

    Dim Json As Object
    Set Json = JsonConverter.ParseJson(JsonString)

#If DEBUGCODE Then
    Debug.Print "resultCd: "; Json("resultCd")
    Stop
#End If

    Dim ResultCd As String ' ... or Date?
    ResultCd = Json("resultCd")
    SaveToTable Me.CboDocument, ResultCd

End Sub

Private Function RequestJsonString(ByVal Url As String, ByVal RequestBody As String) As String

    Dim Request As Object
    Set Request = CreateObject("MSXML2.XMLHTTP")

    With Request
        .Open "POST", Url, False
        .setRequestHeader "Content-type", "application/json"
        .send RequestBody
        If .Status = 200 Then
            RequestJsonString = .responsetext
        Else
            Err.Raise vbObjectError, "RequestJsonString", "..."
        End If
    End With

End Function

Private Sub SaveToTable(ByVal InvoiceId As Long, ByVal ResultCd As {RequiredDataType})

    With CurrentDb.OpenRecordset("select resultCd FROM [tblCustomerInvoice] WHERE [InvoiceID] = " & InvoiceId, dbOpenDynaset)
        .Edit
        ![ResultCd] = ResultCd
        .Update
        .Close
    End With

End Sub

 

[nector]

Many thanks to you people for assistance all is fine now

With
Regards

 

[saketaco]

I have had success parsing JSON using the following module:

JSON Converter for VBA
VBA-JSON v2.3.1
by Tim Hall
github.com/VBA-tools/VBA-JSON

 

[gemma-the-husky]

I find the hardest is digging values out of arrays. It's tricky to get the right syntax for some details.

 

[saketaco]

I find the hardest is digging values out of arrays. It's tricky to get the right syntax for some details.