Dear All,I’m using this agent to update DB2 file and send a mail notification. This agent runs and send the mail as well as saves in lotus client but doesn’t update db2 when i run it manually…I checked the SQL in query analyser…nothing wrong with it…please help me
shana
Dim session As New NotesSession
Dim Currdb As NotesDatabase
Dim view As NotesView
Dim Manager As String
Dim doc As NotesDocument
'Set doc = session.DocumentContext
Set Currdb = session.CurrentDatabase
Set view = Currdb.GetView( "SerAgreeAgt_View" )
'===========Update DB2========================
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim rs As New ODBCResultSet
Dim view1 As NotesView
Dim ctldoc As notesdocument
Dim userName As String
Dim password As String
Dim Connt As String
Set view1 = Currdb.GetView( "SetUp_View" )
Set ctldoc = view1.GetFirstDocument
userName=ctldoc.User_Setup(0)
password =ctldoc.Pass_Setup(0)
Connt=ctldoc.Con_Setup(0)
Dim Edate,Rdate As Variant
Set doc = view.GetFirstDocument
view.AutoUpdate = False
While Not (doc Is Nothing)
Rdate = doc.renNotification(0)
Edate =doc.endDate(0)
Dim STA As String
Dim ANo As String
ANo=doc.agNo(0)
If Rdate=Today() Then
MailTo = doc.perRenewal(0)
subject = "Pending Maintenance Agreement"
doc.Status="Pending"
STA=doc.Status(0)
Call OnApproval(doc,MailTo,subject,"MainAgreement")
Elseif Edate =Today() Then
MailTo = doc.perRenewal(0)
'MailTo = rs.Approver(0)
'MailCC=rs.Approver(0)
subject = "Pending Overdue Maintenance Agreement"
doc.Status="Pending Overdue"
STA=doc.Status(0)
Call OnApproval(doc,MailTo,subject,"MainAgreement")
End If
Manager=Cstr(doc.Approver(0))
doc.FutureApp= Cstr(MailTo & "- Action - Renew")
doc.FutureApp_1= Cstr(Manager & "- Action - Authorise")
doc.FutureApp_2=""
doc.HCurrAppNo=4
Call con.ConnectTo(Connt, userName, password)
Set qry.Connection = con
Set rs.Query = qry
'qry.SQL="UPDATE WINAPPS.KLMAINT_AGREEMENT set Status_Code='" & STA & "' WHERE Agreement_No= '" & ANo & "' "
qry.SQL="UPDATE WINAPPS.KLMAINT_AGREEMENT set Status_Code='PEND' WHERE Agreement_No= '00016' "
rs.Execute
'Call doc.Save (False,False)
Set doc = view.GetNextDocument(doc)
Wend
con.Disconnect