ASP, using SQL COUNT as a condition in IF ELSE statement

[Gary]

I am trying to do a simple "less than" conditional statement, and hitting a
brick wall if I use a database element with it.

This DOES work:
<%
if Request.QueryString("PC") < 10 then Response.Write("less than ten") else
Response.Write("more than or equal to ten") End if
%>


This DOES NOT work:
<%
if Request.QueryString("PC") < Record.Fields.Item("count(*)").Value then
Response.Write("less than COUNT") else Response.Write("less than or equal to
count") End if
%>

The only difference is that in the first example a number is used as the
condition. in the second example, I have counted the number of rows in a
table, and I want to use that number as the condition. The SQL select is:
SELECT count(*), EnquiryDate
FROM lfmasterleads.partner_enquiries
WHERE EnquiryDate = CURRENT_DATE() GROUP BY EnquiryDate

Any help greatly appreciated!

Darren.

[Aaron Bertrand - MVP]

There is no such column as COUNT(*). See [url]http://www.aspfaq.com/2159[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Gary" <sorry@nocando.com> wrote in message
news:6ibYb.564$Ih4.6389862@news-text.cableinet.net...

> I am trying to do a simple "less than" conditional statement, and hitting

a

> brick wall if I use a database element with it.
>
> This DOES work:
> <%
> if Request.QueryString("PC") < 10 then Response.Write("less than ten")

else

> Response.Write("more than or equal to ten") End if
> %>
>
>
> This DOES NOT work:
> <%
> if Request.QueryString("PC") < Record.Fields.Item("count(*)").Value then
> Response.Write("less than COUNT") else Response.Write("less than or equal

to

> count") End if
> %>
>
> The only difference is that in the first example a number is used as the
> condition. in the second example, I have counted the number of rows in a
> table, and I want to use that number as the condition. The SQL select is:
> SELECT count(*), EnquiryDate
> FROM lfmasterleads.partner_enquiries
> WHERE EnquiryDate = CURRENT_DATE() GROUP BY EnquiryDate
>
> Any help greatly appreciated!
>
> Darren.
>
>

[Gary]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:enbI4tN9DHA.1472@TK2MSFTNGP11.phx.gbl...

> There is no such column as COUNT(*). See [url]http://www.aspfaq.com/2159[/url]

Seems to work for me?
Gary

[Aaron Bertrand - MVP]

> Seems to work for me?

Maybe I should rephrase. COUNT(*) is not a column, it is an aggregate. You
cannot refer directly to an aggregate, as the article (and your own "DOES
NOT work" experience) has shown you. You need to define an alias or use the
ordinal number.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bullschmidt]

<<
I am trying to do a simple "less than" conditional statement, and
hitting a
brick wall if I use a database element with it.

This DOES work:
<%
if Request.QueryString("PC") < 10 then Response.Write("less than ten")
else
Response.Write("more than or equal to ten") End if
%>


This DOES NOT work:
<%
if Request.QueryString("PC") < Record.Fields.Item("count(*)").Value then
Response.Write("less than COUNT") else Response.Write("less than or
equal to
count") End if
%>

The only difference is that in the first example a number is used as the
condition. in the second example, I have counted the number of rows in a
table, and I want to use that number as the condition. The SQL select
is:
SELECT count(*), EnquiryDate
FROM lfmasterleads.partner_enquiries
WHERE EnquiryDate = CURRENT_DATE() GROUP BY EnquiryDate

Any help greatly appreciated!

Darren.

>>

Perhaps change:
SELECT count(*), EnquiryDate

To be this instead:
SELECT count(*) AS RecCount, EnquiryDate

And then on the first record use the value of objRS("RecCount").

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!