(LotusScript)How can update sql in notes document button?

Question

I need to update some fields in the oracle database, like the following code.

I set the condition to write the value, but only one data can update.

How to update the data within the conditions?

Sub Click(Source As Button)
    Dim con2 As New ODBCConnection
    Dim qry2 As New ODBCQuery
    Dim result2 As New ODBCResultSet
    If con2.ConnectTo("***","***","***") Then
        Set qry2.connection = con2
        Set result2.query = qry2
        
        mysql2 = "SELECT * FROM CB_A"
        mysql2 = mysql2 + " WHERE PONUM = 'FC950102' OR PONUM = 'FT940141'"
        qry2.SQL = mysql2
        result2.Execute
        If result2.IsResultSetAvailable Then
            result2.FirstRow
            sqlponum = result2.GetValue("PONUM")
            sqlcomp = result2.GetValue("PUR_COMP_NAME")
        End If
        result2.LastRow
        For i = 1 To result2.NumRows
            result2.CurrentRow = i
            sqlponum = result2.GetValue("PONUM")
            Msgbox sqlponum + Chr(10) + sqlcomp
            If j >= 0 And k >= 0 Then
                mysql2 = "UPDATE CB_A SET ORDER_FLAG = '" + order_flag + "' , AUCTION_STATUS = '" + auction_status + "' , AUCTION_DATE = '" + tempaucdate + "' , AUCTION_PLACE = '" + AP(j) + "' , AUCTION_TIME = '" + AT(k) + "'"
                mysql2 = mysql2 + " WHERE PONUM = '" + sqlponum + "'"
                k = k + 1
                If k = 4 Then
                    j = j + 1
                    k = 0
                End If
                qry2.SQL = mysql2
                result2.Execute
            End If
        Next
    End If
    result2.Close(db_close)
    con2.Disconnect
End Sub

Answer 1

I edited some coding to achieved what I want to do.

In the For...Next loop, has "result.Execute", it will that mysql1 to be reset.

So, I copied and pasted mysql1 in For...Next loop.

Then it's can be work.

The following is the final coding...

If con2.ConnectTo("***","***","***") Then
    Set qry2.connection = con2
    Set result2.query = qry2
    
    mysql1 = "SELECT * FROM CB_A WHERE ORDER_FLAG = '1' AND AUCTION_STATUS = '0'"
    qry2.SQL = mysql1
    result2.Execute
    result2.LastRow
    For i = 1 To result2.NumRows
        result2.CurrentRow = i
        mysql1 = "SELECT * FROM CB_A WHERE ORDER_FLAG = '1' AND AUCTION_STATUS = '0'"
        qry2.SQL = mysql1
        result2.Execute
        sqlponum = result2.GetValue("PONUM")
        sqlcomp = result2.GetValue("PUR_COMP_NAME")
        Msgbox sqlponum + Chr(10) + sqlcomp
        If j >= 0 And k >= 0 Then
            mysql2 = "UPDATE CB_A SET ORDER_FLAG = '" + order_flag + "' , AUCTION_STATUS = '" + auction_status + "' , AUCTION_DATE = '" + tempaucdate + "' , AUCTION_PLACE = '" + AP(j) + "' , AUCTION_TIME = '" + AT(k) + "'"
            mysql2 = mysql2 + " WHERE PONUM = '" + sqlponum + "' AND PUR_COMP_NAME = '" + sqlcomp + "'"
            qry2.SQL = mysql2
            result2.Execute
            k = k + 1
            If k = 4 Then
                j = j + 1
                k = 0
            End If
        End If
    Next    
End If