Agent that will update all docs in a database

I'm using the code below to connect to an AS/400 thru an odbc connection. I put this code into an agent and it works fine if I have the document open in edit mode. Is it possible to run the agent from a view and have it update all the documents in the database? When I select a document in a view and run the agent I get an error message "Object variable not set" Looking at this in the lotusscript debugger I can see that it bombs out on the Qry.SQL statement. What am I missing to get an agent to act on every document in the database?





Sub Initialize
      Dim A As String       '*LIMIT STATUS
      Dim H As String
      Dim O As String
      Dim P As String
      Dim S As String
      Dim T As String
      Dim Q As String
      Dim W As String
      
      A = "A"
      H = "H"
      O = "O"
      P = "P"
      S = "S"
      T = "T"
      Q = "Q"
      W = "W"
      
      
      Dim con As New ODBCConnection
      Dim qry As New ODBCQuery
      Dim res As New ODBCResultSet
      
      Dim WS As New notesUIWorkspace
      Dim uidoc As NotesUiDocument
      Dim doc As NotesDocument
      
      Set uidoc = ws.currentdocument
      'Set doc = uidoc.Document
      
      
status=Con.ConnectTo("datasource","id","pw")
If con.geterror<>DBstsSuccess Then
Messagebox con.GetExtendedErrorMessage,,"Could not make connection to AS/400 system"
            Exit Sub
      Else
      End If
      
      Set qry.Connection = Con
      Qry.SQL = "select * from SOFTTURND.TPRJTSK where TRSRC='"+uidoc.fieldgettext("resource")+"'" + "ORDER BY TPTY, TPROJ"
      If qry.geterror<>DBstsSuccess Then
            Messagebox qry.GetExtendedErrorMessage,,"SQL Error"
            con.Disconnect
            Exit Sub
      Else
      End If
      
      
      Set Res.Query = Qry
      
      Res.Execute
      If res.GetError<>DBstsSuccess Then
            Messagebox res.GetExtendedErrorMessage,,"Result Error"
            res.close(DB_CLOSE)
            con.Disconnect
            Exit Sub
            
      Else
            If Not res.IsResultSetAvailable Then
                  Messagebox "No Values Found for your Query"
            Else
                  
                  
                  Dim Counter As Integer
                  Counter = 1
                  
                  Do      
                        Call uidoc.FieldSetText("project_" + Cstr(Counter), "")
                        Call uidoc.FieldSetText("task_" + Cstr(Counter),  "")
                        Call uidoc.FieldSetText("description_" + Cstr(Counter), "")
                        Call uidoc.FieldSetText("requester_" + Cstr(Counter),  "")
                        Call uidoc.FieldSetText("priority_" + Cstr(Counter), "")
                        Call uidoc.FieldSetText("status_" + Cstr(Counter),  "")
                        Counter = Counter + 1                                    
                  Loop Until Counter = 49
                  
                  
                  Counter = 1                  
                  Do
                        res.NextRow
                        
                        If Res.GetValue("TSTS") = S  Or Res.GetValue("TSTS") = H Or Res.GetValue("TSTS") = A Or Res.GetValue("TSTS") = O Or Res.GetValue("TSTS") = P Or Res.GetValue("TSTS") = T Or Res.GetValue("TSTS") = Q Or Res.GetValue("TSTS") = W Then
                              
                              Call uidoc.FieldSetText("project_" + Cstr(Counter), Res.GetValue("TPROJ"))
                              Call uidoc.FieldSetText("task_" + Cstr(Counter),  Res.GetValue("TTASK"))
                              Call uidoc.FieldSetText("description_" + Cstr(Counter),  Res.GetValue("TDESC"))
                              Call uidoc.FieldSetText("requester_" + Cstr(Counter),  Res.GetValue("TRQSTR"))
                              Call uidoc.FieldSetText("priority_" + Cstr(Counter),  Res.GetValue("TPTY"))

Call uidoc.FieldSetText("status_" + Cstr(Counter),  Res.GetValue("TSTS"))
                              
                              
Counter = Counter + 1      
End If
                        
Loop Until res.IsEndOfData Or Counter = 49
                  
                  Counter = Counter - 1
Call uidoc.FieldSetText("count",Cstr(Counter))      
                  
Call uidoc.FieldSetText("date" , Cstr(Date$))
                  
      End If
      End If
      Res.Close(DB_close)
      Con.Disconnect
End Sub
LVL 1
roosterupAsked:
Who is Participating?
 
ArunkumarConnect With a Mentor Commented:
Hi Roosterup,

Include these lines to refer doc(The document you select from the view)

Dim ss As New notessession          
Dim db As notesdatabase
Set db = ss.CurrentDatabase
Set dc = db.UnprocessedDocuments
Set doc = dc.GetFirstDocument

Change all the
Call uidoc.FieldSetText("FieldName",Value) lines to read as

doc.FieldName = Value

MAKE Sure your lines for
Qry.SQL reads as follows

Qry.SQL = "select * from SOFTTURND.TPRJTSK where TRSRC = '"+ doc.resource(0) +"'" + "ORDER BY TPTY, TPROJ"


Also at the end of the script make sure you use the following to save the document

Call doc.Save(True,False)

Good Luck !

-Arun

PS: These Code changes will work for only one document that is selected in the view since we are not looping through documents.
0
 
roosterupAuthor Commented:
I would actually like to run this agent on a schedule and have it update all the documents.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.