display problem when query is empty!?

[Jerome]

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.BO F = true or rsNB_MB_Site.EO F = true then
response.write "amount: 0"
else
response.write "amount: " & response.write
rsNP_MB_Site.Fi elds.Item("NP") .Value
end if

But I keep getting an 'object required' error?

I've also tried with isnull(rsNP_MB_ Site.Fields.Ite m("NP").Value ) but
that didn't work out either.

Any help is greatly appreciated,

Jerome

[Evertjan.]

Re: display problem when query is empty!?

Jerome wrote on 31 aug 2004 in microsoft.publi c.inetserver.as p.general:
[color=blue]
> 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.[/color]

A reasonable ;-) correct SQL count() will return a valid 0 count
[color=blue]
> What I've tried is this:
>
> if rsNP_MB_Site.BO F = true or rsNB_MB_Site.EO F = true then[/color]

if rsNP_MB_Site.BO F = true or rsNB_MB_Site.EO F = true then
is the same as just:
if rsNP_MB_Site.BO F or rsNB_MB_Site.EO F then
[color=blue]
> response.write "amount: 0"
> else
> response.write "amount: " & response.write
> rsNP_MB_Site.Fi elds.Item("NP") .Value[/color]

This is vbs nonsense, should be:

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

but how can this "Item" be the result of a SQL count() ?
[color=blue]
> end if
>[/color]

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)

[Jerome]

Re: display problem when query is empty!?

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.p rinted HAVING
(((museebus_Sit e.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:
[color=blue]
> Jerome wrote on 31 aug 2004 in microsoft.publi c.inetserver.as p.general:
>
>[color=green]
>>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.[/color]
>
>
> A reasonable ;-) correct SQL count() will return a valid 0 count
>
>[color=green]
>>What I've tried is this:
>>
>>if rsNP_MB_Site.BO F = true or rsNB_MB_Site.EO F = true then[/color]
>
>
> if rsNP_MB_Site.BO F = true or rsNB_MB_Site.EO F = true then
> is the same as just:
> if rsNP_MB_Site.BO F or rsNB_MB_Site.EO F then
>
>[color=green]
>> response.write "amount: 0"
>>else
>> response.write "amount: " & response.write
>>rsNP_MB_Site. Fields.Item("NP ").Value[/color]
>
>
> This is vbs nonsense, should be:
>
> response.write "amount: " & rsNP_MB_Site.Fi elds.Item("NP") .Value
>
> but how can this "Item" be the result of a SQL count() ?
>
>[color=green]
>>end if
>>[/color]
>
>
> I suggest you show your code and specify the db-engine and connection
> type.
>[/color]

[Evertjan.]

Re: display problem when query is empty!?

Jerome wrote on 31 aug 2004 in microsoft.publi c.inetserver.as p.general:
[color=blue]
> 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.p rinted HAVING
> (((museebus_Sit e.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).
>[/color]

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)

[Jerome]

Re: display problem when query is empty!?

That works fine!

Thanks.

Evertjan. wrote:[color=blue]
> Jerome wrote on 31 aug 2004 in microsoft.publi c.inetserver.as p.general:
>
>[color=green]
>>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.p rinted HAVING
>>(((museebus_S ite.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).
>>[/color]
>
>
> 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")
>
>[/color]

[Bob Barrows [MVP]]

Re: display problem when query is empty!?

Jerome wrote:[color=blue]
> 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.p rinted HAVING
> (((museebus_Sit e.printed)=No)) ;
>[/color]

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"