A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

ASP recordset as SQL table



 
 
Thread Tools Display Modes
  #1  
Old March 3rd, 2005, 08:51 AM
Ennex
external usenet poster
 
Posts: n/a
Default ASP recordset as SQL table


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
  #2  
Old March 3rd, 2005, 10:35 AM
Geoffs
external usenet poster
 
Posts: n/a
Default

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??


"Ennex" wrote:


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

  #3  
Old March 3rd, 2005, 11:09 AM
Ennex
external usenet poster
 
Posts: n/a
Default


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

  #4  
Old March 3rd, 2005, 03:25 PM
Chris2
external usenet poster
 
Posts: n/a
Default


"Geoffs" wrote in message
...
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


Geoffs,

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


Sincerely,

Chris O.


  #5  
Old March 3rd, 2005, 03:41 PM
Geoffs
external usenet poster
 
Posts: n/a
Default

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" wrote:


"Geoffs" wrote in message
...
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


Geoffs,

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


Sincerely,

Chris O.



  #6  
Old March 3rd, 2005, 09:55 PM
Brendan Reynolds
external usenet poster
 
Posts: n/a
Default


"Chris2" wrote in message
...
snip
Does an empty ADO recordset really display *both* BOF and EOF at the
same time?


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

--
Brendan Reynolds (MVP)


  #7  
Old March 4th, 2005, 09:53 AM
Geoffs
external usenet poster
 
Posts: n/a
Default

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.

"Brendan Reynolds" wrote:


"Chris2" wrote in message
...
snip
Does an empty ADO recordset really display *both* BOF and EOF at the
same time?


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

--
Brendan Reynolds (MVP)



  #8  
Old March 4th, 2005, 01:44 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


Ennex wrote:
In Access, I
define a number of queries that are then used in other queries just

as
if they were tables.


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/de...atashaping.asp

Jamie.

--

  #9  
Old March 5th, 2005, 07:38 PM
Chris2
external usenet poster
 
Posts: n/a
Default


"Brendan Reynolds" anonymous at discussions dot microsoft dot com
wrote in message ...

"Chris2" wrote in

message
...
snip
Does an empty ADO recordset really display *both* BOF and EOF at

the
same time?


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

--
Brendan Reynolds (MVP)



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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to insert record to Access via SQL from an ASP page Norman Yuan General Discussion 1 February 2nd, 2005 09:23 PM
SQL criteria for deleting one table from another Quan Running & Setting Up Queries 1 January 24th, 2005 11:12 PM
Update - If statement Dan @BCBS Running & Setting Up Queries 13 December 14th, 2004 07:02 PM
Access & OleDb - generating schema changes, problem with identity/counter fields. Thomas Tomiczek [MVP] Database Design 9 November 5th, 2004 11:32 AM
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. HDW Database Design 3 October 16th, 2004 03:42 AM


All times are GMT +1. The time now is 07:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.