Okay I give up. I wrote a similar script that runs fine when the user has it open it will go to an external database and bring in information pertaining to what they entered.
Now I am trying to right the same script within an agent and I am having no success. I am getting no error, I have used the debug and still cannot figure out what i am doing wrong. Another pair of eyes would be appreciated. I only want to get the documents that are missing a SSN within the document.
Here is the script:
Sub Initialize
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim datasource As String
Dim resultcon As Variant
Dim mbr As Variant
Set db = session.CurrentDatabase
Set dc = db.UnprocessedDocuments
Set doc = dc.GetFirstDocument
datasource = “db.Server/OSI”
mbr = doc.GetItemValue(“MbrNbr”)'in the debugger I see the the result here
Set qry.Connection = con
Set result.Query = qry
resultcon = con.ConnectTo(datasource)
'Here I only want those docs that are missing SSN
If doc.SSN(0) = “” Then
If con.ConnectTo(“OSI”,“sa”,“sa”) Then
qry.SQL = "SELECT DISTINCT PERS.TAXID, PERS.FIRSTNAME, PERS.LASTNAME, ACCT.MEMBERAGREENBR FROM ACCT INNER JOIN PERS ON ACCT.TAXRPTFORPERSNBR=PERS.PERSNBR WHERE ACCT.MEMBERAGREENBR= ?mbr? + "
result.Execute
Do
result.NextRow
doc.FName = result.GetValue(“firstname”)
doc.LName = result.GetValue(“lastname”)
Call doc.Save(True,False)
Set doc = dc.GetNextDocument(doc)
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
con.Disconnect
End If
End If
End Sub