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
0
Utf
3/3/2005 7:49:02 AM
access.conversion 3037 articles. 0 followers. Follow

3 Replies
442 Views

Similar Articles

[PageSpeed] 14

You can continue to do the same thing in ASP that you do in Access. Although 
most ASP examples build SQL strings in code, there's nothing to prevent you 
from using saved queries.

The alternative would be to use the Filter method of the ADO recordset.

-- 
Brendan Reynolds (MVP)

"Ennex" <Ennex@discussions.microsoft.com> wrote in message 
news:EBE3622D-9205-4F19-A2DB-BE6B307D10B9@microsoft.com...
>
> 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 


0
Brendan
3/3/2005 4:27:36 PM
"Brendan Reynolds" wrote:
> You can continue to do the same thing in ASP that you do in Access. Although 
> most ASP examples build SQL strings in code, there's nothing to prevent you 
> from using saved queries.

     If you mean the queries saved in Access, that's what I'm doing on my 
development machine, but when I run this on the Web, my server will not be 
running Access, so I need to do all the DB processing in SQL. Basically, what 
I'm asking is, is there a way to make a saved query in SQL outside of Access?


> The alternative would be to use the Filter method of the ADO recordset.

     Filtering does not do what I need because I need to make joins between 
tables and the queries.

MB
0
Utf
3/3/2005 5:51:05 PM
There is no need for the server to be running Access. Queries are processed 
by the Jet database engine. There are some things you can do with queries in 
Access that you can't do outside of Access (such as calling custom VBA 
functions) but in general you can use a query in your ASP code in exactly 
the same way that you would a table.

In order to be able to create new queries and modify existing ones without 
taking down the database, you'll need to be familiar with JET DDL. If you're 
not already familiar with this, start with the Access help topic 'CREATE 
VIEW', and follow the links to related topics from there.

-- 
Brendan Reynolds (MVP)


"Ennex" <Ennex@discussions.microsoft.com> wrote in message 
news:6B19B632-D7D5-4D00-8414-F65B7DA092C7@microsoft.com...
>
> "Brendan Reynolds" wrote:
>> You can continue to do the same thing in ASP that you do in Access. 
>> Although
>> most ASP examples build SQL strings in code, there's nothing to prevent 
>> you
>> from using saved queries.
>
>     If you mean the queries saved in Access, that's what I'm doing on my
> development machine, but when I run this on the Web, my server will not be
> running Access, so I need to do all the DB processing in SQL. Basically, 
> what
> I'm asking is, is there a way to make a saved query in SQL outside of 
> Access?
>
>
>> The alternative would be to use the Filter method of the ADO recordset.
>
>     Filtering does not do what I need because I need to make joins between
> tables and the queries.
>
> MB 


0
Brendan
3/3/2005 8:01:51 PM
Reply:

Similar Artilces:

Need Help with Access Tables
I have created a new table titled "Customer Order" and the fields are "Customer ID" "Product ID" and "Quantity Ordered". When I enter data for a customer, saying that Customer with ID as 1 ordered 2 amounts of product A and then in another field entered that cutomer with ID as 1 ordered 4 amounts of product B, Access wont allow me to do so and says that it will create "duplicate values in the index, primary key, or relationship". What am i doing wrong, and how would i go about entering multiple fields with the same customer ID Hi Joe, G...

Complex coding question re: recordset
If I have an open form and am positioned on a new record with no bound field having been changed (dirty = false) and I call a public function which opens a recordset on the same table that my form is bound to and then do a series of movenexts , can my form become dirty? I'm getting strange results and am wondering if this could be the cause. What I am trying to do is to programatically retrieve a given record by doing an applyfilter. However, what seems to be happening is that somehow my record has become dirty and therefore the form is wanting to do a save before executing t...

Need help with SQL tables
After running a security reconcile sa was no longer a user in GP. I received the answer below about re-adding it, but I don't know how to add a userid in to a table. I can't find anything on knowledgebase about this. We don't have the SQL Enterprise Manager but use the SQL 2005 Management Studio Express. Can anyone help me? I just need to know the script that will add it to these tables. Thanks. Tracey D "Therefore it will be just a matter of adding 'sa' USERID into the following tables in DYNAMICS database as a minimum. Once added, use ano...

Installation Problems SQL 2005
I keep getting the following Error when installling CRM 3.0 Small Business Edition "Service mssearch was not found on computer 'SERVER1'. The specified service does not exist as an installed service" I have the Microsoft Full-Text Engine for SQL Server Service Installed and running. Can anyone help? Are you runndng your domain on a Windows Small Business Server? -- Rgds Michael MCDBA 2000 | MCITP DBA 2005 "jl815" wrote: > I keep getting the following Error when installling CRM 3.0 Small Business > Edition "Service mssearch was not found on com...

Using Float Type Variables for Primary on SQL 2005
We seem to have hit an oddity. We have a stored procedure that gets the next sequence number. It is protected by a transaction so no two processes could update the table of ids at the same time. Every so often, we get duplicates. How could that happen? Is the value based on mantissa? If so, is there a way to expanded it? Keep in mind, I can't change the type from float. Any help is greatly appreciated. Thanks in advance On 2010-07-19 16:19, RG wrote: > We seem to have hit an oddity. > Please don't multi-post. If you have to post to multiple newsgroup...

CPU spikes on sql server
We have noticed most of the same performance issues that are reported around here, but in trying to research a few of these I noticed the following strange behavior. Every 10 minutes a sql agent runs a process for "stored procedure priming". When this runs, the cpu on the server pegs at 100% for about 30 seconds. then back to 0% or so until the next firing This is a almost brand new install and only 1 or 2 users hit it. We have only begun to put data into it, so there isn't very much. Seems odd to me that a frequently scheduled process would hit things that hard that ...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

Table object
There is a little table object that shows up on the top left of a table that you can use to move the table to the left or right. But I can't seem to figure out how to get that table object to appear. You have to click around select the whole table/ deselect the table and then at some point it appears. How do you get it to appear so you can move the table? I am getting tired of trying to randomly get it to appear. Thanks, Tom Which version of Word? We support about 20... On 7/04/10 2:14 PM, in article O4J9ajg1KHA.4832@TK2MSFTNGP04.phx.gbl, "tshad"...

Pivot Table Freezes
I have many pivot tables off a large database and they periodically freeze when I refresh the data. Only solution is to crash out. Any ideas, comments, solutions out there. The machine has been checked out OK. I work on a network - checked out OK. Maybe this is the problem: XL2000: PivotTable Updated Slowly with OLAP Data Source http://support.microsoft.com/default.aspx?scid=237469 Jimbo wrote: > I have many pivot tables off a large database and they periodically freeze > when I refresh the data. Only solution is to crash out. Any ideas, comments, > solutions out there....

Change IP address in Excel Sheet for SQL sever
We have change the IP address of our SQL Sever from 192.168.0.xxx to 192.168.0.4. This results in big problems as all our Excel Sheets with Pivots and SQL Queries in the have stopped working. Cause is that instead of using name we have used IP address in our ODBC set-up so the Excel file has the IP insted of the name of the SQL server. Now, one way to solve this is to save every sheet as XML and then change the adress, then import it and save it as an xls file again - losing graphs etc. Is there anyone that have an alternate solution to this problem and I will be eternaly greatful -...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

Copying fields from combo box to a table
Hi, I am creating a database for our Machine Maintence Report (access 2003). I created a combo box from table1 and I want to have the selected fields (i.e. Machine, Technician, Engineer etc...) in table1 to be copied in table2. This table2 has the same fields Machine, Technician, Engineer plus the other field that will be updated when the maintenance is done such as remarks, issue and data readings. Looking forward for your help. If you really have a need to store this information in multiple tables, you can use code in the After Update event of the combo box to push the valu...

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual Studio 2008 using C#. It receives a SqlString as a parameter and return how many times a specific character was found. I deployed the procedure to a SQL Server database and can see it listed under the database. One caveat is that it has a small lock icon on it. I have a select query where I want to use the stored procedure. I am thinking this is doable in a select query but can't seem to get it to work. What I want to do is to have a select statement listing some fields and one of the fields being th...

