Frågan#1
Jag har ett problem med den här koden som jag hoppas kunna få hjälp med.
Jag har ett antal avdelningar med underavdelningar.
Jag vill göra en statistiksida där jag visar hur många avdelningar, hur många underavdelningar
och hur många det finns av varje kategori (4 st) på respektive avdelning och underavdelning.
Den här koden (kanske något överarbetad?) fungerar perfekt.
D.v.s. om alla underavdelningar har minst en av varje kategori!
Saknas någon får jag felmeddelandet:
=======
Antingen är BOF eller EOF satt till True, eller så har aktuell post raderats;
åtgärden som programmet begärt kräver en aktuell post
======
Har försökt med en massa If-satser och andra trix, men så fort en underavdelning saknar en av kategorierna kommer felmeddelandet. Något tips?
<% ' Här finns all data
tabellA = "databas"%>
<% ' här finns namn på alla avdelningar o grupper
tabellB = "databasNamn"%>
<% 'Här är kolumnerna i tabellA
kolA1 = "Avdelning"
kolA2 = "Underavdelning"
%>
<% 'Här är kolumnerna i tabellB
kolB11 = "Avdelning"
kolB12 = "Avdelningnamn"
kolB21 = "Underavdelning"
kolB22 = "UnderavdelningNamn"
%>
<% ' kategorier som skall spaltas upp
katA = "AND pkat = '34'"
katB = "AND pkat = '35'"
katC = "AND pkat = '36'"
katD = "AND pkat = '38'"
villkor = "AND hemma = false"%>
<%queryA = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katA&" "&villkor&" group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katA&" "&villkor&" group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryB = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katB&" "&villkor&" group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katB&" "&villkor&" group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryC = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katC&" "&villkor&" group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katC&" "&villkor&" group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryD = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katD&" "&villkor&" group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katD&" "&villkor&" group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryABCD = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&villkor&" group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&villkor&" group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryTOTAL = "select count(*) as totalt from "&tabellA&" where "&tidin&" AND "&tidut&" "&villkor&" "%>
<table border="1" cellpadding="0" cellspacing="0">
<%
set rsA = connect.execute(queryA)
set rsB = connect.execute(queryB)
set rsC = connect.execute(queryC)
set rsD = connect.execute(queryD)
set rsABCD = connect.execute(queryABCD)
set rsTOTAL = connect.execute(queryTOTAL)
%>
<%while not rsABCD.eof%>
<%if cint(rsABCD("typ")) = 1 then%>
<TR>
<td colspan='7' height=4></TD>
</TR>
<TR>
<%nummer = rsABCD(kolB11)
'response.write nummer
%>
<%queryK = "select distinct * from "&tabellB&" where "&kolB11&" ='"&nummer&"'"%>
<%set rsK = connect.execute(queryK)%>
<%nummernamn = rsK(""&kolB12&"")%>
<td colspan="1"><b><%=nummer%></b></td>
<td colspan="1"><b><%=nummernamn%></b></td>
<td colspan="1"><b><%=rsA("Total")%></b></td>
<td colspan="1"><b><%=rsB("Total")%></b></td>
<td colspan="1"><b><%=rsC("Total")%></b></td>
<td colspan="1"><b><%=rsD("Total")%></b></td>
<td colspan="1"><b><%=rsABCD("Total")%></b></td>
</TR>
<%else%>
<%
nummer1 = rsABCD(""&kolA1&"")
nummer2 = rsABCD(""&kolA2&"")
%>
<%queryAA = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katA&" "&villkor&" AND "&kolA1&" = '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katA&" "&villkor&" AND "&kolA1&" = '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryBB = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katB&" "&villkor&" AND "&kolA1&"= '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katB&" "&villkor&" AND "&kolA1&"= '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryCC = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katC&" "&villkor&" AND "&kolA1&" = '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katC&" "&villkor&" AND "&kolA1&" = '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%queryDD = "select count(*) as Total, "&kolA1&", '' as "&kolA2&",1 as typ from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katD&" "&villkor&" AND "&kolA1&" = '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&" "&_
"union select count(*) ,"&kolA1&","&kolA2&",2 from "&tabellA&" "&_
"where "&tidin&" AND "&tidut&" "&katD&" "&villkor&" AND "&kolA1&" = '"&nummer1&"' AND "&kolA2&" = '"&nummer2&"' group by "&kolA1&","&kolA2&" order by "&kolA1&",typ,"&kolA2&""%>
<%
set rsAA = connect.execute(queryAA)
set rsBB = connect.execute(queryBB)
set rsCC = connect.execute(queryCC)
set rsDD = connect.execute(queryDD)
'response.write rsBB(""&kolA2&"")
'response.write nummer1
'response.write nummer2
%>
<TR>
<%queryP = "select distinct "&kolB22&" from "&tabellB&" where "&kolA1&" ='"&nummer1&"' AND "&kolA2&" ='"&nummer2&"'"%>
<%set rsP = connect.execute(queryP)%>
<%'response.write rsP(kolB22)
namn = rsP(kolB22)
%>
<td colspan="1"><b> <%=rsABCD(""&kolA2&"")%></b></td>
<td colspan="1"><b><%=namn%></b></td>
<td colspan="1"><b><%=rsAA("Total")%></b></td>
<td colspan="1"><b><%=rsBB("Total")%></b></td>
<td colspan="1"><b><%=rsCC("Total")%></b></td>
<td colspan="1"><b><%=rsDD("Total")%></b></td>
<td colspan="1"><b><%=rsABCD("Total")%></b></td>
</TR>
<%end if%>
</tr>
<%
rsABCD.movenext
rsA.movenext
rsB.movenext
rsC.movenext
rsD.movenext
%>
<%wend%>
<%rsABCD.close%>
<%'set rsP=nothing%>
</TABLE>
