Grouping of items properly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Grouping of items properly

Author

Topic

froggy
Starting Member

14 Posts

Posted - 2007-03-03 : 13:39:52

Hi,
i am currently doing up a website and i would like one of my page to look like the ones as shown in the example. However, i am not sure what went wrong with my code as i did not manage to reflect the roles for each Section. This happens when the role of the person is the same as that previously.

Eg. In each page, the people are divided into the different sections they belong to. In each of these sections, the people are further grouped according to the roles they belong to. Either RTL, RTLA, Staff-Imm, Staff-Exd, Staff-SD (in this order). Ideally, the names of these 5 roles should appear orderly and under it, the details of the staff. However, a problem which i am facing currently is that when Section1 ends with (eg. Staff-Exd) and Section2 happens to begin with (eg. Staff-Exd), the role "Staff-Exd" is not reflected. Instead only the details of the staff is reflected. This problem is only solved when the roles of the end of the first section is not the same as that of the beginning of the second section.

Below is a part of the code and i hope for someone to give me a clue to put an end to my problem.


<%
strSQL = "SELECT RTL, RTLA, SI, SE, SSD FROM Employee WHERE RTL=1 OR RTLA=1 OR SI=1 OR SE=1 OR SSD=1 ORDER BY Section"
nRecRU = GetRecordset(strSQL, arrRU)
	
strSQL = "SELECT RoleUser, Surname + ',' + Gname, EmpId, Onum, Mnum, Hnum, Section FROM SectII WHERE Div=(SELECT Div FROM Employee WHERE EmpId = '" & strUsername & "') AND Dept=(SELECT Dept FROM Employee WHERE EmpId = '" & strUsername & "') AND RoleUser<>'0) NA' GROUP BY RoleUser, Surname + ',' + Gname, EmpId, Onum, Mnum, Hnum, Section ORDER BY Section"
nRecDisplay = GetRecordset(strSQL, arrDisplay)
	
strSectID=""	
strSectIDA=""
strRoleID=""
strRoleIDA=""
%>

<tr>
<td width="100%">
<form name="frmMain" method="POST"action="ReportDH.asp">
<table border="0" width="100%" style="font-size: 10pt">
<tr>
<td width="10%" valign="top" bgcolor="#000080"><font color="#FFFFFF"><b>Section / Role</b></font></td>
<td width="20%" valign="top" bgcolor="#000080"><font color="#FFFFFF"><b>Name</b></font></td>
<td width="7%" valign="top" bgcolor="#000080"><font color="#FFFFFF"><b>Employee ID</b></font></td>
<td width="7%" valign="top" bgcolor="#000080"><font color="#FFFFFF"><b>Office Number</b></font></td>
<td width="7%" valign="top" bgcolor="#000080"><font color="#FFFFFF"><b>Mobile Number</b></font></td>
<td width="7%" valign="top" bgcolor="#000080"><font color="#FFFFFF"><b>Home Number</b></font></td>
</tr>
<% for i=0 to nRecDisplay-1
if arrDisplay(6,i)<>strSectID then
        strSectID = arrDisplay(6,i)
%>
<tr>
<td width="100%" colspan="7" bgcolor="#7EB6FF" valign="top">
<b><%=arrDisplay(6,i)%></b>
 	<table border="0" width="100%" cellspacing="0" cellpadding="0" style="font-size: 10pt">
</table>
</td>
</tr>
<% end if %>
            <%    		  
if arrDisplay(0,i) = "1) RTL" then
        	arrDisplay(0,i) = "RTL"
else
if arrDisplay(0,i) = "2) RTLA" then
        	arrDisplay(0,i) = "RTLA"
else
if arrDisplay(0,i) = "3) Staff - Imm" then
        	arrDisplay(0,i) = "Staff-Imm"
else
if arrDisplay(0,i) = "4) Staff - Exd" then
        	arrDisplay(0,i) = "Staff-Exd"
else
if arrDisplay(0,i) = "5) Staff - SD" then
        	arrDisplay(0,i) = "Staff-SD"
end if
end if
end if
end if
end if		
					
for j=0 to nRecDisplay-1
  if arrDisplay(0,i)<>strRoleID then
   strRoleID = arrDisplay(0,i)	
%>	
 <tr>
 <td width="100%" colspan="7" bgcolor="#D2D2D2" valign="top">
<%=arrDisplay(0,i)%>
<table border="0" width="100%" cellspacing="0" cellpadding="0" style="font-size: 10pt">
  </table>
  </td>
  </tr>
  <%end if%>
          
  <%next%>
	
  <tr bgcolor="<%if i mod 2 = 1 then Response.write "#E5E5E5" else Response.write "#FFFFFF" end if%>">

<td width="10%" valign="top"></td>
<td width="20%" valign="top"><%=arrDisplay(1,i)%></td>
<td width="7%" valign="top"><%=arrDisplay(2,i)%></td>
<td width="7%" valign="top"><%=arrDisplay(3,i)%></td>
<td width="7%" valign="top"><%=arrDisplay(4,i)%></td>
<td width="7%" valign="top"><%=arrDisplay(5,i)%></td>
</tr>
          
<%next%>
		
<td colspan=7 vAlign=top align=right><input type=button value="Edit RC Linkage" name="cmdLink" onclick="cmdLink_onclick()"> <input type=reset value=Reset> </td>
</tr>-->
</table>
<p>
<input type="hidden" name="Username" size="20" value="<%=strUsername%>">
</p>
<input type="hidden" name="SectName" value="<%Request "strSectTitle")%>">
<input type="hidden" name="SearchDoc" value="<%=Request("SearchDoc")%>">
<input type="hidden" name="SearchReg" value="<%=Request("SearchReg")%>">
<input type="hidden" name="SearchCap" value="<%=Request("SearchCap")%>">
</form>
        
</td>
</tr>

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources