I'm wondering if someone could help me figure out this query. I have a database with events and clubs (it's for my band's website). The club table has the club name and address information while the event table has the event date and time as well as the club name linked from the club table.

In my calendar page I'm trying to get the club address to show up for each event. But the query below only seems to pull the address information for the first record in the club table while leaving the address blank for the subsequent event records. Therefore, on the calendar page the events that happen to contain the club that is the first record in the club table display the address while the others do not display. I'm guessing that the answer is somewhere in the "SELECT * from clubs" statement but I'm not sure what is needed. Any help would be appreciated. Thanks.

<%
dim RSBODY
dim SQLBODY
Set RSBODY = Server.CreateObject("ADODB.Recordset")
SQLBODY = "SELECT * from SiteSettings"
RSBODY.Open SQLBODY, Conn, 1, 3
%>

<%
Set RSEVENT = Server.CreateObject("ADODB.RecordSet")
RSEVENT.Open "SELECT * FROM Events", Conn, 1, 3
Do while NOT RSEVENT.EOF
rsdate = RSEVENT("Event_Date")
If (Day(rsdate) = dateCounter) AND (Month(rsdate) = CInt(navmonth)) AND (Year(rsdate) = CInt(navyear)) Then%>

<%If RSBODY("Event_Display") = True Then%>
<%=RSEVENT("Category")%>
<%Else%>
<b><%=RSEVENT("Event_Name")%></b><br>
<%=RSEVENT("Description")%>

<%
dim RSCLUB
dim SQLCLUB
Set RSCLUB = Server.CreateObject("ADODB.Recordset")
SQLCLUB = "SELECT * from clubs"
RSCLUB.Open SQLCLUB, Conn, 1, 3
%>

<% If RSEVENT("Event_Name") = RSCLUB("club_name") Then %>
<%=RSCLUB("club_address")%><br>
<%=RSCLUB("club_url")%>


<% End If %>

<% End If
End If
RSEVENT.movenext
Loop
RSEVENT.close
%>

Th problem is that in one connection you can not open two record sets in MSSQL server. In your case you need to open another connection for the second record set.

What?? I think you might be mistaken gyuan. I have on numerous occassions used multiple recordsets from via the same connection at the same time.

The issue he is having primarily is with looping or lack of data.

gbengston, you can create a single query using a join statement to get all the data in one query... something like...

SELECT * FROM Events
left join Clubs on
Events.Club_Name = Clubs.Club_Name

Syntax might be wrong there but the idea is correct...

Alternatively you need to look to look at what you are doing in your loops.

For each event you get a new recordset of clubs (containing all clubs), you check and see if the first club record matches what you want, if it does you use it, if it doesn't you never continue looping to find the one that does....

Another alternative,... you have

SQLCLUB = "SELECT * from clubs"

... perhaps you want

SQLCLUB = "SELECT * from clubs where club_name='" & RSEvents("Club_Name") & "'"

Any of these should solve your problem.

rokslide,

You are right. I made a mistake. I thought that he used the same object to open another record set. Actually, he used the different objects RSEVENT and RSCLUB. Sorry.

Thanks for your help. I found out the problem was in regards to opening up a new record set. Of course, since I am new to writng database queries I had to write it many different ways before it finally worked! :-)

But, it works now so thanks for your suggestions.

-Greg