Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    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.

    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%>

    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 %>

    <% End If %>

    <% End If
    End If

  2. #2
    Join Date
    Dec 2003
    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.

  3. #3
    Join Date
    Nov 2003
    Christchurch, New Zealand
    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.

  4. #4
    Join Date
    Dec 2003

    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.

  5. #5
    Join Date
    Feb 2004
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts