Unanswered: Help with Query based on field relationships
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.
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%>
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") & "'"
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! :-)