Executing Multiple Queries

[XtreamXTC]

I'm quite a newb when it comes to ASP So im hoping there might be a simple answer to what I'm doing wrong!

I've got a set of queries in a database that each feed into the other, i.e. in QRY2 it contains some information from QRY1, and QRY3 contains information from QRY2 etc so its cascading.

I want to know how I can bring back the results of the final query (in this example QRY3). At the moment I've tried the following with no luck:

(the database connection etc is from an inc file which works fine when executing the code against a single stack qry)

<% Dim rslea, sql, Count, sql1, sql2, sql3 set rslea = server.createobject("ADODB.recordset")

sqllea1 = "EXEC QRY1"
sqllea2 = "EXEC QRY2"
sqllea3 = "EXEC QRY3"

rslea.open sqllea3, AdoCon
%>

Any ideas at all would be helpful!

 

[DotNetGnat]

solution 1:using 3 different queres - [red]Not efficient and Not suggested[/red]

qry1= "Select CustomerID, CustomerName from CustomerTable Where CustomerNumber ="&request.form("cusid")
set rs1 = conn.execute(qry1)
'at this point you will have the name and ID of the 
'customer which you want to use it for the QRY2 
qry2 = "Select OrderID, ProductID from Orders WHERE
CustomerID = "&rs1("CustomerID")
set rs2= conn.execute(qry2)
'now you will have OrderID and ProductID which you can use 
'for qry3
qry3="Select ProductName, ProductPrice from Products WHERE
ProductID = "&rs2("ProductID")
set rs3 = conn.execute(qry3)
' at the end you can display all the values from all your record sets 
'clean all your objects and close all of them

solution 2: combine 3 queries and make it a single query using JOINS...something like this, [red]more efficient and suggested way[/red]

Select A.CustomerName, B.OrderID, C.ProductName
from Customers A INNER JOIN Order B
ON A.CustomerID = B.CustomerID
INNER JOIN Products C
ON B.ProductID = C.ProductID

then you would need only one recordset and you can get all the values you want...

-DNG

 

[DotNetGnat]

Oh BTW, the above queries are just sample queries that i provided so that you get an idea of the solution...

if you need help rewriting your query, post some of the data from your tables and the results you are looking...i am sure some one from this forum will surely help...

-DNG

 

[XtreamXTC]

Thanks DNG, I think you've explained what I was after which was rather than having the multi queries stored in the database and trying to execute each of them using ASP, I should use the SQL in ASP to 'Make' the query. At least thats what I think your saying

So is there not a way to execute multi the DB qry's in ASP? I know you can do it with singles just by using "EXEC qryMyQuery" etc.

 

[DotNetGnat]

So is there not a way to execute multi the DB qry's in ASP?

NO.

You need to execute each of your queries to use the result from it in the following queries...

-DNG

 

[Tarwn]

If your using SQL server or something like that (ie, not MS Access), you should be able to execute multiple queries by separating them with single quotes, though I'm not sure if the drivers handle this correctly, as I haven't had a reason to do so myself.
Basically what will happen is that internally it will execute the queries independently, then return multiple recordsets. To do this I believe you would have to use the connection object's .Execute method, capture the output into a recordset object, then use the NextRecordset method of the Recordset object to retrieve your additional recordsets.

My opinion would be to create a new stored procedure that either returned all of the data in one recordset, via JOINs in the query, or at least simply executed all three stored procedures (resulting in a recordset with links to additional recordsets like above).

The only reason to go to a straight SQL statement would be if you didn't have access to create new stored procedures and didn't mind the performance hit that would occur both on the ASP side and the database side.

-T

 

[XtreamXTC]

Thanks Both, I will have a go! (p.s. its in Access )