Fw: Issue with AS/400 data being updated from Lotus Notes Script. -- DOMINO400

Any ideas?  Did I stump everyone with this?

Michael Schutte

----- Forwarded by Michael Schutte/Bob Evans on 04/28/2006 08:37 AM -----
                                                                           
             Michael_Schutte@b                                             
             obevans.com                                                   
             Sent by:                                                   To 
             domino400-bounces         domino400@xxxxxxxxxxxx              
             +michael_schutte=                                          cc 
             bobevans.com@midr                                             
             ange.com                                              Subject 
                                       Issue with AS/400 data being        
                                       updated from Lotus Notes Script.    
             04/27/2006 11:33                                              
             AM                                                            
                                                                           
                                                                           
             Please respond to                                             
              Lotus Domino on                                              
               the iSeries /                                               
                   AS400                                                   
             <domino400@midran                                             
                  ge.com>                                                  
                                                                           
                                                                           






Odd thing is going on here.  I'm trying to select all records from a file
on the AS/400 where a flag is equal to blank.  After the execute, I am
moving to the last row so that I can determine the value of NumRows.  The
value is 30, as I expected, however, the record navigation isn't working
properly.  I have listed the code below along with the log.  Also, for each
record read, I want to update that record with a value of "2" in the flag
so that it will not be downloaded again. Any help will be appreciated.
Sorry if I'm in the wrong area.

Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim selection As String
Dim collection As NotesDocumentCollection
Dim connection As New ODBCConnection
Dim Query As New ODBCQuery
Dim QResult As New ODBCResultSet
Dim Doc As NotesDocument
Dim CC As String
Dim sSQLString As String
Dim i As Variant
Dim QStatus As String

If connection.connectTo("AS400NAME", "USERNAME", "PASSWORD") Then
      QResult.CacheLimit = DB_NONE
      Set Query.connection = connection

      Query.SQL = "SELECT * FROM IMSTST.IMSPPIS WHERE ISNOTES=' ' "
      Set QResult.Query = Query
      QResult.ReadOnly = False
      QResult.AutoCommit = True
      QResult.Execute

      If QResult.IsResultSetAvailable = False Then
            LogError "There are no new Product Info Sheets."
      End If

      'Goto to last row
      QResult.LastRow

      'Starting with the last row, read through all records
      For i = QResult.NumRows To 1 Step -1

            QResult.CurrentRow = i
            Print "What is i? - " & i
            Print "Current Row = " & QResult.CurrentRow

            Set doc = db.createdocument
            doc.form = "ProductInfoSheets"

            Doc.PIUnitNo  = Trim(Qresult.GetValue("ISSTR"))
            Doc.PISpecNo  = Trim(Qresult.GetValue("ISITG"))
            Doc.Co_Name = Trim(Qresult.GetValue("ISDSC"))
            Doc.PIDate  = Trim(Qresult.GetValue("ISCRTE"))
            Doc.PIQtyReceived  = Trim(Cint(Qresult.GetValue("ISQTYR")))
            Doc.PIQtyRecType  = Trim(Qresult.GetValue("ISQTYRUMD"))
            Doc.PIDateReceived  = Trim(Qresult.GetValue("ISRCVE"))
            Doc.PIQtyInvolved  = Trim(Cint(Qresult.GetValue("ISQTYI")))
            Doc.PIQtyInvolvedAmt   =
Trim(Ccur(Qresult.GetValue("ISCOSTI")))
            Doc.PIQtyInvType   = Trim(Qresult.GetValue("ISQTYIUMD"))
            Doc.PIDistributor  = Trim(Qresult.GetValue("ISPRVN"))
            Doc.PIManufacturer   = Trim(Qresult.GetValue("ISMFRN"))
            Doc.PISentBy   = Ucase(Qresult.GetValue("ISNAME"))
            CC = Qresult.GetValue("ISLOCATE")
            If CC = "" Then
                  Doc.PICodeNo  = Qresult.GetValue("ISSCC")
            Else
                  Doc.PICodeNo = "NO CASE CODE FOUND"
            End If
            Doc.PIItemIssue  = Qresult.GetValue("ISISUD")
            Doc.PIItemStatus  = Qresult.GetValue("ISISCD")
            Doc.NatureofConcern  = Qresult.GetValue("ISPROB")
            Call Doc.Save(True, True)

            Call QResult.SetValue( "ISNOTES" , "2" )
            QStatus = Qresult.updaterow()
            If QStatus = True Then
                  Print "True - CurrentRow - " & QResult.CurrentRow
            End If

      Next

      Print "UpdateRow happened  " & QResult.CurrentRow & " - " &
QResult.NumRows

End If
QResult.Close(DB_CLOSE)
connection.Disconnect

Print "END OF PIS UPDATE"

----------------------------------------------------------------

You can see in the log that "i" has the correct value, however, after
record 27 is updated, CurrentRow is stuck on 27.  Anybody know why the
current row isn't being updated to the value of "i" after the fourth read.

BTW, When doing  "For i = 1 To QResult.NumRows Step 1".  The CurrentRow is
stuck on 30 from the beginning until i = 27.  At that time, currentRow will
equal 27, then 28, 29 and finally 30.  Resulting again in only the last 4
records being updated with a "2".  "i" is dimmed as a variant... do you
think it should be dimmed as an Integer or Long?




Log Statements:


04/27/2006 10:45:25   AMgr: 'Agent 'PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf' will run on behalf of 'Sheena Coyne/Bob Evans'
04/27/2006 10:45:25   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Started New Product Info Update agent
04/27/2006 10:45:25   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Last Row = 30
04/27/2006 10:45:25   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 30
04/27/2006 10:45:25   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 30
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 30
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 29
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 29
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 29
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 28
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 28
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 28
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 27
04/27/2006 10:45:26   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 26
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 25
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 24
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 23
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 22
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 21
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 20
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 19
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 18
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 17
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 16
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 15
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 14
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 13
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 12
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 11
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 10
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 9
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 8
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 7
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 6
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 5
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 4
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 3
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 2
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: What is i? - 1
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: Current Row = 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: True - CurrentRow - 27
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: UpdateRow happened  27 - 30
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: All New Records Pulled into Notes
04/27/2006 10:45:27   AMgr: Agent ('PIS INTO NOTES FROM AS400' in
'QualCont/TESTPIS.nsf') printing: END OF PIS UPDATE

Michael Schutte

_______________________________________________
This is the Lotus Domino on the iSeries / AS400 (Domino400) mailing list
To post a message email: Domino400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/domino400
or email: Domino400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/domino400.