Discuss "Help with asp display / recordset (troubleshoot)" in the ASP Development forum.

mehere · #21 June 27th, 2008, 02:35 PM

yeah ... that's what i was afraid of ... because you need to add values before writing to the page, you would have this issue.

Rebelle · #22 June 27th, 2008, 02:51 PM

all is grrrrreat! you did it mehere!!!! Thanks!

I just put this after the loop to get teh last line, just like you taught me.

Code:

Response.Write("<tr><td colwidth=""150""><b>" & strLastTools & "</b></a></td>")
		Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ1 & "</b></td>")
			If strPrevQ1Shipped <> 0 then
				Response.Write("<td align=center colwidth=""50""><A href="http://www.developerbarn.com/asp-development/"//server/test/ShipResults.asp?ToolID=" & strPrevID &"""><b>" & strPrevQ1Shipped & "</b></a></td>")
			Else
				Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ1Shipped & "</b></td>")
			End If
	
		Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ2 & "</b></td>")
		Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ3 & "</b></td>")
		Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ4 & "</b></td></tr>")

Rebelle · #23 June 30th, 2008, 09:11 AM

Ok, I didn't think it was going to be a problem just to add the other data to my history and do a sumShipped for those months like sumShipped sums up my first 3 months using the sum(Case when [MoID] between 2 and 4 then [RolloutShipped] end) as SumShipped...sum(case when [MoID] between 5 and 7 then [RolloutShipped] end) as SumShipped2....but having problems on my asp side again.

At least I think it's on this side, I took my sql statement on my asp page and put it in sql analyzer for toolID = 180 ...I've attached screen results.

When I added SumShipped2 to everything I get NULL results for my 1QShipped and 2QShipped columns in asp.

Any clue why I get NULL on everything....my ToolID 180 should have "5" in 1QShipped and "2" in 2QShipped col.

Thanks again for your help with this....

mehere · #24 June 30th, 2008, 09:43 AM

what is your SQL statement?

Rebelle · #25 June 30th, 2008, 09:50 AM

Thanks Mehere,

Here is the SQL statement:

Code:

Select ToolID,Tools,CurrentMoYr,OnOrder1,OnOrder2,OnOrder3,OnOrder4,MoID,Sum(case when [MoID] between 2 and 4 then (IsNull([RolloutShipped],0)) end) as SumShipped,Sum(case when [MoID] between 5 and 7 then (IsNull([RolloutShipped],0)) end) as SumShipped2 from vwHistory where 1=1 Group By ToolID,Tools,CurrentMoYr,OnOrder1,OnOrder2,OnOrder3,OnOrder4,MoID,SumShipped,SumShipped2 Order By " & sOrderBy & " " & sOrderByDir

The attachment from previous results only shows for MoID 2, 3, 4, 5 because those are the only months I have in there now.....as months go on, I will be adding each months data.

And here is the asp, just incorporated the SumShipped2 for 2QShipped.

Code:

strLastTools = rs("Tools")
strPrevQ1 = rs("OnOrder1")
strPrevQ1Shipped = rs("SumShipped")
strPrevQ2 = rs("OnOrder2")
strPrevQ2Shipped = rs("SumShipped2")
strPrevQ3 = rs("OnOrder3") 
strPrevQ4 = rs("OnOrder4")
strPrevToolID = rs("ToolID")

	do while not rs.EOF
		if i > iStopRec then
			exit do
		end if

	strTools = rs("Tools")
	strQ1 = rs("OnOrder1")
	
	if strTools <> strLastTools then
		strQ1Shipped = rs("SumShipped")
		strQ2Shipped = rs("SumShipped2")
	else
		strQ1Shipped = strQ1Shipped + rs("SumShipped")
		strQ2Shipped = strQ2Shipped + rs("SumShipped2")
	end if

	strQ2 = rs("OnOrder2")
	strQ3 = rs("OnOrder3")
	strQ4 = rs("OnOrder4")
	
	strToolID = rs("ToolID")
	
	If strTools<>strLastTools and strLastTools <> "" Then
	
		Response.Write("<tr><td colwidth=""150""><b>" & strLastTools & "</b></a></td>")
			Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ1 & "</b></td>")
		strPrevQ1 = 0 

	If strPrevQ1Shipped <> 0 then
			Response.Write("<td align=center colwidth=""50""><A href=""//testserver/test/Results.asp?ToolID=" & strPrevToolID &"""><b>" & strPrevQ1Shipped & "</b></a></td>")
		strPrevQ1Shipped = 0
	Else
			Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ1Shipped & "</b></td>")
		strPrevQ1Shipped = 0
	End If
	

			Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ2 & "</b></td>")
		strPrevQ2 = 0

		If strPrevQ2Shipped <> 0 then
			Response.Write("<td align=center colwidth=""50""><A href=""//testserver/test/Results.asp?ToolID=" & strPrevToolID &"""><b>" & strPrevQ2Shipped & "</b></a></td>")
		strPrevQ2Shipped = 0
	Else
			Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ2Shipped & "</b></td>")
		strPrevQ2Shipped = 0
	End If

			Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ3 & "</b></td>")
		strPrevQ3 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strPrevQ4 & "</b></td></tr>")
		strPrevQ4 = 0

	End If
	strLastTools = strTools
	strPrevQ1 = strQ1
	strPrevQ1Shipped = strQ1Shipped
	strPrevQ2 = strQ2
	strPrevQ2Shipped = strQ2Shipped
	strPrevQ3 = strQ3
	strPrevQ4 = strQ4
	strPrevToolID = strToolID

mehere · #26 June 30th, 2008, 10:33 AM

the items at the top of your code are wrong. i'm confused by them. they should be at the end of your loop and they are, so not sure what this is doing.

Code:

strLastTools = rs("Tools")
strPrevQ1 = rs("OnOrder1")
strPrevQ1Shipped = rs("SumShipped")
strPrevQ2 = rs("OnOrder2")
strPrevQ2Shipped = rs("SumShipped2")
strPrevQ3 = rs("OnOrder3") 
strPrevQ4 = rs("OnOrder4")
strPrevToolID = rs("ToolID")

go back to the code you were using when you were only showing the sum of q1Shipped and just add the q2Shipped info.

also, since you are getting null's back, do the following. add this function to your code

Code:

Function IIF(cond, valTrue, valFalse)
   If cond Then
	  IIF = valTrue
   Else  
	  IIF = valFalse
   End If
End Function

then on this, add the bolded.

Code:

if strTools <> strLastTools then
		strQ1Shipped = IIF(isNull(rs("SumShipped")),0,rs("SumShipped"))
		strQ2Shipped = IIF(isNull(rs("SumShipped2")),0,rs("SumShipped2"))
	else
		strQ1Shipped = strQ1Shipped + IIF(isNull(rs("SumShipped")),0,rs("SumShipped"))
		strQ2Shipped = strQ2Shipped + IIF(isNull(rs("SumShipped2")),0,rs("SumShipped2"))
	end if

Rebelle · #27 June 30th, 2008, 11:09 AM

soooooo awesome! Thanks Mehere....that's what I needed....made the changes and burped it and it looks good now!!!!

mehere · #28 June 30th, 2008, 11:19 AM

good ... glad it's working for you. that IIF function was written by Shadow at the other place ... i've been using it ever since. it's a keeper. you could basically use it for most, if not all of your If/Then statements.

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

The Following User Says Thank You to mehere For This Useful Post:
Rebelle (June 30th, 2008)

Discuss "Help with asp display / recordset (troubleshoot)" in the ASP Development forum.

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