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.CreateObject("ADODB.Recordset")
NewsRelSQL = "Select date, company, title, newsreleaseID from
newsreleases;"
do while not RSNewsRelease.EOF
'display news release date, company and title
response.write RSNewsRelease(0).Value & RSNewsRelease(1).Value &
RSNewsRelease(2).Value
'loop through areas
set RSAreas = Server.CreateObject("ADODB.Recordset")
'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 3 3449
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
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.movenext
loop
I won't be back at work until Monday so I will have to wait till then
to try this out.
Thanks,
Jules
Jules (ju****@jrickards.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.movenext 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |