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

nector

Member
Local time
Today, 03:39
Joined
Jan 21, 2020
Messages
408
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

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


VBA Code

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

Active member
Local time
Yesterday, 17:39
Joined
Jan 22, 2016
Messages
291
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.

Well-known member
Local time
Today, 02:39
Joined
Feb 2, 2023
Messages
901
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 :) :
Code:
#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
 
Last edited:

nector

Member
Local time
Today, 03:39
Joined
Jan 21, 2020
Messages
408
Many thanks to you people for assistance all is fine now

With
Regards
 

Users who are viewing this thread

Top Bottom