Classic ASP Discussion - Multiple queries on same page

Print Page | Close Window

Multiple queries on same page

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=3699
Printed Date: 29 March 2026 at 11:56pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Multiple queries on same page

Posted By: jrockfl
Subject: Multiple queries on same page
Date Posted: 20 June 2003 at 10:46am

What would be the correct way to have more then one query on a page. Here is the code I am using for just one query. I would like to add more, I just want to make sure I am doing things correctly.

<%
'Dimension Variables
Dim adoCon 'Holds the database connection object
Dim rsService 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Create an active connection to the connection object
adoCon.Open "DSN=rockenbach"

'Create an ADO recordset object
Set rsService = Server.CreateObject("ADODB.Recordset")

'Initialize the strSQL variable with an SQL query
strSQL = "SELECT servicesid, service FROM r_services ORDER BY sort asc"

'Open the recordset with SQL query
rsService.Open strSQL, adoCon
%>

Replies:

Posted By: MorningZ
Date Posted: 20 June 2003 at 10:53am

either use, then close that recordset.. and reuse it...

or create another recordset with the new query

-------------
Contribute to the working anarchy we fondly call the Internet

Posted By: jrockfl
Date Posted: 20 June 2003 at 11:42am

Something like this? I created another recordset and query

<%
'Dimension Variables
Dim adoCon 'Holds the database connection object
Dim rsService 'Holds the recordset for the records in the database
Dim rsPortfolio 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query
Dim strSQL2 'Holds the SQL query

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Create an active connection to the connection object
adoCon.Open "DSN=rockenbach"

'Create an ADO recordset object
Set rsService = Server.CreateObject("ADODB.Recordset")
Set rsPortfolio = Server.CreateObject("ADODB.Recordset")

'Initialize the strSQL variable with an SQL query
strSQL = "SELECT servicesid, service FROM r_services ORDER BY sort asc"
strSQL = "SELECT * FROM portfolio"

'Open the recordset with SQL query
rsService.Open rsPortfolio.Open strSQL,strSQL2, adoCon
%>

Posted By: ljamal
Date Posted: 20 June 2003 at 2:51pm

Is the DSN pointing to a Access DB or SQLServer?

With SQL you can return multiple recordsets with a single query. So you could do

strSQL = "select servicesID, service from r_services order by sort asc; select * from portfolio"
set rsSQL = sqlConn.Execute(strSQL)

where sqlConn is your open SQL Server Conn and rsSQL is a recordset. By default the first recordset is open (select servicesID, service from r_services order by sort asc) and to move to the next recordset you would use:
Set rsSQL = rsSQL.NextRecordSet()
and then you could access the other recordset (select servicesID, service from r_services order by sort asc)

If you are using SQL Server, my suggestion to learn and use the above method as it reduces your SQL server calls and thus the rendering time for the page.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: jrockfl
Date Posted: 20 June 2003 at 9:15pm

I understand what you are saying with SQL Server. I am using Access though. What would be good practice for Access with mulitple queries?

Posted By: ljamal
Date Posted: 20 June 2003 at 9:20pm

Access does not allow the return of multiple recordsets with a single query. My suggestion is always to open and close database connections ASAP. Usually, I make all my database calls at the top of an ASAP page, store to a detached recordset or array and then use the array or recordset when need in the page. I lean towards array because they make moving through (forwards and backwards) recordsets easier.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming