Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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 />
...
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:19 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.