Web Analytics Made Easy -
StatCounter how i can appear only 1 data for the same data.. - CodingForum

Announcement

Collapse
No announcement yet.

how i can appear only 1 data for the same data..

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • how i can appear only 1 data for the same data..

    this is my coding..
    it doesnt have error but just appear only 1 name..

    <%@ Language=VBScript %>
    <!--#include file="connection.asp"-->

    <%

    sql ="SELECT DISTINCT StaffName from Training where Company = 'TSB' "
    SET RS = Conn.Execute(sql)

    %>
    <%

    sql ="SELECT * from Training where Company = 'TSB' AND StaffName='" &(rs("StaffName"))& "' "
    SET RS6 = Conn.Execute(sql)

    %>
    <%

    sql ="SELECT CourseStatus from Training where Company = 'TSB' "
    SET RS1 = Conn.Execute(sql)


    %>

    <%

    sql ="SELECT CourseStatus from Training where Company = 'TSB' "
    SET RS1 = Conn.Execute(sql)

    %>
    <%
    sql ="SELECT CourseName from Training where Company = 'TSB' "
    SET RS2 = Conn.Execute(sql)
    %>
    <%

    sql ="SELECT OrganizerName from Training where Company = 'TSB' "
    SET RS3 = Conn.Execute(sql)

    %>
    <%

    sql ="SELECT Quarter from Training where Company = 'TSB' "
    SET RS4 = Conn.Execute(sql)

    %>
    <%

    sql ="SELECT EstiCost from Training where Company = 'TSB' "
    SET RS5= Conn.Execute(sql)

    %>


    <%Response.ContentType = "application/vnd.ms-excel"%>




    <style type="text/css">
    <!--
    body,td,th {
    font-family: Arial;
    }
    .style11 {
    font-size: 10px;
    font-weight: bold;
    }
    .style12 {font-size: 10px}
    .style15 {color: #00CC66}
    -->
    </style>






    <table width="1469" border="1" cellpadding="0" cellspacing="0">
    <tr>
    <td width="61" height="16" bgcolor="#CCCCCC" class="xl22"><div align="center"><span class="style11">No </span></div></td>
    <td bgcolor="#CCCCCC" width="109" class="xl22"><div align="center"><span class="style11">Name </span></div></td>
    <td bgcolor="#CCCCCC" width="126" class="xl22"><div align="center"><span class="style11">Position </span></div></td>
    <td bgcolor="#CCCCCC" width="41" class="xl22"><div align="center"><span class="style11">A </span></div></td>
    <td bgcolor="#CCCCCC" width="46" class="xl22"><div align="center"><span class="style11">B </span></div></td>
    <td bgcolor="#CCCCCC" width="56" class="xl22"><div align="center"><span class="style11">C </span></div></td>
    <td bgcolor="#CCCCCC" width="17" class="xl22"><div align="center"><span class="style11">= </span></div></td>
    <td bgcolor="#CCCCCC" width="49" class="xl22"><div align="center"><span class="style11">TD </span></div></td>
    <td bgcolor="#CCCCCC" width="57" class="xl22"><div align="center"><span class="style11">TR </span></div></td>
    <td bgcolor="#CCCCCC" width="34" class="xl22"><div align="center"><span class="style11">Status </span></div></td>
    <td bgcolor="#CCCCCC" width="50" class="xl22"><div align="center"><span class="style11">Training Proposed </span></div></td>
    <td bgcolor="#CCCCCC" width="69" class="xl22"><div align="center"><span class="style11">Organizer </span></div></td>
    <td bgcolor="#CCCCCC" width="114" class="xl22"><div align="center"><span class="style11">Proposed Date </span></div></td>
    <td bgcolor="#CCCCCC" width="86" class="xl22"><div align="center"><span class="style11">Esrtmated Cost </span></div></td>
    <td bgcolor="#CCCCCC" width="96" class="xl22"><div align="center"><span class="style11">Total Budget </span></div></td>
    <td bgcolor="#CCCCCC" width="102" class="xl22"><div align="center"><span class="style11">Actual Cost </span></div></td>
    <td bgcolor="#CCCCCC" width="103" class="xl22"><div align="center"><span class="style11">Balance Budget </span></div></td>
    <td bgcolor="#CCCCCC" width="56" class="xl22"><div align="center"><span class="style11">Notes </span></div></td>
    <td bgcolor="#CCCCCC" width="56" class="xl22"><div align="center"><span class="style11">TIF </span></div></td>
    <td bgcolor="#CCCCCC" width="50" class="xl22"><div align="center"><span class="style11">TEF </span></div></td>
    <td bgcolor="#CCCCCC" width="47" class="xl22"><div align="center"><span class="style11">TEF6 </span></div></td>
    </tr>
    <% 'cycle thru the record set and display each row results
    do until rs6.EOF
    bil=bil+1
    %>
    <tr>
    <td height="50" class="style11"><div align="center" class="style11 style15">&nbsp;
    <div align="center"><%=bil%>&nbsp;</div>
    </div></td>
    <td class="style11"><div align="center" class="style11">&nbsp;
    <div align="center"><%= rs("StaffName")%>&nbsp;</div>
    </div></td>
    <td class="style11"><div align="center" class="style11">&nbsp;
    <div align="center"><%= rs6("SPosition")%>&nbsp;</div>
    </div></td>
    <td bgcolor="#FFCC00"class="style11"><div align="center" class="stle11">&nbsp;
    <div align="center">
    <%
    'strbln = Request("strbln")
    'strBranch = Request("strBranch")
    'strYear = Request("strYear")

    Set rsCount= Server.CreateObject("ADODB.Recordset")
    CountSql = " SELECT TrainingStatus FROM Training WHERE TrainingStatus='Planning' AND Company = 'TSB'"
    rsCount.open CountSql, conn,1,3
    response.write "" & rsCount.RecordCount
    %>&nbsp;
    </div>
    </div></td>
    <td bgcolor="#FFCC00" class="style11"><div align="center" class="style11">&nbsp;
    <div align="center">
    <%
    'strbln = Request("strbln")
    'strBranch = Request("strBranch")
    'strYear = Request("strYear")

    Set rsCount1= Server.CreateObject("ADODB.Recordset")
    CountSql = " SELECT TrainingStatus FROM Training WHERE TrainingStatus='Approved' AND Company='TSB'"
    rsCount1.open CountSql, conn,1,3
    response.write "" & rsCount1.RecordCount
    %>&nbsp;

    </div>
    </div></td>
    <td bgcolor="#FFCC00" class="style11"><div align="center" class="style11">&nbsp;
    <div align="center">
    <%
    'strbln = Request("strbln")
    'strBranch = Request("strBranch")
    'strYear = Request("strYear")

    Set rsCount= Server.CreateObject("ADODB.Recordset")
    CountSql = " SELECT TrainingStatus FROM Training WHERE TrainingStatus='Decline'"
    rsCount.open CountSql, conn,1,3
    response.write "" & rsCount.RecordCount
    %>&nbsp;
    </div>
    </div></td>
    <td bgcolor="#000000" class="style11"><div align="center"><span class="style12"><strong>&nbsp;
    </strong></span></div></td>
    <td bgcolor="#FFCC00" class="style11"><div align="center" class="style11">&nbsp;
    <div align="center">
    <%
    'strbln = Request("strbln")
    'strYear = Request("strYear")

    Set rsCount= Server.CreateObject("ADODB.Recordset")
    CountSql = " SELECT TrainingStatus, CourseName FROM Training WHERE TrainingStatus='Approved' OR TrainingStatus='Done' AND company ='TSB'"
    rsCount.open CountSql, conn,1,3
    response.write "" & rsCount.RecordCount
    %>&nbsp;
    </div>
    </div></td>





    <td bgcolor="#FFCC00" class="style11"><div align="center" class="style11">&nbsp;
    <div align="center">
    <%
    'strbln = Request("strbln")
    'strYear = Request("strYear")

    Set rsCount= Server.CreateObject("ADODB.Recordset")
    CountSql = " SELECT CourseName FROM Training WHERE company ='TSB'"
    rsCount.open CountSql, conn,1,3
    response.write "" & rsCount.RecordCount
    %>&nbsp;
    </div>
    </div></td>

    <td bgcolor="#FFCC00" class="style11"><div align="center" class="style11">
    <% 'cycle thru the record set and display each row results
    do while not rs1.EOF %>
    <%= rs1("CourseStatus")%><br>
    <%'increment record position with MoveNext method
    rs1.MoveNext
    loop
    'close connection and flush objects from memory
    rs1.Close
    set rs1 = Nothing %>
    </div></td>
    <td class="style11"><div align="center">
    <% 'cycle thru the record set and display each row results
    do while not rs2.EOF %>
    <%= rs2("CourseName")%><br>
    <%'increment record position with MoveNext method
    rs2.MoveNext
    loop
    'close connection and flush objects from memory
    rs2.Close
    set rs2 = Nothing %>
    </div></td>
    <td class="style11"><div align="center">
    <% 'cycle thru the record set and display each row results
    do while not rs3.EOF %>
    <%= rs3("OrganizerName")%><br>
    <%'increment record position with MoveNext method
    rs3.MoveNext
    loop
    'close connection and flush objects from memory
    rs3.Close
    set rs3 = Nothing %>
    </div></td>
    <td class="style11"><div align="center" class="style11">
    <div align="center" class="style11">
    <div align="center">
    <div align="center" class="style11">
    <div align="center" class="style11">
    <div align="center">&nbsp;
    <% 'cycle thru the record set and display each row results
    do while not rs4.EOF %>
    <%= rs4("Quarter")%><br>
    <%'increment record position with MoveNext method
    rs4.MoveNext
    loop
    'close connection and flush objects from memory
    rs4.Close
    set rs4 = Nothing %>
    </div>
    </div>
    </div>
    </div>
    </div>
    </div></td>
    <td class="style11"><div align="center" class="style11">
    <div align="center" class="style11">
    <div align="center" class="style11">
    <div align="center">
    <% 'cycle thru the record set and display each row results
    do while not rs5.EOF %>
    <%=FormatNumber(rs5("estiCost"),2)%><br>
    <%'increment record position with MoveNext method
    rs5.MoveNext
    loop
    'close connection and flush objects from memory
    rs5.Close
    set rs5 = Nothing %>
    &nbsp;</div>
    </div>
    </div>
    * *</div></td>
    <td class="style11"><div align="center" class="style11">&nbsp;
    <div align="center">
    <% Dim Budget1
    Budget1 = 0
    sql = "SELECT * from Training WHERE Company='TSB'"
    SET RS1= Conn.Execute(sql)
    While Not RS1.EOF
    If Not IsNull(RS1("EstiCost")) Then
    Budget1 = Budget1 + CSng(RS1("EstiCost"))
    End If
    RS1.MoveNext
    Wend
    Response.Write "" & FormatNumber(Budget1,2)

    %>
    &nbsp; </div>
    </div></td>
    <td class="style11"><div align="center" class="style11">&nbsp;
    <div align="center">
    <% Dim Budget2
    Budget2 = 0
    sql = "SELECT * from Training WHERE Company='TSB'"
    SET RSy= Conn.Execute(sql)
    While Not RSy.EOF
    If Not IsNull(RSy("CourseExpense")) Then
    Budget2 = Budget2 + CSng(RSy("CourseExpense"))
    End If
    RSy.MoveNext
    Wend
    Response.Write "" & FormatNumber(Budget2,2)

    %>
    &nbsp; </div>
    </div></td>
    <td class="style11"><div align="center" class="style11">&nbsp;
    <div align="center">
    <% Dim VARIANCE
    VARIANCE = 0
    VARIANCE=Budget1-Budget2
    Response.Write "" & FormatNumber(VARIANCE,2)


    %>
    &nbsp; </div>
    </div></td>
    <td class="xl22">&nbsp; </td>
    <td class="xl22">&nbsp; </td>
    <td class="xl22">&nbsp; </td>
    <td class="xl22">&nbsp; </td>

    </tr>
    <% rowCount = rowCount+1
    'increment record position with MoveNext method
    rs6.MoveNext
    loop
    'close connection and flush objects from memory
    rs6.Close
    set rs6 = Nothing %>

    </table>

  • #2
    Because you are only *ASKING* for *ONE* name.
    Code:
    sql ="SELECT DISTINCT StaffName from Training where Company = 'TSB' "
    SET RS = Conn.Execute(sql)
    
    sql ="SELECT * from Training where Company = 'TSB' AND StaffName='" &(rs("StaffName"))& "' "
    SET RS6 = Conn.Execute(sql)
    Your RS6 will *ONLY* contain the data for *ONE* StaffName. To wit, the StaffName of the FIRST RECORD in the table Training

    I have to say that this is pretty bad coding. Sorry. You keep going back to the SAME table and looping through the SAME data, over and over and over again! At a minimum, you could have gotten all the data into memory at once and simply moved back to the beginning of the recordset each time you needed a new loop. Better would probably have been to use GETROWS to convert the data to an array and then loop through the array as needed.
    Be yourself. No one else is as qualified.

    Comment


    • #3
      can u give me the sample coding to appear only 1 data for the same data..
      actually im a student..
      so i dont have much knowledge about this..
      please help me..

      Comment


      • #4
        I'm sorry but I do not understand that question.

        "only 1 data for the same data"

        How would you get more than one for one???
        Be yourself. No one else is as qualified.

        Comment


        • #5

          what i meant was, if you can see in the above table, we have several staff, attending several courses.what im trying to do is to display these details exactly like in the above table..

          i can only do like below..

          staffname ... courses
          siti<t> Adobe Photoshop
          siti<t> Public Speaking

          rahmah<t> Management
          rahmah<t> Housekeeping

          i dont want the same name to display again and again.. only 1 nae for each courses they attended. hope you can give me some guidance on how to tackle this problem.thanks.

          Last edited by IraFashua; Apr 2, 2009, 02:34 AM. Reason: cannot see

          Comment

          Working...
          X