Dynamic Record Listing 1

[Corneliu]

I wanted to know if someone could help me with this issue. My current records listing works great, BUT, since the page and categories changes so much, I wanted to know if there is another way to list my records dynamically. I dont want to have static categories, since they change many times and more get added, and would like as little maintenance as possible in this page, since it will be the main Time Tracking Page for the entire Department. I am building quite a few of them, but just need a little help to get me started. I tried Arrays and other codes that I have found here, asked many others and still no luck yet.
Any help, I mean any help I would greatly appreciate.

Here is what I have and works:
There is one main page where the records get listed from and the actual names of the categories and user and departments comes from other tables as IDs. So the main table has just numbers.

SQL Query:
<!--#include file=&quot;../../../Connections/Daily.asp&quot; -->
<!-- #include file=&quot;../../../Connections/adovbs.inc&quot; -->

<%
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 Category.CatID, Main.Category &quot;
SET RS = objConn.Execute( sqlString )

%>

The Cases:
<%
Dim Category1, Category2, Category3, Category4, Category5, Category6, Category7, Category8, Category9, Category10, Category11
Dim Category12, Category13, Category14, Category15, Category16, Category17, Category18, Category19, Category20, Total
Category1 = 0
Category2 = 0
Category3 = 0
Category4 = 0
Category5 = 0
Category6 = 0
Category7 = 0
Category8 = 0
Category9 = 0
Category10 = 0
Category11 = 0
Category12 = 0
Category13 = 0
Category14 = 0
Category15 = 0
Category16 = 0
Category17 = 0
Category18 = 0
Category19 = 0
Category20 = 0
Total = 0

while not RS.EOF

Cat_Name = RS(&quot;Category_Name&quot
Cat = RS(&quot;Category&quot
Spent = RS(&quot;TSpent&quot
Tech = RS(&quot;Tech_Name&quot

Select Case Cat

'Select Category
Case &quot;1&quot;
Category1 = Spent /60 + Category1
Case &quot;2&quot;
Category2 = Spent /60 + Category2
Case &quot;3&quot;
Category3 = Spent /60 + Category3
Case &quot;4&quot;
Category4 = Spent /60 + Category4
Case &quot;5&quot;
Category5 = Spent /60 + Category5
Case &quot;6&quot;
Category6 = Spent /60 + Category6
Case &quot;7&quot;
Category7 = Spent /60 + Category7
Case &quot;8&quot;
Category8 = Spent /60 + Category8
Case &quot;9&quot;
Category9 = Spent /60 + Category9
Case &quot;10&quot;
Category10 = Spent /60 + Category10
Case &quot;11&quot;
Category11 = Spent /60 + Category11
Case &quot;12&quot;
Category12 = Spent /60 + Category12
Case &quot;13&quot;
Category13 = Spent /60 + Category13
Case &quot;14&quot;
Category14 = Spent /60 + Category14
Case &quot;15&quot;
Category15 = Spent /60 + Category15
Case &quot;16&quot;
Category16 = Spent /60 + Category16
Case &quot;17&quot;
Category17 = Spent /60 + Category17
Case &quot;18&quot;
Category18 = Spent /60 + Category18
Case &quot;19&quot;
Category19 = Spent /60 + Category19
Case &quot;20&quot;
Category20 = Spent /60 + Category20
End select

RS.MoveNext
WEND

Total = Category1 + Category2 + Category3 + Category4 + Category5 + Category6 + Category7 + Category8 + Category9 + Category10 + Category11 + Category12 + Category13 + Category14 + Category15 + Category16 + Category17 + Category18 + Category19 + Category20

%>

The Listing:
<table width=&quot;100%&quot; border=&quot;1&quot; cellpadding=&quot;0&quot; cellspacing=&quot;2&quot; bordercolor=&quot;#000000&quot; class=&quot;Graph&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;412&quot;>Category:</td>
<td width=&quot;168&quot;> <div align=&quot;right&quot;>Moves </div></td>
<td width=&quot;398&quot;><div align=&quot;right&quot;>
<%
If Category1 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category1) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Tickets</div></td>
<td><div align=&quot;right&quot;>
<%
If Category2 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category2) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td> <div align=&quot;right&quot;>Internal Project</div></td>
<td><div align=&quot;right&quot;>
<%
If Category3 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category3) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td> <div align=&quot;right&quot;>External Project </div></td>
<td><div align=&quot;right&quot;>
<%
If Category4 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category4) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Image Updates </div></td>
<td><div align=&quot;right&quot;>
<%
If Category5 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category5) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Web Updates </div></td>
<td><div align=&quot;right&quot;>
<%
If Category6 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category6) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Email </div></td>
<td><div align=&quot;right&quot;>
<%
If Category7 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category7) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Lab Work </div></td>
<td><div align=&quot;right&quot;>
<%
If Category8 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category8) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Web Design </div></td>
<td><div align=&quot;right&quot;>
<%
If Category9 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category9) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Reports </div></td>
<td><div align=&quot;right&quot;>
<%
If Category10 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category10) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Misc Work </div></td>
<td><div align=&quot;right&quot;>
<%
If Category11 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category11) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Research </div></td>
<td><div align=&quot;right&quot;>
<%
If Category12 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category12) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Clean Up </div></td>
<td><div align=&quot;right&quot;>
<%
If Category13 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category13) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Design </div></td>
<td><div align=&quot;right&quot;>
<%
If Category14 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category14) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Data Entry </div></td>
<td><div align=&quot;right&quot;>
<%
If Category15 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category15) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Collections Efforts</div></td>
<td> <div align=&quot;right&quot;>
<%
If Category16 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category16) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Collections OT</div></td>
<td> <div align=&quot;right&quot;>
<%
If Category17 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category17) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Executive Work</div></td>
<td> <div align=&quot;right&quot;>
<%
If Category18 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category18) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Training</div></td>
<td> <div align=&quot;right&quot;>
<%
If Category19 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category19) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
<tr>
<td>Category:</td>
<td><div align=&quot;right&quot;>Administration</div></td>
<td> <div align=&quot;right&quot;>
<%
If Category20 = 0 then
Response.Write (&quot;No Time Recorded&quot
Else
Response.Write (Category20) & &quot;Hrs&quot;
End If
%>
</div></td>
</tr>
</table>

I have tried several books and forums and online FAQs and yet still no luck. Been trying this for about 2 weeks now and just cant come up with another solution besides Static names and Calculations. PLEASE PLEASE someone give me a hand here. I would really appreciate this.

Again Thank You For Your Time.

 

[Tarwn]

You should look into using the Sum function in your sql statement, specifically in reference to the TSpent field. If you set it up correctly this will give you your records already totalled along with the other information you are trying to collect. This way you can output directly from the recordset dynamically without having to worry about how many catagories you have or totalling each catagory like you are above.

-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...)

 

[Corneliu]

I did what you have said before but still without luck, or maybe I did not do it correctly. Could you please help me forward more, or let me know any link or web page that could give me more info on what I am trying to do, PLEASE.

Here is what I did, as you said before, and I get an error now. I am using WIN 2K Web Server with SQL Database.

Now the SQL statement is:
<%
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, 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; Order by Category.CatID, Main.Category &quot;
SET RS = objConn.Execute( sqlString )

%>

As you can see, I did the SUM as you said, but now I get this. Any clues as to what could be:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'Main.Site' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
/Reports/Daily/Reports/UserReport.asp, line 14

All records within the Main Table are numbers and set to Int within the SQL Database.
I really appreciate your help. Been working on this for a long time.

 

[Corneliu]

My fault. I forgot to Group the list, but I get almost the same error back.

SQL Statement:
<%
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, 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 Category.CatID, Main.Category &quot;
SET RS = objConn.Execute( sqlString )

%>

Error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'Main.Site' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
/Reports/Daily/Reports/UserReport.asp, line 14

 

[Tarwn]

When using the sum function, any other fields you include in the select statement will need to be placed in the group by portion as well, basically what this will do is give you the sum of all the values for each instance where the other fields are the same, ie you r records will return with sums for each distinct set of records that you select. You may want to remove the date and/or week from your select unless you want the total on a given day.

sql:

SqlString = "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 "
SqlString = SqlString & " 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"
SqlString = SqlString & " WHERE Technicians.TechID = " & UserID & " and Week.WeekID = " & WkID
SqlString = SqlString & " Group by Category.CatID, Main.Category, Main.Site, Main.Technician,Main.Date1, Main.Department, Main.DeptWork, Main.Week,Department.Department"

Another option if you only need a single date but the totals in a weekly format is to use a min() or max() function on the date (or a count to get the number of instances). This will give you the total for the week as well as either the minimum (starting), maximum (ending), or count (number of entries for that sum).

'note the min function to grab the first date entered for this week and the max function to grab the last day for the entries
SqlString = "SELECT Main.Site, Main.Technician, SUM (Main.TSpent), Main.Category, MIN(Main.Date1), MAX(Main.Date1), Main.Department, Main.DeptWork, Main.Week, Department.Department as Department_Name, Technicians.Technician as Tech_Name, Category.Category as Category_Name "
SqlString = SqlString & " 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"
SqlString = SqlString & " WHERE Technicians.TechID = " & UserID & " and Week.WeekID = " & WkID
SqlString = SqlString & " Group by Category.CatID, Main.Category, Main.Site, Main.Technician, Main.Department, Main.DeptWork, Main.Week,Department.Department"

I hope that helps more than it hinders.
Your records will now be returned like so:
Site, Technician, Sum of Time, Catagory, Date of 1st entry, date of most recent entry, Department, DeptWork, the week, the dept name, tech name, catagory (again as catagory name)

-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...)

 

[Corneliu]

This is what I have now. There are no errors on the Query, but one more question please:

Do I list the information in a loop, or I have to do more calculations before I can go further?

I tried a loop but it gives me an error that RS(&quot;TSpent&quot does not exist. Had to remove some of the fields since I dont want them anymore. I want it to be as clean as possible, so removed Site, Dept, etc. Just left the info that I REALLY NEED.

SQL:
<%
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.Technician, SUM(Main.TSpent), Main.Category, Main.Week, 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 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.Category, Category.Category, Main.Technician, Technicians.Technician, Main.Week &quot;
SET RS = objConn.Execute( sqlString )

%>

Listing:
<% 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;Graph&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;412&quot;>Category:</td>
<td width=&quot;168&quot;> <div align=&quot;right&quot;><%=RS(&quot;Category_Name&quot

%></div></td>
<td width=&quot;398&quot;><div align=&quot;right&quot;><%=RS(&quot;TSpent&quot

%> </div></td>
</tr>
</table>
<% RS.MoveNext
WEND
%>

AGAIN, THANK YOUR VERY MUCH FOR YOUR HELP.

 

[Corneliu]

Got It. Tarwn, I THANK YOU VERY MUCH FOR YOUR HELP.
This really helped me a LOT. THANK YOU.

I forgot that you have to state a name for the time spent as a variable, so here it is:

<%
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.Technician, SUM(Main.TSpent) as TimeSpent, Main.Category, Main.Week, 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 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.Category, Category.Category, Main.Technician, Technicians.Technician, Main.Week &quot;
SET RS = objConn.Execute( sqlString )

%>

The Listing:

<% 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;Graph&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;412&quot;>Category:</td>
<td width=&quot;168&quot;> <div align=&quot;right&quot;><%=RS(&quot;Category_Name&quot

%></div></td>
<td width=&quot;398&quot;><div align=&quot;right&quot;><%=RS(&quot;TimeSpent&quot

%> </div></td>
</tr>
</table>
<% RS.MoveNext
WEND
%>

Again, Tarwn THANK YOU VERY MUCH. This really helped me.

 

[Tarwn]

Glad to be of help

-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...)