Discuss "Loop / Summary help" in the ASP Development forum.

Rebelle · #1 (**permalink**) April 10th, 2008, 04:03 PM

All,

I have the following code, which returns 49 records with the lines that I've commented out. but when I try to use the lines that are commented out, I don't get any results.

I'm trying to summarize the 49 records to display only 1 record. Tools and OnOrder repeat and I want to only show them once...and sum the SumShipped.

Code:

<%
'strLastTools = rs("Tools")
'strLastRegionID = rs("RegionID")
'strLastDistrictID = rs("DistrictID")
	do while not rs.EOF
		if i > iStopRec then
			exit do
		end if
	
	strQ1 = rs("OnOrder1")
	strQ1Shipped = strQ1Shipped + rs("SumShipped")

	strCurrentTools = rs("Tools")
'	strCurrentRegionID = rs("RegionID")
'	strCurrentDistrictID = rs("DistrictID")

'	If strCurrentTools<>strLastTools Then

		Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""150""><b>" & strCurrentTools & "</b></a></td>")
			Response.Write("<td align=center colwidth=""50""><b>" & strQ1 & "</b></td>")
		strQ1 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ1Shipped & "</b></td></tr>")
		strQ1Shipped = 0
'	End If
'	strLastTools = strCurrentTools	
'	strLastRegionID = strCurrentRegionID
'	strLastDistrictID = strCurrentDistrictID

%>


<%
	i = i + 1
  rs.MoveNext
loop
%>

Any suggestions/help is greatly appreciated.

richyrich · #2 (**permalink**) April 11th, 2008, 03:57 AM

The reason it shows no records must have something to do with this line:-

Code:

If strCurrentTools<>strLastTools Then

Without seeing how these variables are populated it's difficult to see what the problem is.

Code:

'strLastTools = rs("Tools")

strCurrentTools = rs("Tools")

Both strCurrentTools and strLastTools are populated from the same recordset. How are these values retrieved from your db?

Could you post some more code?

Rebelle · #3 (**permalink**) April 11th, 2008, 08:08 AM

Thanks for the reply Richyrich!

Here is my sql statement:

Code:

sSql = "Select ToolID,RegionID,DistrictID,Tools, OnOrder1, OnOrder2, OnOrder3, OnOrder4, RolloutShipped,Sum(IsNull([RolloutShipped],0)) as SumShipped From vwSumShipped"
sSql = sSql & " WHERE 1=1"


sSql = sSql & " Group By ToolID,RegionID,DistrictID,Tools,OnOrder1,OnOrder2,OnOrder3,OnOrder4,RolloutShipped,SumShipped"
sSql = sSql & " Order By " & sOrderBy & " " & sOrderByDir

Then here is what I have:

Code:

<%
Dim strCurrentTools, strLastTools, strCurrentRegionID, strLastRegionID, strCurrentDistrictID, strLastDistrictID, strQ1, strQ1Shipped
if rs.EOF and rs.BOF then
else
%>


    <TABLE border="3" width=1000>
      <TR bgcolor="gray">
<TD>Tools</TD>
<td><b>Q1-08</b></td>
<td><b>Q1 Shipped</b></td>
</TR>

</CENTER>

<%
strLastTools = rs("Tools")
strLastRegionID = rs("RegionID")
strLastDistrictID = rs("DistrictID")
	do while not rs.EOF
		if i > iStopRec then
			exit do
		end if
	
	strQ1 = rs("OnOrder1")
	strQ1Shipped = strQ1Shipped + rs("SumShipped")

	strCurrentTools = rs("Tools")
	strCurrentRegionID = rs("RegionID")
	strCurrentDistrictID = rs("DistrictID")

'	If strCurrentTools<>strLastTools Then

		Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""150""><b>" & strCurrentTools & "</b></a></td>")
			Response.Write("<td align=center colwidth=""50""><b>" & strQ1 & "</b></td>")
		strQ1 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ1Shipped & "</b></td></tr>")
		strQ1Shipped = 0
'	End If
'	strLastTools = strCurrentTools	
'	strLastRegionID = strCurrentRegionID
'	strLastDistrictID = strCurrentDistrictID

%>


<%
	i = i + 1
  rs.MoveNext
loop
%>
<%
rs.Close
end if
%>

I'm getting results like this 49 records, instead of it just showing me one line with the Q1 Shipped column summed up:
ToolName------------Q1-08-------------Q1 Shipped
Hammer----------------6-------------------1
Hammer----------------6-------------------0
Hammer----------------6-------------------1
Hammer----------------6-------------------0

Thanks for your help.

richyrich · #4 (**permalink**) April 11th, 2008, 08:55 AM

I'm not quite sure where all the other fields you include in the SELECT statement come into it?

I would have thought something as simple as this would suffice.

Code:

sSQL = "SELECT Tools, onOrder1, Sum(IsNull([RolloutShipped],0)) As sum_shipped FROM vwSumShipped WHERE 1=1 GROUP BY Tools ORDER BY " & sOrderBy & " " & sOrderByDir

These are the only fields I can see you using.

Also not sure what the significance of WHERE 1=1 is?

Could you give an example of what it looks like against what you want it to look like?

Rebelle · #5 (**permalink**) April 11th, 2008, 09:26 AM

Hey Richyrich,

Thanks for the info.

about the where 1=1....someone told me to always use this.

Anyhow, with the info above, I removed the other fields....I was thinking I needed them...but I did added the OnOrder2,OnOrder3, and OnOrder4 back in.

With your help, I got it to look like I wanted.

Like:
Tools----------------Q1-08--------Q1 Shipped
Hammer----------------6---------------2
Jigsaw-----------------4---------------3

I guess I didn't need all those extra commented out lines....so I removed them as so:

Code:

<%
	do while not rs.EOF
		if i > iStopRec then
			exit do
		end if
	
	strTools = rs("Tools")
	strQ1 = rs("OnOrder1")
	strQ1Shipped = strQ1Shipped + rs("SumShipped")
	strQ2 = rs("OnOrder2")
	strQ3 = rs("OnOrder3")
	strQ4 = rs("OnOrder4")

	strCurrentTools = rs("Tools")

		Response.Write("<tr bgcolor=#D3D3D3><td colwidth=""150""><b>" & strTools & "</b></a></td>")
			Response.Write("<td align=center colwidth=""50""><b>" & strQ1 & "</b></td>")
		strQ1 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ1Shipped & "</b></td>")
		strQ1Shipped = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ2 & "</b></td>")
		strQ2 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ3 & "</b></td>")
		strQ3 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ4 & "</b></td></tr>")
		strQ4 = 0	

%>

I guess what was throwing me off too...was I had the sumShipped in the groupby.

Thanks again!

richyrich · #6 (**permalink**) April 11th, 2008, 09:33 AM

No Problem. Glad you got it sorted.

In general, you only need to group by the elements you want contracted. In this case you only wanted to see 1 of each Tool element, so if you group everything by that element, you will only receive 1 record for each instance.

Hope that makes sense...

mehere · #7 (**permalink**) April 11th, 2008, 09:38 AM

Quote:

Originally Posted by richyrich

Also not sure what the significance of WHERE 1=1 is?

Using the WHERE 1 = 1 ... is good to use when building dynamic SQL statements ... for example a search function on your site. this way when the WHERE clause is created ... you use AND or OR without figuring out when to use WHERE. something like this

Code:

strSQL = "SELECT * FROM tbl_TableName WHERE 1= 1 "
if request("field1") <> "" then
     strSQLEND = strsQLEND & "OR field1 = " & request("field1") & " "
end if
if request("field2") <> "" then
      strSQLEND = strsQLEND & "OR field2 = " & request("field2") & " "
end if
strSQL = strSQL & strSQLEND
set rs = dbConn.Execute(strSQL)

richyrich · #8 (**permalink**) April 11th, 2008, 10:12 AM

Quote:

Originally Posted by mehere

Using the WHERE 1 = 1 ... is good to use when building dynamic SQL statements ... for example a search function on your site. this way when the WHERE clause is created ... you use AND or OR without figuring out when to use WHERE. something like this

Code:

strSQL = "SELECT * FROM tbl_TableName WHERE 1= 1 "
if request("field1") <> "" then
     strSQLEND = strsQLEND & "OR field1 = " & request("field1") & " "
end if
if request("field2") <> "" then
      strSQLEND = strsQLEND & "OR field2 = " & request("field2") & " "
end if
strSQL = strSQL & strSQLEND
set rs = dbConn.Execute(strSQL)

That's good. Hadn't thought of that...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Programmatically Add Item to Validation Summary	jmurrayhead	.Net	0	March 20th, 2008 09:41 AM

The Following User Says Thank You to richyrich For This Useful Post:
jmurrayhead (May 27th, 2008)

Discuss "Loop / Summary help" in the ASP Development forum.

ASP Development - Learn coding practices and tips to get the best out of your Active Server Pages (ASP). The Classic ASP forum is for ASP/VBScript and ASP/JScript applications.