Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 12th, 2010, 05:19 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default Displaying Zero Count

So I have a checklist I've created for an app that we use, and it displays (among other things) open events, and action-errors counts. In one instance, the hyperlink and its associated count doesn't display when the count is 0. However, the other instance displays a zero count just fine. I've looked at this for a while now and my eyes are all blurry, and I can't see the obvious error.

Here's the one that works:

Code:
 
set rsqdbb = conn.Execute ("Select count (*) as scripts_no_action_total " _
& "from Job J " _
& "left join ActionTmpl A on A.OwnerJobID = J.JobID   " _
& "left join Object O on O.ObjID = J.MachineObjID  " _
& "left Join ViewHierarchy vh  " _
& "On o.ObjID = vh.ObjID  " _
& "Join object o2  " _
& "ON vh.parentobjID=o2.ObjID  " _
& "WHERE o.typeid = 2  " _
& "and J.ParentJobID is not null     " _
& "and A.ActionName is NULL    " _
& "and J.Status &0xFF =1    " _
& "and J.KSName not like '%analy%'    " _
& "and J.KSName not like '%systemuptime%'    " _
& "and J.KSName not like '%top%'    " _
& "and J.KSName not like '%discovery%'  " _
& " and (o2.Name like '%storage%' or o2.Name like '%comm%') ")
 
<%do while not rsqdbb.EOF%></do>
<a href="http://show_scripts_no_actions_storage_det0.asp" target="content">Show Jobs With No Actions
<font color=maroon>&nbsp;(<%response.write rsqdbb("scripts_no_action_Total")%>)</font><br />
<%rsqdbb.MoveNext%>
<%loop%>
And here's the one that doesn't display the hyperlink at all:

Code:
 
set rsqdbte = conn.Execute ("SELECT count (distinct EventMSG) as Total_Events" _
& " FROM Object o Join ViewHierarchy vh " _
& " On o.ObjID = vh.ObjID " _
& " Join object o2 " _
& " ON vh.parentobjID=o2.ObjID " _
& " Join event " _
& " On event.machineobjid = o.objID " _
& " WHERE o.typeid IN (2,16) " _
& " and (o2.Name like '%storage%' or o2.Name like '%comm%') " _
& " AND vh.ViewID=0 " _
& " and event.status = 0 " _
& " and eventMSG not like '%action%' " _
& " and eventMSG not like '%boot%' " _
& " and eventmsg NOT LIKE '%DISCOVERY%' " _
& " and eventmsg not like '%normal%' " _
& " and eventmsg not like '%state change%' " _
& " group by o.typeid ")
 
<%do while not rsqdbte.EOF%></do>
<a href="http://show_storage_events_det0.asp" target="content">Show Open Events
<font color=maroon>&nbsp;(<%response.write rsqdbte("Total_Events")%>)</font><br />
<%rsqdbte.MoveNext%>
<%loop%>
Thanks for any help!

Dale
 
Old October 12th, 2010, 06:44 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

DEBUG DEBUG DEBUG...

What happens when you execute that query from SQL Server management console (or QA if you are still using SQL Server 2000)??

This is PURELY a guess. But I think it might be because you are indeed doing
Code:
COUNT(DISTINCT eventmsg)
If there are *NO* eventmsg instances found, then you are essentially asking it to do
Code:
COUNT(DISTINCT null)
and I am just *guessing* that a COUNT(null) is itself null, so you get no records.

I'll bet you could fix this easily, if I'm right. Just do:
Code:
SELECT ISNULL( count(distinct EventMSG), 0 ) as Total_Events
 
Old October 13th, 2010, 08:53 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Old Pendant,

I should have clarified my post. You're right. When the count is empty (and it is sometimes), I'd like it to display (for example) Show Open Events (0). And it does this in the first bit of code, but not the second. For some odd reason, when the count is zero, in the second bit of code, the entire line does not get displayed.

Does this help?

Dale
 
Old October 13th, 2010, 02:41 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You just repeated what you said in first post. My answer is the same.

Again, if the COUNT(DISTINCT xxx) is returning a NULL, then you will *NOT* get ANY records and your "do while not rsqdbte.EOF" will indeed not loop at all. No records, no results.

Of course, there is an easy way to fix this in the ASP code. Just replace this code:
Code:
<%do while not rsqdbte.EOF%>
</do>
   <a href="http://show_storage_events_det0.asp" target="content">Show Open Events 
    <font color=maroon>&nbsp;(<%response.write rsqdbte("Total_Events")%>)</font>
    <br />
<%rsqdbte.MoveNext%> 
<%loop%>
with this:
Code:
<%
... make the query ...
total = 0
If Not rsqdbte.EOF Then total = rsqdbte(0)
%>
<a href="http://show_storage_events_det0.asp" target="content">Show Open Events 
    <font color=maroon>&nbsp;(<%=total%>)</font>
    <br />
...
 
Old October 18th, 2010, 09:06 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thanks OldPendant! You saved the day yet again.

Dale




Similar Threads
Thread Thread Starter Forum Replies Last Post
XSL: Count = Count + 1 elayaraja.s XSLT 3 July 18th, 2008 03:21 AM
is there any in built function to count page count g.tamilselvan MySQL 1 February 15th, 2006 07:43 AM
Need Count Help ~Bean~ SQL Language 11 August 9th, 2005 11:22 PM
Count, sum, count a value, return records CongoGrey Access 1 April 18th, 2005 02:25 PM
Displaying New record Count tycoon Access ASP 1 February 5th, 2004 10:33 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.