473,224 Members | 1,391 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

display problem when query is empty!?

Hi,

I've got the following problem:

I want my ASP page to display a certain number (based on a COUNT query),
it works fine if the result is at least 1! If there are no records to be
counted (= the query is empty), I get errors.

What I've tried is this:

if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then
response.write "amount: 0"
else
response.write "amount: " & response.write
rsNP_MB_Site.Fields.Item("NP").Value
end if

But I keep getting an 'object required' error?

I've also tried with isnull(rsNP_MB_Site.Fields.Item("NP").Value) but
that didn't work out either.

Any help is greatly appreciated,

Jerome
Jul 19 '05 #1
5 2225
Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:
I've got the following problem:

I want my ASP page to display a certain number (based on a COUNT
query), it works fine if the result is at least 1! If there are no
records to be counted (= the query is empty), I get errors.
A reasonable ;-) correct SQL count() will return a valid 0 count
What I've tried is this:

if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then
if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then
is the same as just:
if rsNP_MB_Site.BOF or rsNB_MB_Site.EOF then
response.write "amount: 0"
else
response.write "amount: " & response.write
rsNP_MB_Site.Fields.Item("NP").Value
This is vbs nonsense, should be:

response.write "amount: " & rsNP_MB_Site.Fields.Item("NP").Value

but how can this "Item" be the result of a SQL count() ?
end if


I suggest you show your code and specify the db-engine and connection
type.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress,
but let us keep the discussions in the newsgroup)

Jul 19 '05 #2
Hi, sorry for my messed up coding ...

Here's the SQL code (accessing an Access DB):

SELECT Count(museebus_Site.IDUmeldung) AS NP
FROM museebus_Site GROUP BY museebus_Site.printed HAVING
(((museebus_Site.printed)=No));

What I want to do is display on a summary page how many records there
are which are still un-printed (hence the count).

But if there are no unprinted records, the query is empty instead of
having 0.

Thanks for any help.

Jerome
Evertjan. wrote:
Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:

I've got the following problem:

I want my ASP page to display a certain number (based on a COUNT
query), it works fine if the result is at least 1! If there are no
records to be counted (= the query is empty), I get errors.

A reasonable ;-) correct SQL count() will return a valid 0 count

What I've tried is this:

if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then

if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then
is the same as just:
if rsNP_MB_Site.BOF or rsNB_MB_Site.EOF then

response.write "amount: 0"
else
response.write "amount: " & response.write
rsNP_MB_Site.Fields.Item("NP").Value

This is vbs nonsense, should be:

response.write "amount: " & rsNP_MB_Site.Fields.Item("NP").Value

but how can this "Item" be the result of a SQL count() ?

end if

I suggest you show your code and specify the db-engine and connection
type.

Jul 19 '05 #3
Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:
Hi, sorry for my messed up coding ...

Here's the SQL code (accessing an Access DB):

SELECT Count(museebus_Site.IDUmeldung) AS NP
FROM museebus_Site GROUP BY museebus_Site.printed HAVING
(((museebus_Site.printed)=No));

What I want to do is display on a summary page how many records there
are which are still un-printed (hence the count).


Why not simply [something like/not tested]:

SQL="SELECT Count(*) AS NP FROM museebus_Site "_
"WHERE printed = 'No'"

And then [assuming the table has records]:

x.execute(SQL)

response.write "amount: " & x("NP")
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress,
but let us keep the discussions in the newsgroup)

Jul 19 '05 #4
That works fine!

Thanks.

Evertjan. wrote:
Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:

Hi, sorry for my messed up coding ...

Here's the SQL code (accessing an Access DB):

SELECT Count(museebus_Site.IDUmeldung) AS NP
FROM museebus_Site GROUP BY museebus_Site.printed HAVING
(((museebus_Site.printed)=No));

What I want to do is display on a summary page how many records there
are which are still un-printed (hence the count).

Why not simply [something like/not tested]:

SQL="SELECT Count(*) AS NP FROM museebus_Site "_
"WHERE printed = 'No'"

And then [assuming the table has records]:

x.execute(SQL)

response.write "amount: " & x("NP")

Jul 19 '05 #5
Jerome wrote:
Hi, sorry for my messed up coding ...

Here's the SQL code (accessing an Access DB):

SELECT Count(museebus_Site.IDUmeldung) AS NP
FROM museebus_Site GROUP BY museebus_Site.printed HAVING
(((museebus_Site.printed)=No));


Evertian has it correct. You are confused about when it is correct to use
Having instead of Where. The simple explanation is: WHERE conditions are
applied BEFORE the records are grouped and aggregations are calculated.
HAVING conditions are applied AFTER the grouping. Whenever possible, you
should use WHERE, because the fewer records you supply to the grouping
mechanism, the better your query will perform. Only use HAVING conditions
when you need to evaluate the records after the grouping and aggregation are
completed.
Some guidelines:
1. If the field you wish to filter appears in the GROUP BY clause, use WHERE

Select a, sum(b)
from table
where a = 3
group by a

This query will return a single row

2. If you wish to limit the resultset based on the result of an aggregation,
use HAVING

Select a, sum(b)
from table
group by a
HAVING sum(b) > 25

This query will return only rows where the sum is greater than 25.

3. If the field you are grouping by does not appear in the SELECT list, then
there is no need to put it in the GROUP BY clause:

Select sum(b)
from table
where a = 3

This query will again return a single row

4. If the field you wish to filter appears neither in the GROUP BY list nor
the SELECT list, use WHERE:

Select a, sum(b)
from table
where c = 3
group by a
HAVING sum(b) > 25
The reason that your query returned no records is because the HAVING clause
was applied to the records resulting from the grouping operation. These
records contained no field called printed, so the HAVING condition could
never be satisfied, resulting in no records being returned.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: Mat | last post by:
<div id="container"> <div id="main"> <div id="header"> <p class="Address">123 Fake Street, </p> <p class="City">Crazy City, </p> <p class="Province">Ontario </p> <p class="PostalCode">H0H...
2
by: Kay | last post by:
This function is used to display the context of a queue. I input an integer comparing with the queue cuisine number, if cuisine number is same as the input, it shows the context. However, it cannot...
1
by: Tristan Miller | last post by:
Greetings. I am trying to write a function which toggles the display of a certain class of <div> elements in an HTML page. The CSS file initially sets some classes to "display: none", and...
9
by: Sandy | last post by:
Hi all, I have a form to list records (frmListIssue) which I call from different other forms. My wish is to display a message when the form is called and empty; no records to display. I want to...
2
by: ameshkin | last post by:
I know this is probably not too hard to do, but how do I display multiple rows of a mysql query/recordset. Im having trouble doing this. I don't just want to display them, but I want to make sure...
3
by: Bob Sanderson | last post by:
I am trying to create a form for a MySQL database similar to a spreadsheet. The idea is to display a list of records, with the last line of the list being an input form. When the user enters data...
2
by: rn5a | last post by:
The different Validation controls like RequiredFieldValidator, RangeValidator etc. have a property named Display. This property can have 3 values - Dynamic, Static & None. What's the difference...
18
by: fishwick | last post by:
I haven't really done any css in quite a while, and am banging my head against the wall trying get the rudimentary layout together of a church website home page to display correctly - I don't want...
4
by: rn5a | last post by:
A MS-Access DB has 3 tables - Teacher, Class & TeacherClass. The Teacher table has 2 columns - TeacherID & TeacherName (TeacherID being the primary key). The Class table too has 2 columns - ClassID...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.