ASP recordset as SQL table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi experts,

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.

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
 
Hi Ennex,
Well assuming that you are using ADO to create the recordset from your first
sql statement, then all you have to do is apply the "Filter" to get a subset
from you first recordset.
EG: - oRS1.Filter = "[fieldname]=criteria"
'Then test for records returned
If (oRS1.BOF And oRS1.EOF) Then '--NO RECORDS
'Then when finished get back ALL records by setting Filter to ""
oRS1.Filter = ""

Does that help??
 
A filter would work for simple extraction, but the queries I have perform
joins between tables and other queries. I can't do that with a filter.

MB
 
Geoffs said:
Hi Ennex,
Well assuming that you are using ADO to create the recordset from your first
sql statement, then all you have to do is apply the "Filter" to get a subset
from you first recordset.
EG: - oRS1.Filter = "[fieldname]=criteria"
'Then test for records returned
If (oRS1.BOF And oRS1.EOF) Then '--NO RECORDS
Click to expand...

Geoffs,

Does an empty ADO recordset really display *both* BOF and EOF at the
same time?


Sincerely,

Chris O.
 
Hi Chris,
OOPS !!! Sorry, I missed off the Not - it should have read -
If Not (oRS1.BOF And oRS1.EOF) Then
'do some code
Else
'--NO RECORDS
End If

You are quite right, the absolute position of the recordset cann't be in two
places at once. In a new recordset the position would be at the first record
if there was one, so on that basis if it is not at BOF and it is not at EOF
then there must be some records.
Sorry for the typo. :-(


Chris2 said:
Geoffs said:
Hi Ennex,
Well assuming that you are using ADO to create the recordset from your first
sql statement, then all you have to do is apply the "Filter" to get a subset
from you first recordset.
EG: - oRS1.Filter = "[fieldname]=criteria"
'Then test for records returned
If (oRS1.BOF And oRS1.EOF) Then '--NO RECORDS
Click to expand...

Geoffs,

Does an empty ADO recordset really display *both* BOF and EOF at the
same time?


Sincerely,

Chris O.
Click to expand...
 
Does an empty ADO recordset really display *both* BOF and EOF at the
same time?
Click to expand...

Yes.

Public Sub BofAndEof()

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT * FROM Categories WHERE False"
.Open
Debug.Print "BOF: " & .BOF
Debug.Print "EOF: " & .EOF
.Close
End With

End Sub

In the Immediate window ...

bofandeof
BOF: True
EOF: True
 
Ah - said the blind man waving his wooden leg - now I see where we all stand!
Of course, I never thought of it like that Brendan. The absolute position is
still only in ONE place isn't it - 'cos if there are no records in between
then BOF and EOF and the same place. Silly me.
Thanks for the enlightenment Brendan, I have frequently wanted to use the
(BOF AND EOF) test without an else part, but have always used it as above
which, whilst no big deal, has resulted in a couple of superflous lines of
code.
 
Ennex said:
In Access, I
define a number of queries that are then used in other queries just as
if they were tables.
Click to expand...

If you mean an object that is persisted in the database, the standard
term is a VIEW. If you are referring to a 'query within a query' in SQL
code e.g.

SELECT DT1.last_name FROM (
SELECT LastName as last_name
FROM Employees
WHERE HireDate > '1992-12-31'
) AS DT1 ORDER BY DT1.last_name;

then this is known as a derived table. Both concepts port well <g>.

Just to be clear, you cannot query the contents of an ADO recordset as
if it were a table. If filtering is insufficient for your needs,
consider multiple recordsets (remember: they may be disconnected) or a
single hierarchical recordset using the SHAPE syntax. For the latter,
see:

http://msdn.microsoft.com/library/d...0/htm/mdmschierarchicalcursorsdatashaping.asp

Jamie.

--
 
Brendan Reynolds said:
Yes.

Public Sub BofAndEof()

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT * FROM Categories WHERE False"
.Open
Debug.Print "BOF: " & .BOF
Debug.Print "EOF: " & .EOF
.Close
End With

End Sub

In the Immediate window ...

bofandeof
BOF: True
EOF: True
Click to expand...

Brendan Reynolds,

I find that to be very interesting. It's not the way I would have set
up the values of those properties in the Object Model, but oh well . .
..


Sincerely,

Chris O.
 
Back
Top