Board index » delphi » using sql "COUNT(*)"

using sql "COUNT(*)"

Hello,
I am using D5 and the ADO components and I am having a problem getting a
count of the records in a dataset after I perform a filter.

Problem:
I create a tab delimited text file from a report and then I use the Jet
engine to read this file into a TADODataset component by setting the
properties of the Dataset as follows:
"textds" is the name of the TADODataset component
     textds.Connectionstring := Provider=Microsoft.Jet.OLEDB.4.0;
                                        Data
Source=c:\odinwork\;Mode=ReadWrite|Share Deny None;
                                        Extended Properties="Text";Persist
Security Info=False
     textds.commandtype := cmdtabledirect;
           /* "textfilename" is the name of the text file with the '.'
replaced by '#' */
     textds.CommandText := textfilename;    /* sample#txt */

This all works fine.
I also have a TADOCommand component that i use to execute some sql commands,
here is where I have a problem with getting the count.
textcmd is the name of the TADCommand component
     textcmd.connectionsstring := same as for textds (see above)
     textcmd.Commandtype := 'cmdtext';
              /* "filename.txt" is the name of the tab delimited text file
same as textfilename above */
     textcmd.commandtext := 'Select COUNT(*) as cnt FROM filename.txt';

     textcmd.execute;
    showmessage(textds.fields.fieldbyname('cnt').asstring);

I get an error, something like - "textds field "cnt" is unknown".

Can I do this?
Is my problem with the fact that I am using a text file?  (I have some very
good reasons for using the text file and everything else in the app is
working fine)
I can iterate throught the dataset and get the number of records after the
filter but that seems slow.  Is there another way to do this?

Any help would be greatly appreciated!

Thanks,  Joe

 

Re:using sql "COUNT(*)"


As a select statement returns results, you need to open it, not execute it.
This is true regardless of what the select statement does, so transfer this
to a TADOQuery and open it. It should work then.

--
Adroit Software Ltd
------------------------------
Remove the nospam. to mail me direct

Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b1e507a_1@dnews...
> Hello,
> I am using D5 and the ADO components and I am having a problem getting a
> count of the records in a dataset after I perform a filter.

> Problem:
> I create a tab delimited text file from a report and then I use the Jet
> engine to read this file into a TADODataset component by setting the
> properties of the Dataset as follows:
> "textds" is the name of the TADODataset component
>      textds.Connectionstring := Provider=Microsoft.Jet.OLEDB.4.0;
>                                         Data
> Source=c:\odinwork\;Mode=ReadWrite|Share Deny None;
>                                         Extended Properties="Text";Persist
> Security Info=False
>      textds.commandtype := cmdtabledirect;
>            /* "textfilename" is the name of the text file with the '.'
> replaced by '#' */
>      textds.CommandText := textfilename;    /* sample#txt */

> This all works fine.
> I also have a TADOCommand component that i use to execute some sql
commands,
> here is where I have a problem with getting the count.
> textcmd is the name of the TADCommand component
>      textcmd.connectionsstring := same as for textds (see above)
>      textcmd.Commandtype := 'cmdtext';
>               /* "filename.txt" is the name of the tab delimited text file
> same as textfilename above */
>      textcmd.commandtext := 'Select COUNT(*) as cnt FROM filename.txt';

>      textcmd.execute;
>     showmessage(textds.fields.fieldbyname('cnt').asstring);

> I get an error, something like - "textds field "cnt" is unknown".

> Can I do this?
> Is my problem with the fact that I am using a text file?  (I have some
very
> good reasons for using the text file and everything else in the app is
> working fine)
> I can iterate throught the dataset and get the number of records after the
> filter but that seems slow.  Is there another way to do this?

> Any help would be greatly appreciated!

> Thanks,  Joe

Re:using sql "COUNT(*)"


Thanks Brian, I will give it a try

Quote
"Brian Jones" <bri...@nospam.adroit-software.co.uk> wrote in message

news:3b1e5148$2_1@dnews...
Quote
> As a select statement returns results, you need to open it, not execute
it.
> This is true regardless of what the select statement does, so transfer
this
> to a TADOQuery and open it. It should work then.

> --
> Adroit Software Ltd
> ------------------------------
> Remove the nospam. to mail me direct
> "Joe Pettit" <j...@i-f-s.com> wrote in message news:3b1e507a_1@dnews...
> > Hello,
> > I am using D5 and the ADO components and I am having a problem getting a
> > count of the records in a dataset after I perform a filter.

> > Problem:
> > I create a tab delimited text file from a report and then I use the Jet
> > engine to read this file into a TADODataset component by setting the
> > properties of the Dataset as follows:
> > "textds" is the name of the TADODataset component
> >      textds.Connectionstring := Provider=Microsoft.Jet.OLEDB.4.0;
> >                                         Data
> > Source=c:\odinwork\;Mode=ReadWrite|Share Deny None;
> >                                         Extended

Properties="Text";Persist

- Show quoted text -

Quote
> > Security Info=False
> >      textds.commandtype := cmdtabledirect;
> >            /* "textfilename" is the name of the text file with the '.'
> > replaced by '#' */
> >      textds.CommandText := textfilename;    /* sample#txt */

> > This all works fine.
> > I also have a TADOCommand component that i use to execute some sql
> commands,
> > here is where I have a problem with getting the count.
> > textcmd is the name of the TADCommand component
> >      textcmd.connectionsstring := same as for textds (see above)
> >      textcmd.Commandtype := 'cmdtext';
> >               /* "filename.txt" is the name of the tab delimited text
file
> > same as textfilename above */
> >      textcmd.commandtext := 'Select COUNT(*) as cnt FROM filename.txt';

> >      textcmd.execute;
> >     showmessage(textds.fields.fieldbyname('cnt').asstring);

> > I get an error, something like - "textds field "cnt" is unknown".

> > Can I do this?
> > Is my problem with the fact that I am using a text file?  (I have some
> very
> > good reasons for using the text file and everything else in the app is
> > working fine)
> > I can iterate throught the dataset and get the number of records after
the
> > filter but that seems slow.  Is there another way to do this?

> > Any help would be greatly appreciated!

> > Thanks,  Joe

Re:using sql "COUNT(*)"


Actually upon thinking about this I have a question.
All I want is the count, so do I want/ need "results"?
Can I just get the count?
Can i use the "Parameters" in TADOCOMMAND?

Thanks again,  Joe

Quote
"Brian Jones" <bri...@nospam.adroit-software.co.uk> wrote in message

news:3b1e5148$2_1@dnews...
Quote
> As a select statement returns results, you need to open it, not execute
it.
> This is true regardless of what the select statement does, so transfer
this
> to a TADOQuery and open it. It should work then.

> --
> Adroit Software Ltd
> ------------------------------
> Remove the nospam. to mail me direct
> "Joe Pettit" <j...@i-f-s.com> wrote in message news:3b1e507a_1@dnews...
> > Hello,
> > I am using D5 and the ADO components and I am having a problem getting a
> > count of the records in a dataset after I perform a filter.

> > Problem:
> > I create a tab delimited text file from a report and then I use the Jet
> > engine to read this file into a TADODataset component by setting the
> > properties of the Dataset as follows:
> > "textds" is the name of the TADODataset component
> >      textds.Connectionstring := Provider=Microsoft.Jet.OLEDB.4.0;
> >                                         Data
> > Source=c:\odinwork\;Mode=ReadWrite|Share Deny None;
> >                                         Extended

Properties="Text";Persist

- Show quoted text -

Quote
> > Security Info=False
> >      textds.commandtype := cmdtabledirect;
> >            /* "textfilename" is the name of the text file with the '.'
> > replaced by '#' */
> >      textds.CommandText := textfilename;    /* sample#txt */

> > This all works fine.
> > I also have a TADOCommand component that i use to execute some sql
> commands,
> > here is where I have a problem with getting the count.
> > textcmd is the name of the TADCommand component
> >      textcmd.connectionsstring := same as for textds (see above)
> >      textcmd.Commandtype := 'cmdtext';
> >               /* "filename.txt" is the name of the tab delimited text
file
> > same as textfilename above */
> >      textcmd.commandtext := 'Select COUNT(*) as cnt FROM filename.txt';

> >      textcmd.execute;
> >     showmessage(textds.fields.fieldbyname('cnt').asstring);

> > I get an error, something like - "textds field "cnt" is unknown".

> > Can I do this?
> > Is my problem with the fact that I am using a text file?  (I have some
> very
> > good reasons for using the text file and everything else in the app is
> > working fine)
> > I can iterate throught the dataset and get the number of records after
the
> > filter but that seems slow.  Is there another way to do this?

> > Any help would be greatly appreciated!

> > Thanks,  Joe

Re:using sql "COUNT(*)"


No, you will need to open it, get the fieldbyname, and then close it.
Otherwise you could write a storedprocedure - but this is overkill for a
simple select count(*)

--
Adroit Software Ltd
------------------------------
Remove the nospam. to mail me direct

Quote
"Joe Pettit" <j...@i-f-s.com> wrote in message news:3b1e6409$1_2@dnews...
> Actually upon thinking about this I have a question.
> All I want is the count, so do I want/ need "results"?
> Can I just get the count?
> Can i use the "Parameters" in TADOCOMMAND?

> Thanks again,  Joe

> "Brian Jones" <bri...@nospam.adroit-software.co.uk> wrote in message
> news:3b1e5148$2_1@dnews...
> > As a select statement returns results, you need to open it, not execute
> it.
> > This is true regardless of what the select statement does, so transfer
> this
> > to a TADOQuery and open it. It should work then.

> > --
> > Adroit Software Ltd
> > ------------------------------
> > Remove the nospam. to mail me direct
> > "Joe Pettit" <j...@i-f-s.com> wrote in message news:3b1e507a_1@dnews...
> > > Hello,
> > > I am using D5 and the ADO components and I am having a problem getting
a
> > > count of the records in a dataset after I perform a filter.

> > > Problem:
> > > I create a tab delimited text file from a report and then I use the
Jet
> > > engine to read this file into a TADODataset component by setting the
> > > properties of the Dataset as follows:
> > > "textds" is the name of the TADODataset component
> > >      textds.Connectionstring := Provider=Microsoft.Jet.OLEDB.4.0;
> > >                                         Data
> > > Source=c:\odinwork\;Mode=ReadWrite|Share Deny None;
> > >                                         Extended
> Properties="Text";Persist
> > > Security Info=False
> > >      textds.commandtype := cmdtabledirect;
> > >            /* "textfilename" is the name of the text file with the '.'
> > > replaced by '#' */
> > >      textds.CommandText := textfilename;    /* sample#txt */

> > > This all works fine.
> > > I also have a TADOCommand component that i use to execute some sql
> > commands,
> > > here is where I have a problem with getting the count.
> > > textcmd is the name of the TADCommand component
> > >      textcmd.connectionsstring := same as for textds (see above)
> > >      textcmd.Commandtype := 'cmdtext';
> > >               /* "filename.txt" is the name of the tab delimited text
> file
> > > same as textfilename above */
> > >      textcmd.commandtext := 'Select COUNT(*) as cnt FROM
filename.txt';

> > >      textcmd.execute;
> > >     showmessage(textds.fields.fieldbyname('cnt').asstring);

> > > I get an error, something like - "textds field "cnt" is unknown".

> > > Can I do this?
> > > Is my problem with the fact that I am using a text file?  (I have some
> > very
> > > good reasons for using the text file and everything else in the app is
> > > working fine)
> > > I can iterate throught the dataset and get the number of records after
> the
> > > filter but that seems slow.  Is there another way to do this?

> > > Any help would be greatly appreciated!

> > > Thanks,  Joe

Other Threads