Board index » delphi » Executing the SQL "count(*)"

Executing the SQL "count(*)"

I am having a problem with trying to use the SQL Count(*) command.
I am using  TADOCommand connected to a TADODatset - I seem to be getting a
syntax error, also I am not sure how I get the results of the Count (I am
just looking for the number not any resulting data set)  Can I do this.
I am not using ADODatset.recordcount because I am trying to get the count
after I have performed a filter.

Thanks for any help,  Joe

 

Re:Executing the SQL "count(*)"


If you think you might have a problem with syntax, you should post your sql
statement here so we can take a look at it. Concerning getting result out
you should do something like

    select count(*) as cnt from....

later you can pick this value from cnt field AsInteger.

rb

Re:Executing the SQL "count(*)"


If the Count(*) is in a SELECT statement you have to look into the resulting
data set. If you don't like that, you can make a stored procedure for it,
which returns the result as a parameter.

Martijn Houtman

Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0c23fe$1_2@dnews...
> I am having a problem with trying to use the SQL Count(*) command.
> I am using  TADOCommand connected to a TADODatset - I seem to be getting a
> syntax error, also I am not sure how I get the results of the Count (I am
> just looking for the number not any resulting data set)  Can I do this.
> I am not using ADODatset.recordcount because I am trying to get the count
> after I have performed a filter.

> Thanks for any help,  Joe

Re:Executing the SQL "count(*)"


Thanks Martin.
Actually that sounds like what I am trying to do!  I will look into that do
you know where I could find a good example?

Thanks again, Joe

Quote
"Martijn Houtman" <newsgr...@martijnhoutman.com> wrote in message

news:3b0cee02_1@dnews...
Quote
> If the Count(*) is in a SELECT statement you have to look into the
resulting
> data set. If you don't like that, you can make a stored procedure for it,
> which returns the result as a parameter.

> Martijn Houtman

> "Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0c23fe$1_2@dnews...
> > I am having a problem with trying to use the SQL Count(*) command.
> > I am using  TADOCommand connected to a TADODatset - I seem to be getting
a
> > syntax error, also I am not sure how I get the results of the Count (I
am
> > just looking for the number not any resulting data set)  Can I do this.
> > I am not using ADODatset.recordcount because I am trying to get the
count
> > after I have performed a filter.

> > Thanks for any help,  Joe

Re:Executing the SQL "count(*)"


May be you can start with the Ado demo in Delphi.

Martijn Houtman

Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0d0cba$1_2@dnews...
> Thanks Martin.
> Actually that sounds like what I am trying to do!  I will look into that
do
> you know where I could find a good example?

> Thanks again, Joe

> "Martijn Houtman" <newsgr...@martijnhoutman.com> wrote in message
> news:3b0cee02_1@dnews...
> > If the Count(*) is in a SELECT statement you have to look into the
> resulting
> > data set. If you don't like that, you can make a stored procedure for
it,
> > which returns the result as a parameter.

> > Martijn Houtman

> > "Joe Pettit" <j...@i-f-s.com> wrote in message

news:3b0c23fe$1_2@dnews...

- Show quoted text -

Quote
> > > I am having a problem with trying to use the SQL Count(*) command.
> > > I am using  TADOCommand connected to a TADODatset - I seem to be
getting
> a
> > > syntax error, also I am not sure how I get the results of the Count (I
> am
> > > just looking for the number not any resulting data set)  Can I do
this.
> > > I am not using ADODatset.recordcount because I am trying to get the
> count
> > > after I have performed a filter.

> > > Thanks for any help,  Joe

Re:Executing the SQL "count(*)"


Thanks Rob.

I did not put an example because a lot of this is new to me so I wasn't
really sure how close I was.  As it turned out I was pretty close and now I
put the sql command in Tadocommand and it seems to execute without an error.

Quote
>    select count(*) as cnt from....
> later you can pick this value from cnt field AsInteger.

Here I am lost again, is "cnt" supposed to be a field already defined in the
table?  Or does the Count(*) add this field?
Either way I am having a problem getting the count.

Thanks again, Joe

Re:Executing the SQL "count(*)"


If you want to return a result set, you should use a TAdoDataset instead of
a TAdoCommand.  TAdoCommand is for DDL statements (INSERT, UPDATE, etc.)

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0d3209_2@dnews...
> Thanks Rob.

> I did not put an example because a lot of this is new to me so I wasn't
> really sure how close I was.  As it turned out I was pretty close and now
I
> put the sql command in Tadocommand and it seems to execute without an
error.

> >    select count(*) as cnt from....
> > later you can pick this value from cnt field AsInteger.

> Here I am lost again, is "cnt" supposed to be a field already defined in
the
> table?  Or does the Count(*) add this field?
> Either way I am having a problem getting the count.

> Thanks again, Joe

Re:Executing the SQL "count(*)"


