[thelist] ASP: Sub Recordsets?

Casey Crookston casey at thecrookstons.com
Mon Jul 14 11:12:23 CDT 2003


I'll try to explain my question in a way that makes sense....

I am building a site for a commercial construction company.  The site has a
database driven portfolio section.  In short, the tables looks like this:

Table jobs:

+---------+------------------+--------------+-------------+-----------------
-+
 |  job_id   |  job_title              |  category_id   |  foreman_id  |
job_description   |
+---------+------------------+--------------+-------------+-----------------
-+
 |     1        | Caribou Coffee   |        2             |        11
|       text                |
+---------+------------------+--------------+-------------+-----------------
-+

Table job_cats:

+---------+------------------+
 |  cat_id   |  cat                      |
+---------+------------------+
 |     1        | Mall Stores         |
+---------+------------------+
 |     2        | Resturants           |
+---------+------------------+

Table employees:

+---------+-------------+------------------+
 | emp_id   |  first_name   |     last_name        |
+---------+-------------+------------------+
 |     11       | Bob            |   The Builder        |
+---------+--------- ---+------------------+

(Can you tell I have young boys?)

I am also building a back-end management system so the client can edd, edit,
and delete portfolio items.  I have build the front end and it works just
dandy.  I am now building the back end, and I've run up against the EOF of
my own abilities.  Here's the trouble:

I can populate a single recordset and loop through it, placing each value in
an HTML form which the user can then edit and execute to the db.  No trouble
there.  BUT, when I get to Category and Forman, I want there to be a drop
down <select> list for all of the categories and all of the foremen.

How in the name-of-the-sql gods does one build such a select statement?  Or,
is it better to have three recordset objects each with their own select...
one containing all jobs, one containing all categories, and one containing
all formen.  The Jobs set loops once, and within each job loop the other two
both loop one time each, are rendered in the drop down list, and then bumped
back to BOF to get ready for the next loop.  Make sense?

I'm sure someone has come up against something like this before.  What's the
best way to deal with it?

Many TIA's

Casey



More information about the thelist mailing list