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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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 | |
|
|
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 |