I have an Active Server Page, which displays Bookings of the current Day in a HTML- Table. I display informations like Roomname, Organizer, From, To and Participants.My Problem is that if i try to get the Participants from a booking, the Participants for a specific booking is displayed in multiple booking even if it does not belong to that booking. To illustrate my Problem
ailyPlanAs you can see the Name of the Participants "Kashif Butt" or "adad" is displayed multiple times. But they only belongs to the Booking with the name "PASS"What could be the Problem? I searched for hours but did not found the Problem. Hope you can help me.My Code so far: sConnString = "Driver={SQL Server}; Server=localhost; Database=fifa;"sSQL="select distinct THEMA, ID, AGENDA, VON, BIS, PERSONENZAHL, THEMA_ENABLED from VIEW_RAUMBUCHUNG_DISPO " & _ "where von >= convert(date, getdate(), 4) " & _ " and von < convert(date, dateadd(day,1, GETDATE()), 4) " & _ " and BIS >= getdate() " & _ " and STORNO is null " & _ " order by von, bis"Connection.Open sConnStringRecordset.open sSQL, sConnString,1,1%><html><head><meta http-equiv="content-type" content="text/html;charset=utf-8"/><meta http-equiv="REFRESH" content="10"/> <style type="text/css" media="all"> body{font-family: Arial;} h4{font-size: 10pt;font-weight: bold;white-space: nowrap;margin-top: 0; margin-bottom: 10px;} th{font-size: 9pt;font-weight: normal;text-align: center;white-space: nowrap;} td{font-size: 9pt;}.content td{border: solid 1px #dadada;}.content th{border: solid 1px #dadada;background-image: url("tbl_header_row_bg.gif"); background-repeat: repeat-x; white-space: nowrap;} </style> </head> <form name=form1><p align="left" style="margin-top:20px;margin-bottom:20px;font-family:'Arial';font-size:20pt; color:#000000"> Daily Plan </p><p align="left" style="margin-top:10px;margin-bottom:20px;font-family:'Arial';font-size:10pt; color: font-weight:bold;color:#000000"><% =formatDateTime(CurrentDate, 1)%></p></form><table width="100%" class="content" cellpadding="3" cellspacing="0" border="0" style="border-collapse: collapse;"> <tr> <th width="350" align="left">Event</th> <th width="100" align="absmiddle">Room</th> <th width="60" align="absmiddle">From</th> <th width="60" align="absmiddle">To</th> <th align="left">Equipment</th> <th align="left">Catering</th> <th align="left">Agenda</th> <th align="left">Participants</th> <th align="absmiddle">Persons</th> </tr><%Do Until Recordset.Eof rsRaum.open "select raum_id, KOPPELBESTUHLUNG_ID from RESERVIERUNGRAUM where buchung_id = " & Recordset("ID"), Connection raum_id = rsRaum("raum_id") KOPPELBESTUHLUNG_ID = rsRaum("KOPPELBESTUHLUNG_ID") rsRaum.close ' falls Kopplung, hole ID des "Parent"-Raumes if not isNull( KOPPELBESTUHLUNG_ID ) then rsRaum.open "select parent_id from KOPPELN where CHILD_ID = " & raum_id, Connection if not rsRaum.EOF then raum_id = rsRaum("parent_id") end if rsRaum.close end if ' hole Raum Details rsRaum.open "select bezeichnung from Raum where ID = " & raum_id, Connection raumname = rsRaum("bezeichnung") rsRaum.close'GET THE PARTICIPANTS------------------------------------------------------ rsRaum.open "SELECT DISTINCT NAME, FIRMA FROM PARTICIPANT WHERE buchung_id = " & Recordset("ID") & " and STATUS = 2 and DAILYPLAN = 1" , Connection if not rsRaum.EOF then dim new_list new_list = "" do while not rsRaum.eof new_list = new_list & rsRaum("NAME") & " " & "(" & rsRaum("FIRMA") & ")" & "," rsRaum.movenext loop new_list = left(new_list, len(new_list)-1) end if rsRaum.closeif ucase( Recordset("thema_enabled") ) = "Y" or isnull(Recordset("thema_enabled")) then thema = Recordset("THEMA") else thema = "" end if%> <tr "margin-bottom:100px" height="30" valign="top"> <td style="overflow:hidden;"><% =thema %></td> <td align="center"; ><% =raumname %><br></td> <td align="center"; ><% =FormatDateTime( Recordset("von"), 4)%></td> <td align="center"; ><% =FormatDateTime( Recordset("bis"), 4) %></td> <td align="center"; ><br></td> <td align="center"; ><% =new_list3 %><br></td> <td align="center"; ><% =Recordset("agenda") %></td> <td ><% =new_list%><br></td> <td align="center"; ><% =Recordset("personenzahl") %><br></td> </tr><%Recordset.MoveNextLoop
ailyPlanAs you can see the Name of the Participants "Kashif Butt" or "adad" is displayed multiple times. But they only belongs to the Booking with the name "PASS"What could be the Problem? I searched for hours but did not found the Problem. Hope you can help me.My Code so far: sConnString = "Driver={SQL Server}; Server=localhost; Database=fifa;"sSQL="select distinct THEMA, ID, AGENDA, VON, BIS, PERSONENZAHL, THEMA_ENABLED from VIEW_RAUMBUCHUNG_DISPO " & _ "where von >= convert(date, getdate(), 4) " & _ " and von < convert(date, dateadd(day,1, GETDATE()), 4) " & _ " and BIS >= getdate() " & _ " and STORNO is null " & _ " order by von, bis"Connection.Open sConnStringRecordset.open sSQL, sConnString,1,1%><html><head><meta http-equiv="content-type" content="text/html;charset=utf-8"/><meta http-equiv="REFRESH" content="10"/> <style type="text/css" media="all"> body{font-family: Arial;} h4{font-size: 10pt;font-weight: bold;white-space: nowrap;margin-top: 0; margin-bottom: 10px;} th{font-size: 9pt;font-weight: normal;text-align: center;white-space: nowrap;} td{font-size: 9pt;}.content td{border: solid 1px #dadada;}.content th{border: solid 1px #dadada;background-image: url("tbl_header_row_bg.gif"); background-repeat: repeat-x; white-space: nowrap;} </style> </head> <form name=form1><p align="left" style="margin-top:20px;margin-bottom:20px;font-family:'Arial';font-size:20pt; color:#000000"> Daily Plan </p><p align="left" style="margin-top:10px;margin-bottom:20px;font-family:'Arial';font-size:10pt; color: font-weight:bold;color:#000000"><% =formatDateTime(CurrentDate, 1)%></p></form><table width="100%" class="content" cellpadding="3" cellspacing="0" border="0" style="border-collapse: collapse;"> <tr> <th width="350" align="left">Event</th> <th width="100" align="absmiddle">Room</th> <th width="60" align="absmiddle">From</th> <th width="60" align="absmiddle">To</th> <th align="left">Equipment</th> <th align="left">Catering</th> <th align="left">Agenda</th> <th align="left">Participants</th> <th align="absmiddle">Persons</th> </tr><%Do Until Recordset.Eof rsRaum.open "select raum_id, KOPPELBESTUHLUNG_ID from RESERVIERUNGRAUM where buchung_id = " & Recordset("ID"), Connection raum_id = rsRaum("raum_id") KOPPELBESTUHLUNG_ID = rsRaum("KOPPELBESTUHLUNG_ID") rsRaum.close ' falls Kopplung, hole ID des "Parent"-Raumes if not isNull( KOPPELBESTUHLUNG_ID ) then rsRaum.open "select parent_id from KOPPELN where CHILD_ID = " & raum_id, Connection if not rsRaum.EOF then raum_id = rsRaum("parent_id") end if rsRaum.close end if ' hole Raum Details rsRaum.open "select bezeichnung from Raum where ID = " & raum_id, Connection raumname = rsRaum("bezeichnung") rsRaum.close'GET THE PARTICIPANTS------------------------------------------------------ rsRaum.open "SELECT DISTINCT NAME, FIRMA FROM PARTICIPANT WHERE buchung_id = " & Recordset("ID") & " and STATUS = 2 and DAILYPLAN = 1" , Connection if not rsRaum.EOF then dim new_list new_list = "" do while not rsRaum.eof new_list = new_list & rsRaum("NAME") & " " & "(" & rsRaum("FIRMA") & ")" & "," rsRaum.movenext loop new_list = left(new_list, len(new_list)-1) end if rsRaum.closeif ucase( Recordset("thema_enabled") ) = "Y" or isnull(Recordset("thema_enabled")) then thema = Recordset("THEMA") else thema = "" end if%> <tr "margin-bottom:100px" height="30" valign="top"> <td style="overflow:hidden;"><% =thema %></td> <td align="center"; ><% =raumname %><br></td> <td align="center"; ><% =FormatDateTime( Recordset("von"), 4)%></td> <td align="center"; ><% =FormatDateTime( Recordset("bis"), 4) %></td> <td align="center"; ><br></td> <td align="center"; ><% =new_list3 %><br></td> <td align="center"; ><% =Recordset("agenda") %></td> <td ><% =new_list%><br></td> <td align="center"; ><% =Recordset("personenzahl") %><br></td> </tr><%Recordset.MoveNextLoop