• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

Best Way to Handle Loop within Loop

Hello all.  I have an app where I have a main stored procedures that pulls records and then is looped through for each record.   Then within that loop I have to pull all the detail for the order number that is grabbed from the 1st Outer Loop.  How can I handle this best without having to hit the database each time again and run the query again over and over.
0
sbornstein2
Asked:
sbornstein2
  • 4
1 Solution
 
Shiju SasidharanCommented:
Instead of using loops, make it a single query using INNER JOIN
0
 
Shiju SasidharanCommented:
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sbornstein2Author Commented:
I understand that and your right I can join but here is what I am trying to get at.  So lets say I have the two tables with all the fields from both table then I would have something like this.

T1 = Table
T2 = Table2

T1.OrderNumber, T1.InvoiceNumber, T1.CustomerName, T2.SKUNumber, T2.QTY, T2.Price

Now whats going to happen is I will get ,multiple records per order because 1 order will have many details/SKU number records attached to the order number

T1.OrderNumber, T1.InvoiceNumber, T1.CustomerName, T2.SKUNumber, T2.QTY, T2.Price
OD23232323        54454                    ABC Company        4400                 5           6.00
OD23232323        54454                    ABC Company        8800                 12         8.05
OD23232323        54454                    ABC Company        7832                 77        11.35

Ok so I have this no problem.  Now how within my two loops do the following:
I need to through outer loop 1 write out a record that shows like this:

Order:
OrderNumber   InvoiceNumber   CustomerName
OD23232323    54454               ABC Company

Order Details:
SKU(S)    QTY   Price
4400       5       6.00
8800       12     8.05
7832       77    11.35

This is where I am stuck.  I need to know how to loop through the recordset and pull the date together like this.
0
 
CobyRCommented:
Once you join your two tables together you will get back a single recordset.

T1.OrderNumber, T1.InvoiceNumber, T1.CustomerName, T2.SKUNumber, T2.QTY, T2.Price
OD23232323        54454                    ABC Company        4400                 5           6.00
OD23232323        54454                    ABC Company        8800                 12         8.05
OD23232323        54454                    ABC Company        7832                 77        11.35
OD23232324        54455                    XYZ Company        4404                 2         10.00
OD23232324        54455                    XYZ Company        8800                 6          8.05

Create a loop like this:

... Establish connection string and open the connection ...

dim loRS as adodb.recordset
dim lsPOrderNumber         as String

set loRS = createobject("ADODB.Recordset")

set loRS.ActiveConnection = loConn

lsSQL = "SELECT T1.OrderNumber, T1.InvoiceNumber, T1.CustomerName, T2.SKUNumber, T2.Qty, T2.Price FROM T1 inner join T2 on T1.InvoiceNumber = T2.InvoiceNumber"

lors.open lssql

do while not loRS.eof
   ' The If statement below checks to see if your order Number has changed, if so it displays
   ' the order information, otherwise the order info is skipped and just the details are displayed.
   if lors("OrderNumber") <> lsPreviousOrderNumber then
      ' Here is where you would put your Code to display the Order Information
      'Order:
      'OrderNumber   InvoiceNumber   CustomerName
      'OD23232323    54454               ABC Company
      lsPreviousOrderNumber = lors("OrderNumber")
   end if

   'Here is where you would put your code to display the Detail Information from the record

   lors.movenext
loop
0
 
Shiju SasidharanCommented:
method suggested by CobyR is good
just try it out and please let us know the result
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now