Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 16th, 2004, 03:04 PM
Registered User
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Best method - Nested data?

I have tried to search for this, but haven't been able to find anything that exactly fits my situation.

I have data from several tables that I need to display. The information is nested, so I have it working using nested loops, but I don't think nesting the DB connections is the best way to do this.

Here is a quick example of the data I have.

Soldier Table:
ID, Name, RankID, PlatoonID

Rank Table:
ID, RankName, RankImage, RankOrderVal

Platoon Table:
ID, PlatoonName, PlatoonDesc, PlatoonOrderVal

Medal Table:
ID, MedalName, MedalIMG, MedalDesc, MedalOrderVal

Award Table:
ID, AwardName, AwardIMG, AwardDesc, AwardOrderVal

ID, UserID, MedalID, Comment

ID, UserID, AwardID, Comment

Here is what I have so far.

DB Connect 1
Select1 "Platoon"
    Loop 1
     Output PlatooNames
     DB Connect 2
     Select 2 "Rank"
     Output Ranks
     DB Connect 3
     Select 3 "Soldiers"
         Loop 3
             Output Soldiers
             DB Connect 4
             Select 4 "Medals"
         Loop 4
                 Output medals
             End Loop 4
             Close DB 4
             DB Connect 5
             Select 5 "Awards"
             Loop 5
                 Output Awards
             End Loop 5
             Close DB 5
         End Loop3
         Close DB 3
     End Loop 2
    Close DB 2
End Loop 1
Close DB 1

Here is a sample output:

Platoon 1
     Soldier 3 - Medals 1, 2, 3
     Soldier 1 - Medals 2, 4 - Awards 4
     Soldier 5 - Medals 5, 6 - Awards 2
     Soldier 4 - Medals None

Platoon 2
     Soldier 7 - Medals 3
     Soldier 2 - Medals 1 - Awards 2
Staff Sgt
     Soldier 6 - Medals 7

As you can see, some soldiers can have multiple medals, or none.

Sorry for all the info, but I wanted to be clear about what I was doing.

So, is there a better way to do this?

Reply With Quote
  #2 (permalink)  
Old February 17th, 2004, 08:40 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts

Making multiple selects like you are doing is never the best way. If you have to make calls over and over to the database then you are going to see a real big hit in performance. The way I usually do something like this is to use a bunch of joins in the data to change relational data (best way to store it in the DB) to a "flat table". Then in your asp code you determine when the data changes within each level. You have to store the "last item" for each level you are working with. So you have a single loop that goes thru each row returned and you have several If structures that check for a new occurance of each level.

For Each row
    If row("platoon") <> sLastPlatoon Then
        'output platoon text
    End If
    If row("rank") <> sLastRank Then
        'output rank text
    End If

Although this is a little bit more clumsy, it's a whole lot more efficient than make repeated calls to the database. The storage you have for the data is quite correct, you just need to select it for processing in a different way that is more efficient for the data consumer (ASP). Post back if you need a hand constructing a query to give you a "flat file" structure.

Work smarter, not harder.
Reply With Quote
  #3 (permalink)  
Old February 17th, 2004, 08:46 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts

Alternatively, you could use Data Shaping. It's a bit scary technology, with an odd syntax, but it comes in handy every now and then.





Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #4 (permalink)  
Old February 17th, 2004, 04:18 PM
Registered User
Join Date: Feb 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

  Thanks for your help.. I don't think I am ready for data shaping yet.

I'll give the flat table method a try.


Reply With Quote
  #5 (permalink)  
Old February 20th, 2004, 12:46 PM
Friend of Wrox
Join Date: Sep 2003
Location: Minneapolis, MN, USA.
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post

Try joining your tables within a select statement and as you loop (once) you can get these results by ordering your output properly and passing a variable for each group which will allow you to supress the duplicates. It's pretty simple and will give you exactly what you are looking for. If the info you provided allows me to, I will come up with a solution when I get some time today.

By the way, where you say Medals 7, does 7 represent a count of how many medals this soldier has or is it a certain medal named 7? If it is a name and you have Medals 1, 2, 3, then it is going to be more difficult trying to avoid any nested recordsets. In this case I would nest a recordset for Medals and Awards.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with binding XMl data into nested repeater vishnu108mishra XML 0 November 19th, 2007 03:53 AM
What is the best method to append data? rdmapes PHP How-To 0 December 27th, 2006 01:06 PM
four Nested gridviews with data KamalRaturi ASP.NET 2.0 Professional 0 November 15th, 2006 10:17 PM
nested muenchian method. Anyone? Kabe XSLT 1 March 26th, 2005 03:24 AM
Method or Data member not found Anup Gavate VB How-To 2 March 25th, 2005 04:50 PM

All times are GMT -4. The time now is 02:52 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.