parallon
09-10-2008, 05:55 PM
Hello all. I have 3 queries (1 main and 2 sub) that I am trying to get to work. The issue I am having is that my array is getting pupulated with the same record multiple times (Which happens to be the last record). I have tried almost everything, but I still can't find where my error is. Can someone help? Throughout the code I am doing a response.write to see the data, and it is proper, but the array isn't being pupulated correctly. Below is the code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/mmm.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<%
Dim LaborArray(50,2) ' Array for Graph XML data.
Dim MaterialsArray(50,2)
x=0
y=0
Dim rsPackages
Dim rsPackages_cmd
Dim rsPackages_numRows
Set rsPackages_cmd = Server.CreateObject ("ADODB.Command")
rsPackages_cmd.ActiveConnection = MM_mmm_STRING
rsPackages_cmd.CommandText = "SELECT Equipment, EquipmentDesc FROM Equipment WHERE ParentId IS NULL ORDER BY Equipment ASC"
rsPackages_cmd.Prepared = true
Set rsPackages = rsPackages_cmd.Execute
rsPackages_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rsPackages_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
If Not rsPackages.EOF Or Not rsPackages.BOF Then
Dim strHeading
strHeading = ""
While ((Repeat1__numRows <> 0) AND (NOT rsPackages.EOF))
If strHeading <> (rsPackages("Equipment").Value) then
vPackage = rsPackages("Equipment").value
End If
%>
<% '*************************************************************************************************** ************************ %>
<%
' ****************** Get list of ALL Labor for current package ****************
Dim vEquipment
vEquipment = vPackage
Dim rsLabor
Dim rsLabor_cmd
Dim rsLabor_numRows
Set rsLabor_cmd = Server.CreateObject ("ADODB.Command")
rsLabor_cmd.ActiveConnection = MM_mmm_STRING
rsLabor_cmd.CommandText = "SELECT WOLabour.TotalCost AS SumOfTotalLabor, WorkOrder.Equipment, WOLabour.TransDate FROM WOLabour RIGHT JOIN WorkOrder ON WOLabour.WoNum = WorkOrder.WoNum WHERE (((WorkOrder.Equipment) LIKE '" & vEquipment & "%') AND ((WOLabour.TransDate) Between #1/1/2008# And #12/1/2008#)) ORDER BY WorkOrder.Equipment"
rsLabor_cmd.Prepared = true
Set rsLabor = rsLabor_cmd.Execute
'rsLabor_numRows = 0
Dim Repeat2__numRows
Dim Repeat2__index
Repeat2__numRows = -1
Repeat2__index = 0
rsLabor_numRows = Recordset2_numRows + Repeat2__numRows
vLaborTotal = 0
Dim strHeading2 ' strHeading will be populated with Craft Codes
'strHeading = ""
strHeading2 = vEquipment
response.Write("strHeading2 is " & strHeading2 & "<BR>")
While ((Repeat2__numRows <> 0) AND (NOT rsLabor.EOF))
If strHeading2 <> vPackage Then ' If this is a NEW Craft Code, then ...
response.Write(strHeading2 & " Total = " & vLaborTotal & "<BR>") ' For Testing...
x=x+1 ' Advance Array location for Craft Codes by 1
LaborArray(x,1) = strHeading2 'Add current Equipment code to Array
LaborArray(x,2) = vLaborTotal ' Add Labor Cost for current Craft to Array.
vLaborTotal = 0 ' Reset Labor Cost back to '0' after populating array.
End If
vLaborTotal = round(vLaborTotal + rsLabor("SumOfTotalLabor"),2) ' Total cost for ALL labor of this Craft Code
strHeading2 = vEquipment
Response.Write(vLaborTotal & "<BR>") ' For testing individual totals
Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
rsLabor.MoveNext()
Wend
response.Write(strHeading2 & " Labor Total is " & vLaborTotal & "<BR>") ' For Testing...
LaborArray(x,1) = strHeading2
LaborArray(x,2) = vLaborTotal
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
strHeading = (rsPackages.Fields.Item("Equipment").Value)
rsPackages.MoveNext()
Wend
%>
<% End If ' end Not rsPackages.EOF Or NOT rsPackages.BOF %>
</head>
<body>
<%
' XML string for the graph.
xmlStr = "<chart caption='"& vTitle & "' subCaption='" & vEquip & " | "& vWeek &"' enableRotation='1' pieYScale='60' startingAngle='345' pieSliceDepth='25' formatNumberScale='0' formatNumber='1' decimals='2' forceDecimals='1' labelSepChar=' - ' showPercentageValues='1' animation='1' showLabels='1' showValues='1' >" & vbcrlf & vbcrlf
'If Not rsLabor.EOF Then
For i=0 to UBound(LaborArray)-1
xmlStr = xmlStr & "<set label='" & LaborArray(x,1) & "' value='" & LaborArray(x,2) & "' hoverText='" & LaborArray(x,2) & "' isSliced='1' />" & vbcrlf
Next
'For i=0 to UBound(MaterialsArray)-1
'xmlStr = xmlStr & "<set label='" & MaterialsArray(y,1) & "' value='" & MaterialsArray(y,2) & "' hoverText='" & MaterialsArray(y,2) & "' isSliced='1' />" & vbcrlf
'Next
'End If
'xmlStr = xmlStr & "<set label='Labor' value='"& vTotal &"' isSliced='1' />" & vbcrlf
xmlStr = xmlStr & "</chart>" & vbcrlf
%>
<p>
<OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" WIDTH="650" HEIGHT="400" id="FusionCharts" ALIGN="">
<PARAM NAME=movie VALUE="Charts/Pie3d.swf?noCache=1&chartWidth=650&chartHeight=400">
<PARAM NAME="FlashVars" VALUE="&dataXML=<%=xmlStr%>">
<PARAM NAME=quality VALUE=high>
<PARAM NAME=bgcolor VALUE=#FFFFFF>
<EMBED src="Charts/Pie3d.swf" FlashVars="&dataXML=<%=xmlStr%>&chartWidth=300&chartHeight=400" quality=high bgcolor=#FFFFFF WIDTH="650" HEIGHT="400" NAME="FusionCharts" ALIGN="" TYPE="application/x-shockwave-flash" PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer"></EMBED>
</OBJECT>
</p>
<p>
<OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" WIDTH="300" HEIGHT="200" ALIGN="">
<PARAM NAME=movie VALUE="Charts/SSGrid.swf?noCache=2&chartWidth=300&chartHeight=400">
<PARAM NAME="FlashVars" VALUE="&dataXML=<%=xmlStr%>">
<PARAM NAME=quality VALUE=high>
<PARAM NAME=bgcolor VALUE=#FFFFFF>
<EMBED src="Charts/SSGrid.swf" FlashVars="&dataXML=<%=xmlStr%>&chartWidth=650&chartHeight=400" quality=high bgcolor=#FFFFFF WIDTH="300" HEIGHT="400" ALIGN="" TYPE="application/x-shockwave-flash" PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer"></EMBED>
</OBJECT>
</p>
</body>
</html>
Thanks,
Parallon
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/mmm.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<%
Dim LaborArray(50,2) ' Array for Graph XML data.
Dim MaterialsArray(50,2)
x=0
y=0
Dim rsPackages
Dim rsPackages_cmd
Dim rsPackages_numRows
Set rsPackages_cmd = Server.CreateObject ("ADODB.Command")
rsPackages_cmd.ActiveConnection = MM_mmm_STRING
rsPackages_cmd.CommandText = "SELECT Equipment, EquipmentDesc FROM Equipment WHERE ParentId IS NULL ORDER BY Equipment ASC"
rsPackages_cmd.Prepared = true
Set rsPackages = rsPackages_cmd.Execute
rsPackages_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rsPackages_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
If Not rsPackages.EOF Or Not rsPackages.BOF Then
Dim strHeading
strHeading = ""
While ((Repeat1__numRows <> 0) AND (NOT rsPackages.EOF))
If strHeading <> (rsPackages("Equipment").Value) then
vPackage = rsPackages("Equipment").value
End If
%>
<% '*************************************************************************************************** ************************ %>
<%
' ****************** Get list of ALL Labor for current package ****************
Dim vEquipment
vEquipment = vPackage
Dim rsLabor
Dim rsLabor_cmd
Dim rsLabor_numRows
Set rsLabor_cmd = Server.CreateObject ("ADODB.Command")
rsLabor_cmd.ActiveConnection = MM_mmm_STRING
rsLabor_cmd.CommandText = "SELECT WOLabour.TotalCost AS SumOfTotalLabor, WorkOrder.Equipment, WOLabour.TransDate FROM WOLabour RIGHT JOIN WorkOrder ON WOLabour.WoNum = WorkOrder.WoNum WHERE (((WorkOrder.Equipment) LIKE '" & vEquipment & "%') AND ((WOLabour.TransDate) Between #1/1/2008# And #12/1/2008#)) ORDER BY WorkOrder.Equipment"
rsLabor_cmd.Prepared = true
Set rsLabor = rsLabor_cmd.Execute
'rsLabor_numRows = 0
Dim Repeat2__numRows
Dim Repeat2__index
Repeat2__numRows = -1
Repeat2__index = 0
rsLabor_numRows = Recordset2_numRows + Repeat2__numRows
vLaborTotal = 0
Dim strHeading2 ' strHeading will be populated with Craft Codes
'strHeading = ""
strHeading2 = vEquipment
response.Write("strHeading2 is " & strHeading2 & "<BR>")
While ((Repeat2__numRows <> 0) AND (NOT rsLabor.EOF))
If strHeading2 <> vPackage Then ' If this is a NEW Craft Code, then ...
response.Write(strHeading2 & " Total = " & vLaborTotal & "<BR>") ' For Testing...
x=x+1 ' Advance Array location for Craft Codes by 1
LaborArray(x,1) = strHeading2 'Add current Equipment code to Array
LaborArray(x,2) = vLaborTotal ' Add Labor Cost for current Craft to Array.
vLaborTotal = 0 ' Reset Labor Cost back to '0' after populating array.
End If
vLaborTotal = round(vLaborTotal + rsLabor("SumOfTotalLabor"),2) ' Total cost for ALL labor of this Craft Code
strHeading2 = vEquipment
Response.Write(vLaborTotal & "<BR>") ' For testing individual totals
Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
rsLabor.MoveNext()
Wend
response.Write(strHeading2 & " Labor Total is " & vLaborTotal & "<BR>") ' For Testing...
LaborArray(x,1) = strHeading2
LaborArray(x,2) = vLaborTotal
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
strHeading = (rsPackages.Fields.Item("Equipment").Value)
rsPackages.MoveNext()
Wend
%>
<% End If ' end Not rsPackages.EOF Or NOT rsPackages.BOF %>
</head>
<body>
<%
' XML string for the graph.
xmlStr = "<chart caption='"& vTitle & "' subCaption='" & vEquip & " | "& vWeek &"' enableRotation='1' pieYScale='60' startingAngle='345' pieSliceDepth='25' formatNumberScale='0' formatNumber='1' decimals='2' forceDecimals='1' labelSepChar=' - ' showPercentageValues='1' animation='1' showLabels='1' showValues='1' >" & vbcrlf & vbcrlf
'If Not rsLabor.EOF Then
For i=0 to UBound(LaborArray)-1
xmlStr = xmlStr & "<set label='" & LaborArray(x,1) & "' value='" & LaborArray(x,2) & "' hoverText='" & LaborArray(x,2) & "' isSliced='1' />" & vbcrlf
Next
'For i=0 to UBound(MaterialsArray)-1
'xmlStr = xmlStr & "<set label='" & MaterialsArray(y,1) & "' value='" & MaterialsArray(y,2) & "' hoverText='" & MaterialsArray(y,2) & "' isSliced='1' />" & vbcrlf
'Next
'End If
'xmlStr = xmlStr & "<set label='Labor' value='"& vTotal &"' isSliced='1' />" & vbcrlf
xmlStr = xmlStr & "</chart>" & vbcrlf
%>
<p>
<OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" WIDTH="650" HEIGHT="400" id="FusionCharts" ALIGN="">
<PARAM NAME=movie VALUE="Charts/Pie3d.swf?noCache=1&chartWidth=650&chartHeight=400">
<PARAM NAME="FlashVars" VALUE="&dataXML=<%=xmlStr%>">
<PARAM NAME=quality VALUE=high>
<PARAM NAME=bgcolor VALUE=#FFFFFF>
<EMBED src="Charts/Pie3d.swf" FlashVars="&dataXML=<%=xmlStr%>&chartWidth=300&chartHeight=400" quality=high bgcolor=#FFFFFF WIDTH="650" HEIGHT="400" NAME="FusionCharts" ALIGN="" TYPE="application/x-shockwave-flash" PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer"></EMBED>
</OBJECT>
</p>
<p>
<OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" WIDTH="300" HEIGHT="200" ALIGN="">
<PARAM NAME=movie VALUE="Charts/SSGrid.swf?noCache=2&chartWidth=300&chartHeight=400">
<PARAM NAME="FlashVars" VALUE="&dataXML=<%=xmlStr%>">
<PARAM NAME=quality VALUE=high>
<PARAM NAME=bgcolor VALUE=#FFFFFF>
<EMBED src="Charts/SSGrid.swf" FlashVars="&dataXML=<%=xmlStr%>&chartWidth=650&chartHeight=400" quality=high bgcolor=#FFFFFF WIDTH="300" HEIGHT="400" ALIGN="" TYPE="application/x-shockwave-flash" PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer"></EMBED>
</OBJECT>
</p>
</body>
</html>
Thanks,
Parallon