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 ######################
martin05Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

madheeswarCommented:
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
madheeswarCommented:
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

                         
martin05Author Commented:
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.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

madheeswarCommented:
the first one is just a suggestion and without testing I have given.. ofcourse I would have analysed.. Sorry for that.

do one thing.. this is also not tested... Just I am providing the logic..
dim tmpclass() as string
dim tmpgoods() as string
k=0
If result.IsResultSetAvailable Then
                         Do
                              result.NextRow
redim preserve tmpclass(k)
redim preserve tmpgoods(k)
                                                     tmpgoods(k)=result.GetValue( "Goods" )
tmpclass(k)=result.GetValue( "Class" )
k=k+1
                         Loop Until result.IsEndOfData
Set docdialog = db.CreateDocument
                         Set uidoc = ws.CurrentDocument

docdialog.classNumber=tmpclass
                         docdialog.classDescription=tmpgoods
                         docdialog.caseNumber=doc.ix_prefix(0) + doc.ix_fnum(0)
                         docdialog.caseClient=doc.ix_client(0)

In this way, u can assign the values to the document.


Hope it provides some insight...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martin05Author Commented:
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.
martin05Author Commented:
Worked without me having to touch it, slotted right in to my code.

Thanks very much Madheeswar.
madheeswarCommented:
thanks for the points...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.