Schedule Agent Problem..pls help

HCL Domino Domino Forum
1

Dear all,this agent runs on a view and sends mails for perticular users. My problem is it always runs for a single record. And sometimes it doesn’t run at all…even if i run it on manual. Pleas help me

Regards

shana

Sub Initialize

Dim session As New NotesSession

Dim Currdb As NotesDatabase

Dim CurrDoc As NotesDocument	

Dim view As NotesView

Dim doc As NotesDocument

Dim Manager As String 



Set Currdb = session.CurrentDatabase

Set CurrDoc = New NotesDocument(Currdb)

Set view = Currdb.GetView( "SerAgreeAgt_View" )



	'===========Update DB2========================

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim rs As New ODBCResultSet



Dim userName As String

Dim password As String



userName="DOMUSR"

password ="DOMPASS"

’ Call con.ConnectTo(“C601627B”, userName, password)

’ Set qry.Connection = con

’ Set rs.Query = qry

Dim Edate,Rdate As Variant



Set doc = view.GetFirstDocument



While Not (doc Is Nothing) 		

	Call con.ConnectTo("C601627B", userName, password)

	

	Set qry.Connection = con

	Set rs.Query = qry

	

	Rdate = doc.renNotification(0)

	

	Edate =doc.endDate(0)

	'Dim STA As String 

	

	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	

	Dim ANo As Variant 

	ANo=doc.agNo(0)

	

	qry.SQL="UPDATE WINAPPS.KLMAINT_AGREEMENT set Status_Code='" & doc.Status(0) & "' WHERE Agreement_No= '" & ANo & "' "

’ qry.SQL="UPDATE WINAPPS.KLMAINT_AGREEMENT set Status_Code=‘Pending’ WHERE Agreement_No= ‘00004’ "

	rs.Execute

	con.Disconnect

	

	Call doc.Save (False,True)	

	

	Set doc = view.GetNextDocument(doc) 

Wend

'===========End DB2 Update====================

End Sub

2

Subject: schedule Agent Problem…pls help

Could it be, that the modifications you make to the document force it to disappear from the view? Try to set view.AutoUpdate = False

before your loop.

Also, I’d like to know why you moved the connection stuff inside the loop.

3

Subject: RE: schedule Agent Problem…pls help

Thanks a lot…now the loop works…but it does’t update DB2 file. When i hardcode the Where clause it works…

4

Subject: RE: schedule Agent Problem…pls help

A small tip to begin with: LotusScript knows 3 different string delimiters:double quotes “stringOne”

pipes |stringTwo|

curly braces {string three}

There are tiny (but sometimes important) differences, like a string delimited by pipes can contain literal line breaks, but generally they work the same. When writing stuff like SQL statements or @Formula macros to evaluate, simply pick one of the three choices, that doesn’t interfere with the string you put together.

Your SQL statement would be a lot easier to read, if you use pipes instead of quotes, because of the single quotes in your query.

If it works with the hardcoded where clause, obviously the variable ANo does not contain a correct value. What kind of field is agNo? For your code to work, it must probably not be a number field, because DB2 will most probably want to be fed the number with leading zeros. Also, you shouldn’t declare ANo as Variant, but rather as the datatype fitting the job best.

Use the debugger to check the status of ANo before building the query and most probably you’ll see what’s wrong.

5

Subject: RE: schedule Agent Problem…pls help

Thanks a lot…i’ll try that out…