Actually i would rather not return a result set.  But I was having a problem
with using a parameter with either ADOQuery or ADOStoredproc.

Quote
"Michael Rodriguez" <mike@yada_yada_yada.compeat.com> wrote in message

news:3b0d62a1_1@dnews...
Quote
> If you want to return a result set, you should use a TAdoDataset instead
of
> a TAdoCommand.  TAdoCommand is for DDL statements (INSERT, UPDATE, etc.)

> --
> Michael Rodriguez
> Compeat Restaurant Accounting Systems
> http://www.compeat.com
> m...@compeat.com

> "Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0d3209_2@dnews...
> > Thanks Rob.

> > I did not put an example because a lot of this is new to me so I wasn't
> > really sure how close I was.  As it turned out I was pretty close and
now
> I
> > put the sql command in Tadocommand and it seems to execute without an
> error.

> > >    select count(*) as cnt from....
> > > later you can pick this value from cnt field AsInteger.

> > Here I am lost again, is "cnt" supposed to be a field already defined in
> the
> > table?  Or does the Count(*) add this field?
> > Either way I am having a problem getting the count.

> > Thanks again, Joe

Re:Executing the SQL "count(*)"


So, you have a SELECT command, that returns a field, and you don't want a
result set...

???

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0d64d3$1_1@dnews...
> Actually i would rather not return a result set.  But I was having a
problem
> with using a parameter with either ADOQuery or ADOStoredproc.

Re:Executing the SQL "count(*)"


Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0d3209_2@dnews...

> Here I am lost again, is "cnt" supposed to be a field already defined in
the
> table?  Or does the Count(*) add this field?
> Either way I am having a problem getting the count.

AS operator gives you a way to "name" nameless calculated fields or rename
fields when 2 or more fields from the same result set have the same name.
For example

    select a.Student, a.City as StudentCity, b.University, b.City as
UniversityCity
    from Students a
    inner join Universities b on...

If you don't rename City fields yourself, Delphi will do it for you using
underscores and digits to extend their names, i.e. City and City_1. Clearly
the above example is much more useful.

rb

Re:Executing the SQL "count(*)"


Actually what I am trying to do is find the number of records in a dataset
after a filter has been applied.
To do this I was attempting to use the SQL command "Select Count(*)".  So I
really don't need any resulting set I just want the count.  But if I can't
get just the count and I have to get the resulting set then so be it.
I apologize if this seems confusing.  I am somehat surprised that I am
having such a hard time getting this "filter count".

Thanks for any help,  Joe

Quote
"Michael Rodriguez" <mike@yada_yada_yada.compeat.com> wrote in message

news:3b0d66af_2@dnews...
Quote
> So, you have a SELECT command, that returns a field, and you don't want a
> result set...

> ???

> --
> Michael Rodriguez
> Compeat Restaurant Accounting Systems
> http://www.compeat.com
> m...@compeat.com

> "Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0d64d3$1_1@dnews...
> > Actually i would rather not return a result set.  But I was having a
> problem
> > with using a parameter with either ADOQuery or ADOStoredproc.

Re:Executing the SQL "count(*)"


So in your example how do access "StudentCity"?
??? resultset.fields.fieldbyname('StudentCity').asstring

I think I tried this with the Count(*) as cnt example and had no luck.
("Dataset.fields.fieldbyname('Cnt').asinteger")

Thanks, Joe

Quote
> AS operator gives you a way to "name" nameless calculated fields or rename
> fields when 2 or more fields from the same result set have the same name.
> For example

>     select a.Student, a.City as StudentCity, b.University, b.City as
> UniversityCity
>     from Students a
>     inner join Universities b on...

> If you don't rename City fields yourself, Delphi will do it for you using
> underscores and digits to extend their names, i.e. City and City_1.
Clearly
> the above example is much more useful.

> rb

Re:Executing the SQL "count(*)"


Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b0e649e_1@dnews...
> So in your example how do access "StudentCity"?
> ??? resultset.fields.fieldbyname('StudentCity').asstring

> I think I tried this with the Count(*) as cnt example and had no luck.
> ("Dataset.fields.fieldbyname('Cnt').asinteger")

Try again, I do this all the time and for the same reasons you mentioned
before. What may be causing you problems is persistent fields - if you
created any.

Your "real" query and "count" query look the same except that count query
doesn't have explicit fields listed, i.e.

    select Client, City, Country from Clients where Client like 'M%'
vs.
    select count( * ) as cnt from Clients where Client like 'M%'

In this case, you can't have persistent fields defined since you're changing
the query and its nature. If you do have persistent fields Query1Client,
Query1City and Query1Country, then Delphi won't dynamically create ones for
you, and therefore FieldByName( 'cnt' ) would fail since cnt it's not on the
list. If you need persistent fields anyway (for grid or other data aware
controls), you'll have to use 2 different components to accomplish this.

rb

Other Threads