Compare String and Totals 1

[Corneliu]

Can someone help me with this please. I am trying to add totals from a category, but yet it wont work properly. I have tried several ways to make it work and every time I get errors.
What I have now is this, in one version:

Category Moves 20
Category Moves 20
Category Lab 50
Category Lab 50

What I would like is this:

Category Moves 40
Category Lab 100

I have 2 codes for it. One of them gives me the information above, the first output and the other gives me "Error Type:
(0x80020009)
Exception occurred.
/Reports/Daily/Reports/UserReport.asp, line 212"

Can someone please HELP me with this? Been working on it for 3 days and still no luck.

SQL Query:
<%
UserID = TRIM( Request.QueryString( &quot;UserID&quot )
WkID = TRIM( Request.QueryString( &quot;WkID&quot )

Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
SqlString = &quot;SELECT Main.Site, Main.Technician, Main.TSpent, Main.Category, Main.Date1, Main.Department, Main.DeptWork, Main.Week, Department.Department as Department_Name, Technicians.Technician as Tech_Name, Category.Category as Category_Name &quot;
SqlString = SqlString & &quot; FROM ((((Main INNER JOIN Technicians ON Main.Technician = Technicians.TechID) INNER JOIN Site ON Main.Site = Site.SiteID) INNER JOIN Department ON Main.Department = Department.DeptID) INNER JOIN Category ON Main.Category = Category.CatID) INNER JOIN Week ON Main.Week = Week.WeekID&quot;
SqlString = SqlString & &quot; WHERE Technicians.TechID = &quot; & UserID & &quot; and Week.WeekID = &quot; & WkID
SqlString = SqlString & &quot; Order by Main.Category &quot;
SET RS = objConn.Execute( sqlString )

%>

Table Code:
<td width=&quot;287&quot; bgcolor=&quot;<%=bgColor%>&quot;><div align=&quot;right&quot;><em><font color=&quot;#000000&quot; size=&quot;2&quot;>
<%While Not RS.EOF
CatName = RS(&quot;Category&quot

Response.Write(Category)
Spent = 0
Total = 0
While CatName = CatName
Total = Total + RS(&quot;TSpent&quot
RS.MoveNext
Wend

%> Hr(s)</font></em></div></td>
</tr>
<% Wend
%>

Line 212 is ====Total = Total + RS(&quot;TSpent&quot====

Any help on this I would REALLY appreciate it. Bascially just want the total time spent on each category, but output it just ones with the total instead of # of the category records it exists. Thank Again for your HELP.

 

[TomasCrossroads]

a few things that might help if you intend to sum the hours in your asp code:

- &quot;While CatName = CatName&quot;
This will always be through and you'll stay in the loop forever.
I guess you want to try this:
While CatName = RS(&quot;Category&quot

- add an extra RS.MoveNext outside the inner while loop. Else, when you reach a new catname in the recordset you would be in an endless loop again.

- make sure RS(&quot;TSpent&quot is an integer value. You can use Int(RS(&quot;TSpent&quot) or CInt(RS(&quot;TSpent&quot) instead.


But if possible try to sum the hours per category in the sql query.
I'm not sure about your database structure but this might work:
SqlString = &quot;SELECT Main.Site, Main.Technician, SUM(Main.TSpent), Main.Category, Main.Date1, Main.Department, Main.DeptWork, Main.Week, Department.Department as Department_Name, Technicians.Technician as Tech_Name, Category.Category as Category_Name &quot;
SqlString = SqlString & &quot; FROM ((((Main INNER JOIN Technicians ON Main.Technician = Technicians.TechID) INNER JOIN Site ON Main.Site = Site.SiteID) INNER JOIN Department ON Main.Department = Department.DeptID) INNER JOIN Category ON Main.Category = Category.CatID) INNER JOIN Week ON Main.Week = Week.WeekID&quot;
SqlString = SqlString & &quot; WHERE Technicians.TechID = &quot; & UserID & &quot; and Week.WeekID = &quot; & WkID
SqlString = SqlString & &quot; GROUP BY Main.Site, Main.Technician, Main.Category, Main.Date1, Main.Department, Main.DeptWork, Main.Week, Department.Department, Technicians.Technician, Category.Category Order by Main.Category &quot;

 

[Corneliu]

This is what I have now, and it does not work. It gives me 0 instead of the numbers.
I did not make changes to the SQL statement. I cannot sum by category there because I would like the list to be dynamic. I want to just give the UserID, Week # and than give me the total for that user depending on the category. All my tables are numbers and set as Int.

<%While Not RS.EOF %>
<table width=&quot;100%&quot; border=&quot;1&quot; cellpadding=&quot;0&quot; cellspacing=&quot;2&quot; bordercolor=&quot;#000000&quot; class=&quot;Listing&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;204&quot;><div align=&quot;left&quot;><em></em></div></td>
<td width=&quot;487&quot;><div align=&quot;center&quot;><em><font color=&quot;#000000&quot; size=&quot;2&quot;><strong></strong>
</font></em></div></td>
<td width=&quot;287&quot;><div align=&quot;right&quot;><em><font color=&quot;#000000&quot; size=&quot;2&quot;>
<%
Spent = 0
Total = 0
While CatName = RS(&quot;Category&quot
Total = Total + RS(&quot;TSpent&quot
RS.MoveNext
Wend
Response.Write(Total)
%> Hr(s)</font></em></div></td>
</tr>
</table>
<%
RS.MoveNext
Wend
%>
<tr>

Again, thank you for your help. I appreciate it.

 

[Tarwn]

You need to reset catname before you go into the loop again so it will be equivalent to your first catagory the first time, and each subsequent catagory after that. My guess is that it is skipping your counter loop all together. Also I would add an EOF check to this loop just in case:

<%  
        Spent = 0
        Total = 0
        CatName = RS(&quot;Catagory&quot;)
        While CatName = RS(&quot;Category&quot;) And Not RS.EOF
        Total = Total + RS(&quot;TSpent&quot;)
        RS.MoveNext
        Wend
         Response.Write(Total)
        %>

It appears this was in your original code but somehow disappeared (the stting of CatName).

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[TomasCrossroads]

sometimes it helps to use CStr() when comparing strings.

while CStr(catname) = CStr(RS(&quot;Category&quot)

but you'll have to be sure RS(&quot;Category&quot is never Null...