Hi, I wrote a scheduled agent that goes to a DB2 table, performs a SQL query and if any matching records are found then it needs to go to another DB2 table and manipulate some fields and keep looping in the second table until all the relevant records are updated. The problem is once the matching records are found it keeps looping while updating and I can’t seem to come out of the update loop. Does any one know what is missing? Thanks
On Error Goto errorHandler
Dim EofF As Integer
Dim session As New NotesSession
Dim ThisDB As NotesDatabase
Dim doc As NotesDocument
Dim collection As Notesdocumentcollection
Dim view As NotesView
Set ThisDB = session.CurrentDatabase
If Con.ConnectTo("DATASOURCE","USERID","PASSWORD")Then
Print " routine connected to DB2 successfully!!!! "
'Set up the first table variables
Dim Res1 As New ODBCResultSet
Dim Qry1 As New ODBCQuery
Set Qry1.Connection = Con
'Converts today’s calendar date to Julian date
TodayInJulian = cjul(Date())
'Now scan the tables for the records where following selection criteria matches
Qry1.SQL=" SELECT PRMCU, PRLITM, PRAN8 " &_
" FROM table1, table2 WHERE " &_
" PCMCU = PRMCU AND " &_
" PCLITM = PRLITM AND " &_
" PCAN8 = PRAN8 AND "&_
" PCMCU in (' 2100001',' 2100004',' 2100007') AND " &_
" ( PRQTYR > 0 OR PRQTYW > 0 OR PRQTYJ > 0 ) AND PRUPMJ = " + TodayInJulian
Set Res1.Query = Qry1
Res1.Execute
If Res1.IsResultSetAvailable Then
Print " Result set is available now fetching......... "
Do
' ERROR in the logic here as it gets stuck in this loop
'Run the second query to perform updates in the matching records
Dim Res2 As New ODBCResultSet
Dim Qry2 As New ODBCQuery
Set Qry2.Connection = Con
'Print "Executing second SQL query........."
REM qry.SQL = "SELECT * FROM CONTACT WHERE ACCOUNT = ' " & getAccount & " ' "
Qry2.SQL= " UPDATE table SET PCNROU=PCTROU, PCTROU=' ', PCTFRQ=0, PCORDS=0 " &_
" WHERE PCMCU= '" & Res1.GetValue("PRMCU") &"' AND PCLITM= '"& Res1.GetValue("PRLITM") & "' AND PCAN8 =" & Res1.GetValue("PRAN8")
Set Res2.Query = Qry2
Res2.Execute
Loop Until Res1.IsEndOfData
Res2.Close(DB_Close)
Res1.Close(DB_Close)
End If
Print "Result set is not available, terminating...... "
Con.DisConnect
Else
Print "Could not connect to data source"
End If
Exit Sub
errorHandler:
Print "Problem in Update Rejections agent , Error # " & Err() &" Error Message = " & Error() & " Error on line = " & Cstr(Erl())
Messagebox Res.GetExtendedErrorMessage,, Res.GetErrorMessage
End Sub