CODEWITHSUNDEEP
asp-classicvbscript
Paks
Paks

Reputation: 1470

Same data is displayed multiple times. Why?

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:

DailyPlan

As 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:

<%
Dim connection, recordset, sSQL, sConnString, next10, prev10, P
Dim thema, rsRaum, raum_id, KOPPELBESTUHLUNG_ID, raumname, pageCount
Dim TeilnehmerNameExtern, TeilnehmerFirmaExtern

Dim CurrentDate
CurrentDate = Now()

Dim intHour
Dim intMinute
Dim intSecond

intHour = 17
intMinute = 0
intSecond = 0

Dim Time 

Time = TimeSerial(intHour, intMinute, intSecond)

Dim Timeda
Timeda = Date()
Dim ts2
ts2 = Timeda + Time




Dim DayOfWeek 

DayOfWeek = weekday(CurrentDate)


If CurrentDate < ts2 Then



       If DayOfWeek = Weekday(7) Then

       CurrentDate = DateAdd("d",2,CurrentDate)

       End If 

       If DayOfWeek = Weekday(1) Then

       CurrentDate = DateAdd("d",1,CurrentDate)

       End If

       If DayOfWeek = Weekday(2) or DayOfWeek = Weekday(3) or DayOfWeek = Weekday(4) or DayOfWeek = Weekday(5) or DayOfWeek = Weekday(6) Then
       CurrentDate = CurrentDate
       End If


   Else If  CurrentDate > ts2 Then

       If DayOfWeek = Weekday(7) Then

       CurrentDate = DateAdd("d",2,CurrentDate)

       End If 

       If DayOfWeek = Weekday(1) Then

       CurrentDate = DateAdd("d",1,CurrentDate)

       End If

       If DayOfWeek = Weekday(2) or DayOfWeek = Weekday(3) or DayOfWeek = Weekday(4) or DayOfWeek = Weekday(5)Then
       CurrentDate = DateAdd("d",1,CurrentDate)
       End If

       If DayOfWeek = Weekday(6) Then
       CurrentDate = DateAdd("d",3,CurrentDate)
       End If
End If 
End If


Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")
Set rsRaum = Server.CreateObject("ADODB.Recordset")



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 sConnString
Recordset.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>


<%
'If  Recordset.EoF Then
'Response.write "No records to display"

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 Teilnehmer

 ' hole Raum Details
  rsRaum.open "select bezeichnung from Raum where ID = " & raum_id, Connection

      raumname = rsRaum("bezeichnung")

  rsRaum.close


  rsRaum.open  "SELECT DISTINCT NAME, FIRMA FROM TEILNEHMER 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.close

  rsRaum.open  "SELECT DISTINCT TRIGRAM FROM TEILNEHMER WHERE buchung_id = " & Recordset("ID") & " and STATUS = 1 and DAILYPLAN = 1" , Connection

        Response.Write(rsRaum.recordcount)

            if not rsRaum.EOF then

             dim new_list2
            new_list2 = ""

             do while not rsRaum.eof



                 new_list2 = new_list2 & rsRaum("TRIGRAM") & ","  


             rsRaum.movenext



          loop
           new_list2 = left(new_list2, len(new_list2)-1)

         end if


  rsRaum.close

  rsRaum.Open "SELECT distinct d.Bezeichnung, rd.Bestellmenge " & _
                                        "FROM RESERVIERUNGDIENSTLEISTUNG rd " & _
                                        "JOIN DIENSTLEISTUNG d ON rd.DIENSTLEISTUNG_ID = d.ID " & _
                                        "JOIN RESERVIERUNGRAUM rr ON rd.RESERVIERUNGRAUM_ID = rr.ID " & _
                                        "JOIN DIENSTLEISTUNGSART dlart ON d.DIENSTLEISTUNGSART_ID = dlart.ID " & _
                                        "JOIN ABRECHNUNGSGRUPPE ab ON dlart.ABRECHNUNGSGRUPPE_ID = 3 " & _
                                        "JOIN BUCHUNG b ON rr.BUCHUNG_ID = " & Recordset("ID"), Connection

             if not rsRaum.EOF then

             dim new_list3
            new_list3 = ""

             do while not rsRaum.eof



                 new_list3 = new_list3 & rsRaum("Bezeichnung") & ","  


             rsRaum.movenext



          loop
           new_list3 = left(new_list3, len(new_list3)-1)

         end if                             

 rsRaum.close





if 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><% =new_list2%><br></td>
    <td align="center"; ><% =Recordset("personenzahl")  %><br></td>
  </tr>


<%



Recordset.MoveNext
Loop
'End If



Recordset.Close 
Set Recordset = Nothing
Connection.Close
Set Recordset = Nothing 

%>


</table>
</body>
</html>

Upvotes: 1

Views: 110

Answers (1)

Flakes
Flakes

Reputation: 2442

The problem is that the values of new_list and new_list2 are not cleared in each iteration

Upvotes: 1

Related Questions