Nesting queries in ASP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Nesting queries in ASP

Author

Topic

sparky753
Starting Member

7 Posts

Posted - 2007-06-05 : 11:29:22

I've been trying to figure out how to do this but can't seem to. Here's my scenario:

There are 2 related tables:

tblEvents
eventID (PK)
eventDate
eventName

tblEventPresentations
eventPresID (PK)
EventPresentationName
eventPresenter
eventID

One record in tblEvents can have many records in tblEventPresentations

I am trying to write ASP code/SQL queries that would do the following:

Display a record from tblEvents
Then display all corresponding records in tblEventPresentations
Repeat this for every record in tblEvents

For example:

Event1 06-04-07
Presentation 1 - Presenter 1
Presentation 2 - Presenter 2
.
.

Event2 06-06-07
Presentation 1 - Presenter 1
Presentation 2 - Presenter 2
.
.

Right now, this is what my code looks like:

<%
set RS1 = Server.CreateObject("ADODB.RecordSet")
set RS2 = Server.CreateObject("ADODB.RecordSet")
sql1 = "Select * from tblEvents"
RS1 .Open sql1 , oConn

if RS1.EOF then

%>
...
<% else %>

<%
RS1.MoveFirst
do while not RS1.EOF

Response.Write RS1("eventName")
Response.Write RS1("eventDate")
%>

<%
sql2 = "Select * from tblEventPresentations where eventID = " & RS1("eventID")

RS2.Open sql2 , oConn
RS2.MoveFirst
do while not RS2.EOF

Response.Write RS2("EventPresentationName")
Response.Write RS2("EventPresenter")

%>

<%
RS2.MoveNext
loop
%>
<%
RS1.MoveNext
loop
%>

<%end if %>

This works fine for the first recordset but gives an error for the subsequent recordsets. What am i doing wrong? Please help!!!

sparky753
Starting Member

7 Posts

Posted - 2007-06-05 : 13:06:08

Never mind, folks! I figured out how to do it. I have to close the recordset in the inner loop - in this case, RS2.Close

<%
RS2.MoveNext
loop

RS2.Close
%>

That fixed it....Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources