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

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

ok...

here it is:

Code:

SELECT     dbo.tblTools.Tools, dbo.tblTools.OnOrder1, dbo.tblTools.OnOrder2, dbo.tblTools.OnOrder3, dbo.tblTools.OnOrder4, 
                      dbo.tblHistory.CurrentMoYr, SUM(dbo.tblHistory.RolloutShipped) AS SumShipped, dbo.tblHistory.RolloutShipped, 
                      dbo.tblHistory.DistrictID, dbo.tblHistory.RegionID, dbo.tblTools.MeetingID, dbo.tblRegion.RegionName, 
                      dbo.tblDistricts.District_Location, dbo.tblHistory.MoID, dbo.tblHistory.ToolID
FROM         dbo.tblHistory INNER JOIN
                      dbo.tblRegion ON dbo.tblHistory.RegionID = dbo.tblRegion.RegionID INNER JOIN
                      dbo.tblDistricts ON dbo.tblHistory.DistrictID = dbo.tblDistricts.DistrictID LEFT OUTER JOIN
                      dbo.tblTools ON dbo.tblHistory.ToolID = dbo.tblTools.ToolID
GROUP BY dbo.tblTools.Tools, dbo.tblTools.OnOrder1, dbo.tblTools.OnOrder2, dbo.tblTools.OnOrder3, dbo.tblTools.OnOrder4, 
                      dbo.tblHistory.CurrentMoYr, dbo.tblHistory.RolloutShipped, dbo.tblHistory.DistrictID, 
                      dbo.tblHistory.RegionID, dbo.tblTools.MeetingID, dbo.tblRegion.RegionName, dbo.tblDistricts.District_Location, 
                      dbo.tblHistory.MoID, dbo.tblHistory.ToolID
HAVING      (dbo.tblTools.MeetingID = 1) OR
                      (dbo.tblTools.MeetingID = 2)

jmurrayhead · #12 June 27th, 2008, 11:01 AM

If Query Analyzer returns the correct data, then you're right, it's probably an issue with the ASP logic. Have you checked your page source after the page is rendered? That might give a hint as to where the logic is wrong.

Rebelle · #13 June 27th, 2008, 11:06 AM

Quote:

Originally Posted by jmurrayhead

If Query Analyzer returns the correct data, then you're right, it's probably an issue with the ASP logic. Have you checked your page source after the page is rendered? That might give a hint as to where the logic is wrong.

o, i totally forgot about checking that out....will check now. Thanks Jim!

i don't understand why it shows the correct toolID just not the right shipped qty value......if my first record strQ1Shipped value is NULL, should the below be an issue? remember i'm handling the null in the asp side sql statement to make 0. See anything weird?

Code:

If strQ1Shipped <> 0 then
				Response.Write("<td align=center colwidth=""50""><A href="//server/test/ShipResults.asp?ToolID=" & strToolID &"""><b>" & strQ1Shipped & "</b></a></td>")
				strQ1Shipped = 0
			Else
				Response.Write("<td align=center colwidth=""50""><b>" & strQ1Shipped & "</b></td>")
				strQ1Shipped = 0
			End If

mehere · #14 June 27th, 2008, 11:55 AM

can you show me what the query returns in the query analyzer? after i see that, i'll take a look at your ASP code and see where the issue might be.

Rebelle · #15 June 27th, 2008, 12:26 PM

okie here is it mehere....

this is taking the sql statement i have on the asp page from the sql vw.

sorry it's the most i could fit on the screen, the 3rd tool listed has more records that add up to (5)...but what you see on attachment is all i could fit in....

but my asp looks like below, it's starting off with (0) on Q1Shipped and pushed the results down by 1, making that (5) show up on tool 4.

Code:

Tools | Q1-08 |Q1 Shipped |Q2-08| Q3-08 |Q4-08 
tool1 | 0        |0               |0      |0         | 0 
tool2 | 5        | 0              |5      |4         | 4 
tool3 |10        | 0             |4       |4         | 4 
tool4 | 6         |5      |4       |4        | 4

Thanks!

mehere · #16 June 27th, 2008, 12:38 PM

which post has your code for the display? sorry ... i'm just working between 2 desks and i don't have time to really look for it. or if you can post it again, that would be good.

jmurrayhead · #17 June 27th, 2008, 12:42 PM

Quote:

Originally Posted by mehere

which post has your code for the display? sorry ... i'm just working between 2 desks and i don't have time to really look for it. or if you can post it again, that would be good.

http://www.developerbarn.com/2128-post3.html

mehere · #18 June 27th, 2008, 01:03 PM

okie dokie ... change this line:

Code:

If strTools<>strLastTools Then

to

Code:

If strTools<>strLastTools and strLastTools <> "" Then

what's happening is strLastTools is blank when you start, so it's writing your first record, at that point strQ1Shipped is 0 ... then when it gets to your second record, it's using the total of strQ1Shipped from your previous record which is why 5 is showing for tool4 and not tool3.

however, because you need to read the records and to get your total before posting, you will also need to get prev values for all your other records as well.

so, in essence you need something like this:

Code:

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")
	else
		strQ1Shipped = strQ1Shipped + rs("SumShipped")
	end if
	strQ2 = rs("OnOrder2")
	strQ3 = rs("OnOrder3")
	strQ4 = rs("OnOrder4")
	
	strToolID = rs("ToolID")

	
	If strTools<>strLastTools and strLastTools <> "" or rs.EOF Then'I'm not sure if the rs.EOF will work.  Just a thought.
	
		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>")
		


	End If
	strLastTools = strTools
	strPrevQ1Shipped = strQ1Shipped
	strPrevQ2 = strQ2
	strPrevQ3 = strQ3
	strPrevQ4 = strQ4
	strPrevID = strToolID

%>


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

what this is doing, is when the strTool changes it will post the previous record. problem with this is, you may lose the last record. i'm thinking you may have to do this in a stored proc that will create a table to get your data correctly so that when you display it, there is no issue.

give the above code a shot. if the rs.EOF fails in the IF/Then statement, we're going to have to look at a stored proc to do this.

mehere · #19 June 27th, 2008, 01:16 PM

if this doesn't work ... and i'm not too confident it will, if you could provide me with your table layout and sample data, i could work on a query or stored proc that will get you the data you need.

Rebelle · #20 June 27th, 2008, 02:01 PM

Thanks Mehere,

Gave it a try and it's close but not yet lined up....
when I tried all the changes, it now does line up the q1Shipped column with the other qtr columns, but it got rid of tool1 so it now unaligned toolname with the other columns....

Code:

ex:  
tool2----------q1 for tool1 value------q1Ship for tool1 value----other cols.
tool3----------q1 for tool2 value------q1Ship for tool2 value----other cols.
tool4----------q1 for tool3 value------q1Ship for tool3 value----other cols.

will send table layout and sample data to ya.

Disregard the above, I had strTools displaying instead of strLastTools on the response.write line....it lines things up starting with tool1 but it's just not showing the last line (last tool with other columns). w00t!

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 27th, 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.