473,714 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Looping database queries

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
Jul 20 '05 #1
3 3464
Jules (ju************ *@ndm.gov.on.ca ) writes:
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:


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog <so****@algonet .se> wrote in message news:
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


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
Jul 20 '05 #3
Jules (ju****@jrickar ds.ca) writes:
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
Yes, this is what you would receive.
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


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2337
by: Mortrek | last post by:
Hi. We have a .NET programmer using our SQL server. The database is roughly 8GB at this point, full of all of our inventory, payroll data, etc. for running reports. This is on a dual-xeon 3.2Ghz, 4GB DDR, 3x15k RPM SCSI RAID-5, gigabit ethernet, etc server. Yet, every once in a while it slows to an absolute crawl. IIS6 is the front-end, and its limited to a web garden with 4 threads. However, sometimes only 1 processor does any real...
5
1440
by: Karen | last post by:
Most my experience has been with MySQL, so I write queries in terms of SQL statements. I am using access right now as it is on the machine I have. I am using the SQL window as I couldn't figure out how to get it to do what I wanted using the design view or wizard. My problem is I need to take the average of a log of a number (variable name =Ne). In MySQL the command was: select avg(Pow(10,Ne)) from TABLE where..... Access doesn't like...
5
1802
by: Bruce Lawrence | last post by:
I'm running Access 97 and my modules are looping if someone puts an invalid value in. The setup: 3 macros - get_clock_num, verify_clocknum, append_to_history 3 functions. each in their own module - get_clocks(), verify_clocks(), append() Each macro runs a function from in its corresponding module. Here is my code.
20
3056
by: Stewart Graefner | last post by:
Here is a chunk of code that works for an individual record. It evaluates dates and checks or unchecks boxes as it goes along. It may not be pretty but it works. What my problem is that I need it to evaluate all the records(200+) in my db and change those which need changing. Having to do it individually would defeat the purpose of developing this code. What I would like to be able to do is either 1. Open the db push a button and all the...
2
1581
by: Delores | last post by:
I have been running make-table queries to create 10 tables (one for each unit) that are exported to Excel spreadsheets. Because of my limited knowledge with code, I've created 10 separate queries to do the job and then run a macro to do the exporting. I know there must be a way to generate the 10 tables with only one query and I think it probably involves looping(?) through the query. I've read the previous postings and anytime I try to...
13
5022
by: JayCallas | last post by:
I know this question has been asked. And the usual answer is don't use cursors or any other looping method. Instead, try to find a solution that uses set-based queries. But this brings up several questions / senarios: * I created several stored procedures that take parameters and inserts the data into the appropriate tables. This was done for easy access/use from client side apps (i.e. web-based).
6
1820
by: Luke - eat.lemons | last post by:
Hi, Im pretty new to asp so all light on this question would be great. Basically i need to test to see what value is set (where to retrieve the data from) so ive done it like this: If Request.Querystring("id") = "" then TidF=Request.Form("TidF") Else
1
1453
by: RSH | last post by:
Hi, I have a situation in where I have a main loop that queries a "Parent" Global database table. Based on that resultset I loop through all of the databases and the appropriate table in the "children" databases. Because we have roughly 5000 "Children" databases this process is taking quite some time. I was wondering if there is some way to incorporate multithreading to speed
3
2468
by: =?Utf-8?B?R3JlZyBTdGV2ZW5z?= | last post by:
I am connecting to an Oracle database using an OleDbConnection. I am using DataReader objects to get query results. However, this limits me to only having one reader open at a time, which is a problem for one of the operations I am doing. I have a table with hierarhical data (only 3 levels deep) that I would like to parse through in a depth-first search. Each row in the table in the database has a Name and a ParentName, and I'm doing...
0
8817
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9322
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9086
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7963
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5964
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4734
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3170
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2531
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.