Using ASP recordset as SQL table

I'm porting a database developed in Access to a Web site. In Access, I
define a number of queries that are then used in other queries just as if
they were tables. How do I do the same thing in ASP?

I am thinking that what I want to do is something like the following. I
would open a recordset that holds the results of a query:

oRS1.Open('SELECT fields FROM table WHERE criterion', ...)

and then I want to open another recordset that performs a SQL operation on
the first recordset

oRS2.Open('SELECT fields FROM oRS1 WHERE criterion', ...)

But I don't know how to do that. How do I reference an established recordset
in a new SQL statement? I can't just put the name of the recordset there
because the table name that goes into a SQL statement is a string, and the
recordset is an object, not a string.

It doesn't make sense to use a subquery because the first query is quite
complex and is referenced multiple times in the second query, so having the
DB engine recalculate the first query over and over again as a subquery is
going to be horrendously inefficient. It seems there must be a way to
reference a stored recordset for this purpose.

Am I making sense? I'm sure there must be a way to do this, but I can't find
any documentaiton of such a procedure.

Thanks for your help.

Regards,
Marshall Burns
www.Ennex.com

Re: Using ASP recordset as SQL table

Ennex wrote:
>
> Am I making sense?
Of course you are. You made sense the first time you asked the question over
in .scripting.jscript

> I'm sure there must be a way to do this,

Why are you sure there must be a way? Wanting it does not make it so.

> but I
> can't find any documentaiton of such a procedure.

That's because there isn't a way to do it. Data must exist IN THE DATABASE
in order for it to be used as a data source in a query. Data that has been
filtered and sent as a resultset to the client does not exist in the
database in the filtered state.
Having said that, there is a workaround:

Insert the records resulting from your original query into a work table
(temporary or otherwise, depending on what database you are using). Then
select the data from that table if you need the data sent to the client for
some reason. Subsequent queries based on the original filter criteria can
use the work table for their data source.

Also, you can use the Filter property of your original recordset if you
simply need to produce a subset of the records in your original recordset
(ie, if you don't have a requirement to get additional records/fields from
the database)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Using ASP recordset as SQL table

Bob,

You asked me to post to both groups together, but I don't see how to do
that. I'm posting from MSDN.Microsoft.com/newsgroups, and when I click on
"New" or "Reply", it only allows for one discussion group. Maybe there's a
better news client I should be using.


> Having said that, there is a workaround:
> Insert the records resulting from your original query into a work table

By "work table," do you mean what is produced by CREATE VIEW, as
suggested by Dave Methvin over in .scripting.jscript? If so, I'm not sure why
you call that a workaround, because it does exactly what I wanted, which is
to allow me to reference the results of one SELECT in another SELECT.

If you meant something else by "work table," could you please refer me
to the doc on the procedures?


> Also, you can use the Filter property of your original recordset if you
> simply need to produce a subset of the records in your original recordset

No, I need to do JOINs between tables and the results of the query.

Thanks very much for your help.

MB

Re: Using ASP recordset as SQL table

> You asked me to post to both groups together, but I don't see how to
do
> that. I'm posting from MSDN.Microsoft.com/newsgroups, and when I click on
> "New" or "Reply", it only allows for one discussion group. Maybe there's a
> better news client I should be using.

Yes, maybe a newsreader. There's a reason a browser doesn't support NNTP
natively, and they have to convert it to HTML so you can use IE. Blecch.

http://www.aspfaq.com/5007

> By "work table," do you mean what is produced by CREATE VIEW

Not necessarily. That might work, but you won't be able to pass parameters
to it.

Typically, a work table is a new table that is created solely for the
specific query. In SQL Server, for example, you can do this:

SELECT INTO workTable FROM BigTable [WHERE...]

SELECT FROM workTable...

DROP TABLE workTable

Often a #temp table will do this too, it depends on whether you want to use
TEMPDB or the local database.

However, there are limited cases where I think it would be necessary to
break this up into two queries. Usually with the right join techniques and
proper indexing, it won't be necessary. Perhaps if you tell us what you're
really trying to do (I mean the actual business requirement, not your
subject line which is a means, not an end), we will have better advice.

Re: Using ASP recordset as SQL table

Ennex wrote:
> Bob,
>
> You asked me to post to both groups together, but I don't see
> how to do that. I'm posting from MSDN.Microsoft.com/newsgroups, and
> when I click on "New" or "Reply", it only allows for one discussion
> group.
Well, I've never used it, but I went there and clicked on "newsgroup
resources" which brought me here:
http://msdn.microsoft.com/newsgroups/nodepages/tips.aspx
where I found this:
Advanced options
To post in more than one discussion group, click Advanced Options, and then
type the additional discussion groups you want to post to in the Cross-post
to these discussion groups box.



> Maybe there's a better news client I should be using.

No argument there :-)
More below.


>
>> Having said that, there is a workaround:
>> Insert the records resulting from your original query into a work
>> table
>
> By "work table," do you mean what is produced by CREATE VIEW, as

No. CREATE VIEW is the same as creating a saved query with no parameters in
Access. I'm assuming you are still using Access. Is that the case?

>
> If you meant something else by "work table," could you please
> refer me to the doc on the procedures?

I meant creating an actual physical table in your database to hold the
results of your initial query.

>
>
>> Also, you can use the Filter property of your original recordset if
>> you simply need to produce a subset of the records in your original
>> recordset
>
> No, I need to do JOINs between tables and the results of the
> query.
>

Then you need an actual physical table or a saved query (View).

Did you realize you could parameterize a saved query? That could be the way
for you to go ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.