Installing Problem SQL SP3a
Hello NG, If got a new W2K3-Server with installed AD and SQL2000 with SP3a. The machine works properly. On installing CRM1.2 the following error occurs: 'The specified SQL-Server must be running Microsoft SQL-Server version 2000 SP3 or later.' Please help me. Thanks, Andreas. ...

Pivot Tables show zero balance
Hi - I have a pivot table.... I want to show customers that have an outstanding balance. when i sort by month, i see everything, totals of who paid and who ha not. I want to sort to see only who is outstanding. How do I do this? CURRENT SETUP: Sort by page = date of invoice (by month) Rows = client name, then the tota -- Message posted from http://www.ExcelForum.com You could add a Status column to your data source, and calculate the account status. For example, with customer names in column C and amounts in column H: =IF(SUMIF($C$2:$C$39,C2,$H$2:$H$39)>0,"Outstanding&...

Web table to Excel...
Could someone help me exporting the tables from the following web-page into excel ? http://www.ekdd.gr/ESDD/IH/REBBBD-esdd-epityxontwn.htm (use unicode encoding) is this impossible ? ...

MFC String-table in C# ?
Hi, maybe a bit OT for this newsgroup, but I know that people here use C#, too. So, does anyone know how the MFC string-table technique map to C#/WinForm? I initially thought about using an integer->string map in C#, but is there some built-in mechanism to manage that? Thanks, Giovanni "Giovanni Dicanio" <giovanniDOTdicanio@REMOVEMEgmail.com> ha scritto nel messaggio news:ugqw1g7LJHA.1736@TK2MSFTNGP03.phx.gbl... > So, does anyone know how the MFC string-table technique map to C#/WinForm? ....after some web search, it seems that C# has a ResourceManager class ...

Copying and Pasting Excel Tables into Word
We do our financials in Word, and copy and paste special as enhanced metafiles the tables that we do in Excel. This works perfectly in Word 2003. In 2007 however, when we past the table and print it, the table looks different (letter spacing funny, not smooth). I have tried printing to five different printers and it still happens. I'd suggest a slightly different approach. If you insist on using enhanced metafiles, try the following: 1. Select the range that you want to copy. 2. Instead of Copy, select the dropdown under Paste>As Picture>Copy as Picture. 3...

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

a field of a record has more than one record in access table
eg. purchase order table:I have one record for each order,but some orders have more than one item ,how to arrange my table so that I should not repeate the order number for each item specially when the order number is primary key. Thank you very much for your help. On Nov 13, 11:01 am, abosamra <abosa...@discussions.microsoft.com> wrote: > eg. purchase order table:I have one record for each order,but some orders > have more than one item ,how to arrange my table so that I should not repeate > the order number for each item specially when the order number is primary key. >...

SQL Command match Items to Matrix
SQL command that will put all items which contain a certain Supplier ID # to be added to a matrix that I have created. We have a company and we want to do a matrix for all of there items. But instead of manually adding each item to the matrix. If I could run an SQL command that will take all items with the supplier ID attached to them and have them added to the matrix. I am aware that I will still have to label the dimensions. It would just be fantastic if I could do a mass add. Hopefully someone has a creative mind. I tried some commands, but I am not an SQL master either. I know that ...

Upsize AutoNumber field to SQL Server
The Upsizing Wizard Add-In in Access 97 would automatically create an Insert Trigger when upsizing to SQL Server. But when I use the built-in Upsizing Wizard in Access XP (2002), it doesn't seem to create those triggers. Is there an option I'm missing somewhere or will I have to manually create those triggers for my upsized DB? TIA! ...

Sizing Data Tables on a chart
When I have a data table attached to a chart and size the chart, the data table does not size proportionately ...

Inserting New rows into IV00108 Table
I am trying to insert new rows of data into the Iv0018 table I have another table in SQL with tthe data but when i use DTS to append this data it will not let me because of a null value being inserted in the DEX_row_Id column. Is there a SQL script that will allow me to apend this data to this table since the part numbers already exisits this is another pricelevel i am adding. This is SQL 2000 and GP 9.0. Thank you Ken DEX_ROW_ID is an identity column so it will auto populate. In DTS do not map a value for DEx_Row_ID (ignore it) "kfoster@pfeinc.com" <kfosterpfeinccom@disc...