Looping database queries

[Jules]

Hi, newbie here:

I have created a small (5 fields) Access table which I will be
converting to MS-SQL Server format when the ASP code has been
completed. It is basically a collection of links to news releases from
mining companies. The group of people who will be browsing this
database want to know if the news release pertains to their area.
Sometimes the news release pertains to multiple areas if the mining
properties are scattered. Given the possibility of a one-to-many
relationship, ie one news release, many areas, I created an additional
table for the areas. I created the ASP code to pull down the news
release information, then loop through the area records such as:

set RSNewsRelease = Server.CreateOb ject("ADODB.Rec ordset")
NewsRelSQL = "Select date, company, title, newsreleaseID from
newsreleases;"

do while not RSNewsRelease.E OF
'display news release date, company and title
response.write RSNewsRelease(0 ).Value & RSNewsRelease(1 ).Value &
RSNewsRelease(2 ).Value

'loop through areas
set RSAreas = Server.CreateOb ject("ADODB.Rec ordset")
'run query
do while not RSAreas.EOF
'display areas
Loop
set RSAreas = nothing
Loop

In other words, the only way I could get the results I wanted was to
set the Recordset to nothing, then reset it with each iteration of the
outer loop.

Is there a better way to do this?

Jules

[Erland Sommarskog]

Re: Looping database queries

Jules (julian.rickard s@ndm.gov.on.ca ) writes:[color=blue]
> I have created a small (5 fields) Access table which I will be
> converting to MS-SQL Server format when the ASP code has been
> completed. It is basically a collection of links to news releases from
> mining companies. The group of people who will be browsing this
> database want to know if the news release pertains to their area.
> Sometimes the news release pertains to multiple areas if the mining
> properties are scattered. Given the possibility of a one-to-many
> relationship, ie one news release, many areas, I created an additional
> table for the areas. I created the ASP code to pull down the news
> release information, then loop through the area records such as:[/color]

It would probably be more effecient to bring up all information in
in one query:


SELECT nr.date, nr.company, nr.title, a.area
FROM newsreleases nr
JOIN areas a ON nr.newslreaseid = a.newsrleaseid
ORDER BY nr.date, nr.company, nr.title


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jules]

Re: Looping database queries

Erland Sommarskog <sommar@algonet .se> wrote in message news:
[color=blue]
> It would probably be more effecient to bring up all information in
> in one query:
>
>
> SELECT nr.date, nr.company, nr.title, a.area
> FROM newsreleases nr
> JOIN areas a ON nr.newslreaseid = a.newsrleaseid
> ORDER BY nr.date, nr.company, nr.title[/color]

I basically understand your code - nr and a are aliases. The only
problem I have with your example is that, as I understand it, if a
newsrelease pertains to 3 areas, then this SQL code will result in
three "entries" in the recordset array such as (simplified):

June IBM New President Toronto
June IBM New President Cleveland
June IBM New President New York

If this is correct, I then have to find a way to cycle through the
identical recordsets (identical except for the area field). OK, just a
sec, I could add the newsrelease id to the SELECT statement and then
do a:

do while "id is the same"
response.write location
recordset.moven ext
loop

I won't be back at work until Monday so I will have to wait till then
to try this out.

Thanks,

Jules

[Erland Sommarskog]

Re: Looping database queries

Jules (julian@jrickar ds.ca) writes:[color=blue]
> I basically understand your code - nr and a are aliases. The only
> problem I have with your example is that, as I understand it, if a
> newsrelease pertains to 3 areas, then this SQL code will result in
> three "entries" in the recordset array such as (simplified):
>
> June IBM New President Toronto
> June IBM New President Cleveland
> June IBM New President New York[/color]

Yes, this is what you would receive.
[color=blue]
> If this is correct, I then have to find a way to cycle through the
> identical recordsets (identical except for the area field). OK, just a
> sec, I could add the newsrelease id to the SELECT statement and then
> do a:
>
> do while "id is the same"
> response.write location
> recordset.moven ext
> loop[/color]

Yes, doing that sort of logic is not very complicated.

There is something called the Shape Provider in ADO, so that you can
bring up two related recordsets in one query. ADO is not my home ground,
and I've only read about shape, so I'm not providing any example.
And for many purposes a non-normalized recordset like this one is
the simplest way to go.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp