| Author |
 Topic  |
|
JohnC
Junior Member
215 Posts |
 Posted - 13 January 2008 : 20:05:47
|
There's a lot of miscommunication here. Let's try this again. Forget everything I said above.
I want to add multiple instances of database table record counts on an ASP page. For example: I have "X" amount of video categories and "X" amount of videos in each category. I want to be able to display quantities for each category, for example: Video One (24), Video Two (12), etc. I want to be able to do it with the least amount of code as possible because there might be several hundred categories on one page that will display counts. What is the best way to accomplish this? |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 January 2008 : 08:27:00
|
I guess this should do it. Haven't tested it, though.
<%
Response.Write "<table width=""100%"">" & vbNewLine & _
"<tr>" & vbNewLine & _
"<td width=""50%"">Category</td>" & VbNewLine & _
"<td width=""50%"">Quantity</td>" & VbNewLine & _
"</tr>" & vbNewLine
Dim xConn
xConn = MM_connVid_STRING
Dim xSQL
xSQL = "SELECT Category, Count(*) As xTotal FROM Vids GROUP By Category
Dim xRS
Set xRS = Server.CreateObject("ADODB.RecordSet")
xRS.Open xSQL, xConn
While Not xRS.EOF
Response.Write "<tr><td>"
Response.Write xRS("Category") & "</td><td>" & Total: " & xRS("xTotal") & "</td></tr>"
xRs.MoveNext
Wend
Response.Write "</table>" & vbNewLine
xRS.Close
Set xRS = Nothing
xConn.Close
Set xConn = Nothing
%>
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
JohnC
Junior Member
215 Posts |
Posted - 14 January 2008 : 11:04:46
|
| Thanks for your reply, Rui. Though your suggested code may work, there's got to be a better way to write this code for my particular application. As I mentioned above, there can be several hundred categories on one page so the code you suggested will be written several hundred times on a page, which might make it load really slow. What I'm looking for is a way to "define" a common task--in this case, count records--and use just a small amount of code to "call-out" the procedure, like I tried to explain above in REPLY ID 372089. I have an existing application that was written in Ultradev that uses this type of "call-out" procedure (which you worked on for me a while ago). I’m assuming it's common for repetitive database type tasks. By the way, I'm using this code on an ASP page, not VBScript like Snitz, so I don’t think I need the double quotes. Again, I appreciate your help! |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 14 January 2008 : 11:15:20
|
| well, I have just read this thread in it;s entirity and to be honest JohnC, if rui's example does not give you what you wanted then I'm afraid you are not asking the right questions since rui's code will give exactly what you requested, there is no easier way to do it |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 14 January 2008 : 13:50:07
|
JohnC, Its also worth noting that ASP is a framework. Traditionally you would use either VBScript or JScript/JavaScript to work out the server-side processing. Snitz is an ASP application that uses VBScript as the language of choice.  |
|
|
|
JohnC
Junior Member
215 Posts |
Posted - 15 January 2008 : 11:27:39
|
| Well, I have an existing application that does exactly what I explained above so I know it can be done. My only problem is finding someone that knows how to do it. I appreciate everyone's responses. I will post back here when/if I learn how to do define and call-out db count tasks. |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 15 January 2008 : 12:22:11
|
I think the problem is that no one clearly understands what you're looking for. If you're looking for a hardcoded value that you want to get a count from, then you'll want to move the query code into a function.
<%
Function GetCount(CatName)
Dim xConn, xSQL, xRS
xConn = MM_connVid_STRING
xSQL = "SELECT Count(*) As xTotal FROM Vids Where UPPER(Category) = '" & UCase(CatName) & "'"
Set xRS = Server.CreateObject("ADODB.RecordSet")
xRS.Open xSQL, xConn
GetCount = xRS("xTotal")
xRS.Close : Set xRS = Nothing
xConn.Close : Set xConn = Nothing
End Function
%>
Usage
<td>Video</td><td><% =GetCount("video") %></td>
Now, if you're working off a dynamic query, you'd be better off adding the count onto your original query, which will pull the counts in when you make your original query, though as a caveat, you'll have to add the group by to the bottom of the query.
Example:
<%
Dim xConn, xSQL, xRS
objConn = MM_connVid_STRING
strSQL = "SELECT CategoryName, CatDescription, V.Count(*) As xTotal " & _
"FROM Category C, Vids V WHERE C.CatID = V.CatID " & _
"GROUP By CategoryName, CatDescription"
Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open strSQL, objConn
If objRS.EOF And objRS.BOF Then
Response.Write "No records found"
Else
Response.Write "<table width=""100%"">" & vbNewLine & _
"<tr>" & vbNewLine & _
" <td width=""50%"">Category</td>" & VbNewLine & _
" <td width=""50%"">Description</td>" & VbNewLine & _
" <td width=""50%"">Quantity</td>" & VbNewLine & _
"</tr>" & vbNewLine
While Not xRS.EOF
Response.Write "<tr>" & _
" <td>" & objRS("CategoryName") & "</td>" & _
"<td>" & objRS("CategoryName") & "</td>" & _
"<td>Total: " & objRS("xTotal") & "</td></tr>"
objRs.MoveNext
Wend
Response.Write "</table>" & vbNewLine
End If
objRS.Close : Set objRS = Nothing
objConn.Close : Set objConn = Nothing
%>
|
Dave Maxwell
Barbershop Harmony Freak |
Edited by - davemaxwell on 15 January 2008 12:24:48 |
|
|
|
JohnC
Junior Member
215 Posts |
Posted - 15 January 2008 : 21:55:45
|
| Sorry if I'm not explaining myself correctly guys. Being a newbie, I’m not sure of the terminology. It looks like you understand me though, davemaxwell. The first example is what I was looking for. Can the "Function" be written once on the page and "Usage" be written multiple times but changed to reflect the proper video category? |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 16 January 2008 : 04:44:36
|
If you're expecting to have hundreds of categories then Dave's second code snippet would serve you better as it only requires one database hit for the entire page whereas the first sample would require a database hit for each category which could have a drastic effect on the page's load time and your server's response times.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
JohnC
Junior Member
215 Posts |
Posted - 16 January 2008 : 11:30:42
|
Thanks for your response, Shag. So am I correct in assuming that either code example will have to be written on the page as many times as there are categories? So if I had 100 categories, I would need to write the code 100 times on the page because each category is different?
Here is the perfect example of what I'm looking to do:
http://groups.google.com/groups/dir?hl=en
|
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 16 January 2008 : 13:10:46
|
| If you retrieve the value in the original query, then the value would be part of the recordset. If you're dealing with dynamic values, then that's the way you'd want to go. Your query would be written once, and then the value would be accessible via the recordset. |
Dave Maxwell
Barbershop Harmony Freak |
|
|
|
JohnC
Junior Member
215 Posts |
Posted - 22 January 2008 : 11:11:10
|
Thanks again for your response, Dave. I'm not sure I understand it though. The page I'm working on is a static Web page and doesn't pull values from any other page. Is it possible to explain in layman's terms?  |
|
|
|
Topic |
|
Topic Locked
