Get Total Rows in Database?

Discussion in 'ASP General' started by JoeM, Feb 20, 2006.

  1. JoeM

    JoeM Guest

    I have a list of data in a database(access). How can I get the total rows
    in the database?

    Thanks
     
    JoeM, Feb 20, 2006
    #1
    1. Advertising

  2. JoeM

    JoeM Guest

    I have tried that it will count the columns without any trouble, but I need
    to count the rows.
    I have 2 columns Customer ID and Name, I need to get the either the last
    customerID or how many entries are in the data base.

    Example:
    CustomerID Name
    1 James
    2 John
    3 Adam
    4 Jason

    I would need to pull the last customerID. The numbers will increase and are
    unknown when trying to get the ID.

    Thanks

    "Dave Anderson" <> wrote in message
    news:...
    > JoeM wrote:
    >> I have a list of data in a database(access). How can
    >> I get the total rows in the database?

    >
    > http://office.microsoft.com/en-us/assistance/HP010322151033.aspx
    >
    >
    > --
    > Dave Anderson
    >
    > Unsolicited commercial email will be read at a cost of $500 per message.
    > Use of this email address implies consent to these terms. Please do not
    > contact me directly or ask me to contact you directly for assistance. If
    > your question is worth asking, it's worth posting.
    >
     
    JoeM, Feb 20, 2006
    #2
    1. Advertising

  3. top.
    to
    bottom
    from
    read
    not
    do
    conversations
    sentences,
    Like
    toppost.
    not
    do
    Please


    JoeM wrote:
    > I have tried that it will count the columns without
    > any trouble, but I need to count the rows.


    Count(*) returns a row count. I don't know what makes you think it returns a
    column count.



    > I have 2 columns Customer ID and Name, I need to get
    > the either the last customerID or how many entries
    > are in the data base.
    >
    > Example:
    > CustomerID Name
    > 1 James
    > 2 John
    > 3 Adam
    > 4 Jason
    >
    > I would need to pull the last customerID. The numbers
    > will increase and are unknown when trying to get the
    > ID.


    Show us the SQL you have tried.



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, Feb 20, 2006
    #3
  4. JoeM wrote:
    > I have tried that it will count the columns without any trouble,


    No it won't. Count(*) counts rows, not columns.

    > but
    > I need to count the rows.
    > I have 2 columns Customer ID and Name, I need to get the either the
    > last customerID or how many entries are in the data base.


    First of all, let's get your terminology straight: a "database" is a
    collection of tables which are collections of rows and columns of data.

    So it seems that you have a database (what is the type and version please?
    It's not relevant in this situation, but that's rare. The type and version
    of database is almost always relevant) that contains a table that contains
    the following rows and columns of sample data.

    >
    > Example:
    > CustomerID Name
    > 1 James
    > 2 John
    > 3 Adam
    > 4 Jason
    >
    > I would need to pull the last customerID. The numbers will increase
    > and are unknown when trying to get the ID.
    >


    Will the number of "entries" in the table still be relevant if one of the
    entries gets deleted? You should decide what you really want: the largest
    CustomerID

    Select Max(CustomerID) from tablename

    or the number of rows:

    Select Count(*) From tablename

    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"
     
    Bob Barrows [MVP], Feb 20, 2006
    #4
  5. JoeM

    JoeM Guest

    Thanks I had some lines mixed up, the code works, Thank you

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > JoeM wrote:
    >> I have tried that it will count the columns without any trouble,

    >
    > No it won't. Count(*) counts rows, not columns.
    >
    >> but
    >> I need to count the rows.
    >> I have 2 columns Customer ID and Name, I need to get the either the
    >> last customerID or how many entries are in the data base.

    >
    > First of all, let's get your terminology straight: a "database" is a
    > collection of tables which are collections of rows and columns of data.
    >
    > So it seems that you have a database (what is the type and version please?
    > It's not relevant in this situation, but that's rare. The type and version
    > of database is almost always relevant) that contains a table that contains
    > the following rows and columns of sample data.
    >
    >>
    >> Example:
    >> CustomerID Name
    >> 1 James
    >> 2 John
    >> 3 Adam
    >> 4 Jason
    >>
    >> I would need to pull the last customerID. The numbers will increase
    >> and are unknown when trying to get the ID.
    >>

    >
    > Will the number of "entries" in the table still be relevant if one of the
    > entries gets deleted? You should decide what you really want: the largest
    > CustomerID
    >
    > Select Max(CustomerID) from tablename
    >
    > or the number of rows:
    >
    > Select Count(*) From tablename
    >
    > 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"
    >
     
    JoeM, Feb 20, 2006
    #5
  6. JoeM

    JoeM Guest

    Thanks I had some lines mixed up, the code works, Thank you

    "Dave Anderson" <> wrote in message
    news:%...
    > top.
    > to
    > bottom
    > from
    > read
    > not
    > do
    > conversations
    > sentences,
    > Like
    > toppost.
    > not
    > do
    > Please
    >
    >
    > JoeM wrote:
    >> I have tried that it will count the columns without
    >> any trouble, but I need to count the rows.

    >
    > Count(*) returns a row count. I don't know what makes you think it returns
    > a column count.
    >
    >
    >
    >> I have 2 columns Customer ID and Name, I need to get
    >> the either the last customerID or how many entries
    >> are in the data base.
    >>
    >> Example:
    >> CustomerID Name
    >> 1 James
    >> 2 John
    >> 3 Adam
    >> 4 Jason
    >>
    >> I would need to pull the last customerID. The numbers
    >> will increase and are unknown when trying to get the
    >> ID.

    >
    > Show us the SQL you have tried.
    >
    >
    >
    > --
    > Dave Anderson
    >
    > Unsolicited commercial email will be read at a cost of $500 per message.
    > Use of this email address implies consent to these terms. Please do not
    > contact me directly or ask me to contact you directly for assistance. If
    > your question is worth asking, it's worth posting.
    >
     
    JoeM, Feb 20, 2006
    #6
  7. JoeM

    Evertjan. Guest

    Bob Barrows [MVP] wrote on 20 feb 2006 in
    microsoft.public.inetserver.asp.general:

    > First of all, let's get your terminology straight: a "database" is a
    > collection of tables which are collections of rows and columns of data.
    >


    Bob,

    Wouldn't it be far more efficient stick to talking about
    [tables,] records and fields?

    You are the only one that can implement that on this NG at the moment.

    After all, in this sense a record is a collection of fields,
    but a field is not a collection of records.

    A database is not spreadsheet,
    and it is detrimental to the many that see it that way.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Feb 20, 2006
    #7
  8. JoeM

    Paxton Guest

    JoeM wrote:

    > >> I have 2 columns Customer ID and Name, I need to get
    > >> the either the last customerID or how many entries
    > >> are in the data base.
    > >>
    > >> Example:
    > >> CustomerID Name
    > >> 1 James
    > >> 2 John
    > >> 3 Adam
    > >> 4 Jason
    > >>
    > >> I would need to pull the last customerID. The numbers
    > >> will increase and are unknown when trying to get the
    > >> ID.



    JoeM wrote:
    > Thanks I had some lines mixed up, the code works, Thank you


    Select Count(*) to identify the last ID will work *IF* you never delete
    a customer record. However, in any real database, it is inevitable
    that you will delete a customer record sooner or later. Any new record
    will not adopt the deleted record's ID - it will just acquire the next
    number that hasn't been used before.

    So if you delete John, then add Fred, Fred will be number 5, but your
    row count will be 4. If you really want to get the latest ID, you need
    SELECT @@IDENTITY for Access, or SCOPE_IDENTITY() for SQL Server 2000+

    http://www.aspfaq.com/show.asp?id=2174

    /P.
     
    Paxton, Feb 20, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?ZGF2aWQ=?=

    How to get the total number of rows in a Dataset?

    =?Utf-8?B?ZGF2aWQ=?=, May 3, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    17,304
    Yunus Emre ALPĂ–ZEN [MCAD.NET]
    May 3, 2005
  2. thomson
    Replies:
    1
    Views:
    796
    Brock Allen
    Jun 11, 2005
  3. Arjen Hoekstra
    Replies:
    0
    Views:
    564
    Arjen Hoekstra
    Aug 2, 2005
  4. Praveen homkar via JavaKB.com

    how to get total number of rows contained in a resultset

    Praveen homkar via JavaKB.com, Aug 5, 2005, in forum: Java
    Replies:
    1
    Views:
    5,339
  5. yonialhadeff

    Group By Rows and Total Rows

    yonialhadeff, Jan 23, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    849
    Eliyahu Goldin
    Jan 23, 2007
Loading...

Share This Page