Link to home
Start Free TrialLog in
Avatar of martin05
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.IsResultSetAvailable 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=classinf
                         docdialog.classDescription=goods
                         docdialog.caseNumber=doc.ix_prefix(0) + doc.ix_fnum(0)
                         docdialog.caseClient=doc.ix_client(0)

#######################  CODE END ######################
Avatar of madheeswar
madheeswar
Flag of Singapore image

do it like this:
If result.IsResultSetAvailable Then
                         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=classinf
                         docdialog.classDescription=goods
                         docdialog.caseNumber=doc.ix_prefix(0) + doc.ix_fnum(0)
                         docdialog.caseClient=doc.ix_client(0)
go to tmpcont


u can apply ther above logic
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.IsResultSetAvailable Then
                         Do
                              result.NextRow
                            Set docdialog = db.CreateDocument
                         Set uidoc = ws.CurrentDocument
                         docdialog.classNumber=result.GetValue( "Class" )
                         docdialog.classDescription=result.GetValue( "Goods" )
                         docdialog.caseNumber=doc.ix_prefix(0) + doc.ix_fnum(0)
                         docdialog.caseClient=doc.ix_client(0)

                         Loop Until result.IsEndOfData

                         
Avatar of martin05
martin05

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.
ASKER CERTIFIED SOLUTION
Avatar of madheeswar
madheeswar
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Worked without me having to touch it, slotted right in to my code.

Thanks very much Madheeswar.
thanks for the points...