martin05
asked on
SQL > LotusScript Do Loop Syntax
Hi there,
I have a Notes Agent which runs a SELECT statement on a SQL DB, returns a value and populates a Notes dialog box.
My problem is that the SELECT statement may return more than one row and my code below currently only stores data from the last row it hits. I would like the data from each row to be appended to the previous data.
I think this can be done using the NotesItem Class, could you confirm this or suggest another way.
Thanks in advance
####################### CODE START ######################
If result.IsResultSetAvailabl e Then
Do
result.NextRow
classinf=result.GetValue( "Class" )
goods=result.GetValue( "Goods" )
Loop Until result.IsEndOfData
Set docdialog = db.CreateDocument
Set uidoc = ws.CurrentDocument
docdialog.classNumber=clas sinf
docdialog.classDescription =goods
docdialog.caseNumber=doc.i x_prefix(0 ) + doc.ix_fnum(0)
docdialog.caseClient=doc.i x_client(0 )
####################### CODE END ######################
I have a Notes Agent which runs a SELECT statement on a SQL DB, returns a value and populates a Notes dialog box.
My problem is that the SELECT statement may return more than one row and my code below currently only stores data from the last row it hits. I would like the data from each row to be appended to the previous data.
I think this can be done using the NotesItem Class, could you confirm this or suggest another way.
Thanks in advance
####################### CODE START ######################
If result.IsResultSetAvailabl
Do
result.NextRow
classinf=result.GetValue( "Class" )
goods=result.GetValue( "Goods" )
Loop Until result.IsEndOfData
Set docdialog = db.CreateDocument
Set uidoc = ws.CurrentDocument
docdialog.classNumber=clas
docdialog.classDescription
docdialog.caseNumber=doc.i
docdialog.caseClient=doc.i
####################### CODE END ######################
and I won't think u have placed the complete code..
I assume , u want to create documents for every row u go into the loop. then u can use this code:
If result.IsResultSetAvailabl e Then
Do
result.NextRow
Set docdialog = db.CreateDocument
Set uidoc = ws.CurrentDocument
docdialog.classNumber=resu lt.GetValu e( "Class" )
docdialog.classDescription =result.Ge tValue( "Goods" )
docdialog.caseNumber=doc.i x_prefix(0 ) + doc.ix_fnum(0)
docdialog.caseClient=doc.i x_client(0 )
Loop Until result.IsEndOfData
I assume , u want to create documents for every row u go into the loop. then u can use this code:
If result.IsResultSetAvailabl
Do
result.NextRow
Set docdialog = db.CreateDocument
Set uidoc = ws.CurrentDocument
docdialog.classNumber=resu
docdialog.classDescription
docdialog.caseNumber=doc.i
docdialog.caseClient=doc.i
Loop Until result.IsEndOfData
ASKER
I want to append the data from all of the rows in SQL to the same Notes document, for example if the SQL DB looks like this :
fnum Class Goods
Row1 A B C
Row2 A D E
Row3 A F G
and the SQL query is : select * from tab where fnum='A'
I want the notes document(which is a dialog box) to contain 3 fields which are as follows :
Fnum = A
Class = B D F
Goods = C E G
If you think posting the entire code would help let me know.
Also Madheeswar, correct me if I'm wrong but would your first suggestion not stick me in an infinite loop?
Thanks for your suggestions.
fnum Class Goods
Row1 A B C
Row2 A D E
Row3 A F G
and the SQL query is : select * from tab where fnum='A'
I want the notes document(which is a dialog box) to contain 3 fields which are as follows :
Fnum = A
Class = B D F
Goods = C E G
If you think posting the entire code would help let me know.
Also Madheeswar, correct me if I'm wrong but would your first suggestion not stick me in an infinite loop?
Thanks for your suggestions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think thats what I'm looking for. I'm in the office later today and I'll give it a go.
Thanks so far Madheeswar.
Thanks so far Madheeswar.
ASKER
Worked without me having to touch it, slotted right in to my code.
Thanks very much Madheeswar.
Thanks very much Madheeswar.
thanks for the points...
If result.IsResultSetAvailabl
Do
tmpcont:
result.NextRow
classinf=result.GetValue( "Class" )
goods=result.GetValue( "Goods" )
go to createdocs
Loop Until result.IsEndOfData
createdocs:
Set docdialog = db.CreateDocument
Set uidoc = ws.CurrentDocument
docdialog.classNumber=clas
docdialog.classDescription
docdialog.caseNumber=doc.i
docdialog.caseClient=doc.i
go to tmpcont
u can apply ther above logic