formatting problems when using multiple recordsets.

[welshone]

hello all,
I am trying to create a report in asp, in order to do this I am using multiple record sets.
I want my data to look like :

Area : UK
County : Powys
name Tel Fax
Bob 123443 123456
james 11111 111112

County : Gwent
name tel fax
steve 123232 123122
jane 121111 232111


and so on, for differnt areas and differnt countys.

problem is, I can't get the format of the :

name tel fax

part to work correctly. so far it looks like

name tel fax
bob 121232 12332
name tel fax
james 111111 111112

how can I only have the Table headings to only appear once per county ?


full code is :


<%
GeoSQL = &quot;SELECT * FROM GeographicalAreas ORDER BY GeographicalAreaDesc&quot;
Set GeoRS = Conn.Execute( GeoSQL )

EstTypeSQL = &quot;SELECT * FROM EstablishmentTypes ORDER BY EstablishmentTypeDesc&quot;
Set EstTypeRS = Conn.Execute( EstTypeSQL )

schoolSQL = &quot;SELECT GeographicalAreas.GeographicalAreaDesc,EstablishmentTypes.EstablishmentTypeDesc, Establishments.* FROM GeographicalAreas, Establishments, EstablishmentTypes &quot; _
& &quot; WHERE Establishments.GeographicalAreaID = GeographicalAreas.GeographicalAreaID AND Establishments.EstablishmentTypeID = EstablishmentTypes.EstablishmentTypeID&quot; _
& &quot; ORDER BY GeographicalAreas.GeographicalAreaDesc, EstablishmentTypes.EstablishmentTypeDesc, Establishments.EstablishmentName&quot;
Set RS = Conn.Execute( schoolSQL )

priorGeo = &quot;&quot;
priorET = &quot;&quot;
%>



<%
Do Until rs.EOF
curGeo = RS(&quot;GeoGraphicalAreaDesc&quot
curET = RS(&quot;EstablishmentTypeDesc&quot

If curGeo <> priorGeo Then
If priorGeo <> &quot;<BR>&quot; Then
Response.Write &quot;&quot;
End If
Response.Write &quot;<BR><FONT SIZE=3 FACE=Verdana COLOR=RED>&quot; & &quot;District : &quot; & curGeo & &quot;</FONT>&quot;
priorGeo = curGeo

GeoRS.MoveNext
End IF


If curET <> priorET Then
If priorET <> &quot;<BR>&quot; Then
Response.Write &quot;&quot;
End If
Response.Write &quot;<BR><FONT SIZE=2 FACE=Verdana COLOR=blue>&quot; & &quot;Est Type : &quot; & curET & &quot;</FONT>&quot;
priorET = curET

IF EstTypeRS.BOF or EstTypeRS.EOF = True THEN
Response.Write &quot;&quot;
ELSE
EstTypeRS.MoveNext
End If
End IF

%>

<TABLE BORDER=2 BGCOLOR=#ffffff CELLSPACING=1 CELLPADDING=3><FONT FACE=&quot;Arial&quot; COLOR=#000000>

<THEAD>
<TR>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Establishment Name</FONT></TH>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Postcode</FONT></TH>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Telephone</FONT></TH>
<TH nowrap BGCOLOR=#008080 BORDERCOLOR=#000000><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Fax</FONT></TH>
</TR>
</THEAD>

<TBODY>
<TR><TD><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#000000><%=RS(&quot;EstablishmentName&quot%></TD></FONT>
<TD><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#000000><%=RS(&quot;PostCode&quot%></TD></FONT>
<TD><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#000000><%=RS(&quot;Telephone&quot%></TD></FONT>
<TD><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#000000><%=RS(&quot;Fax&quot%></TD></FONT>

</TR>
</TBODY>
<TFOOT></TFOOT>
</TABLE>

<%
rs.MoveNext
Loop
%>

<%
GeoRS.Close
EstTypeRS.Close
RS.Close
Conn.Close
set GeoRS = Nothing
set EstTypeRS = Nothing
set RS = Nothing
set Conn = Nothing
%>

any ideas ?

 

[ehsant]

Hi...
you can use a variable to detect the last record you have printer on the page was the same country or not, if not then response.wite the header... :

vOldCountry = ""
Do Until rs.EOF
  .
  .
  .
<TABLE BORDER=2 BGCOLOR=#ffffff CELLSPACING=1 CELLPADDING=3><FONT FACE=&quot;Arial&quot; COLOR=#000000>
<%
If RS(&quot;Country&quot;) <> vOldCountry Then
  vOldCountry = RS(&quot;Country&quot;) 
%>
<THEAD>
<TR>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Establishment Name</FONT></TH>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Postcode</FONT></TH>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Telephone</FONT></TH>
<TH nowrap BGCOLOR=#008080 BORDERCOLOR=#000000><FONT SIZE=2 FACE=&quot;Verdana&quot; COLOR=#ffffff>Fax</FONT></TH>
</TR>
</THEAD>
<%End If%>
  .
  .
  .

----
TNX.
E.T.

 

[welshone]

cheers ehsant, I now only have the table headers showing once per country.
but the headers are only joined to the first row of data, but the rest of the rows aren't in line with them.
does that make sense ?
so I have :

Name Tel Fax
Bob 123232 122321

looks fine, then the next row, is in its own table with no headings, so is not lined up correctly.
how can I fix this ?