SHARING THE ANSWER TO A QUESTION I HAD POSTED Retriving data Using 2 s 1

[ifeyinwa]

I had posted a question on Retriving data Using 2 sql statements...
thread333-853837 and
thread333-848660
which no solution was found. have it figured out now and would like to share the solution with the forum especially amorus ,Drexor,alfredomulet& cassidyhunt who had helped out in search of a solution.
below was the piece of code that didi it.
thanks guys will be back as usual

<%
Test = request.querystring("Test")

Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("adodb.Recordset")
Conn.Open "eiwp"
SQLQuery = "Select * from tblopgaCOm2 WHERE Test = " & Test
RS.Open SQLQuery ,Conn,1,1

strBillNo=RS("billNo")

SQLQuery1= "Select area FROM tblopgaCOm2 WHERE billNo = '"&strBillNo&"'"
Set RS1=Conn.Execute(SQLQuery1)
%>

 

[CassidyHunt]

Glad to see you got the answer and thanks for posting the solution.

Cassidy

 

[Mr3Putt]

Or, you can use a Stored Procedure that builds multiple recordsets, one after the other... then use the "NextRecordSet" command.

This way you only instantiate a single Connection object, and a single recordset OBJECT. Then you can page through the SQL result-sets, then through the records in each result-set, then move to the next set.

I haven't used that method in quite a while, so I'll leave it to another more qualified coder to expound.

4GuysFromRolla have a very niced article about this at:

http://www.4guysfromrolla.com/webtech/083101-1.shtml

 

[Chopstik]

Thanks, Mr3Putt. whenever I've come across this problem I've typically used ifeyinwa's example... This might be a more elegant solution. Will have to play with it later...

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou