[thelist] asp LOGIC QUESTION

[Ian Anderson]

Brian Delaney wrote:
> I have a sql record set that contains 12 uids. For each UID I want to go 
> the servie table and return the services that are under this catagory's ID
> 
> Once I get the list of services I want to dynamically build the html for 
> display..
> 
> The problem is this: I can loop through the catagory recoredset and find 
> the services per uid I just am stuck on how to create a dynaic unique
> variable name to hold teh html for each service list.

I think retrieving recordsets inside a loop like this is the spawn of 
the devil - I'm pretty sure I got burned badly trying to customise SQL 
queries and execute SELECTs inside a loop this way before.

What I do in these situations is to pull one or both recordsets into 
arrays, and then iterate once through the child array looking for 
matches for each instance in the parent array (or recordset).

For example
aryCategoryList = rsCategoryList.getRows()
aryServiceList = rsServiceList.getRows()

(close the connections and bin the objects)

For i = 0 to Ubound(aryCategoryList, 2)
   For j = 0 to UBound(aryServiceList, 2)
     ...compare the PK ID of the current item in aryCategoryList with 
the FK ID in the current item of aryServiceList and if they match, pull 
the required information into a string for concatenation with previous 
matches...
   Next
Next

The only fiddly bits are remembering where in the array the different 
fields are, and getting the logic right for concatenating the strings 
inside and at the end of each loop.

It's horribly inefficient because you're doing N * M iterations where N 
and M are the number of items in the two arrays, but it executes like 
blazes. VBScript screams along when processing arrays, and even if it 
takes a wee while because of the amount of data, you've closed and 
released the database connection so you're not hogging resources and 
creating a bottleneck.

If the result sets you're working with are of the order of a few tens of 
items times a few hundred items, I don't think it's too impractical. 
Wouldn't want to do it with thousands of rows in either set, though. Had 
a page once doing about 300 * 300, and it took about 5-10 seconds to 
build the page; just about acceptable in the context. That was building 
an enormous set of nested lists for a site map, and I was being quite 
inefficient in assembling the strings. When I had time, I redid it using 
a recursive function to find children of each page, and the processing 
time went down to about 1 second.

Hope this helps

Cheers

Ian

-- 
zStudio - Web development and accessibility
- http://zStudio.co.uk
Snippetz.net - Your personal, private code library
- http://snippetz.net