Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: How will i know how many rows are there in a table?

    adoRS.Open "select * from table_name"

    numField = adoRS.Fields.Count
    response.write("Number of Filed of table_name = "&numFiled)

    my question is....

    how will i know how many rows are there in table_name?
    is there a

    numRows = adoRS.Rows.Count

    any help with this?...... tnx

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    You could either do it in a select statement:
    Code:
    SELECT Count(ID) AS TotalRows FROM table_name
    (where "ID" is your key field)

    Or you could try:
    Code:
    adoRS.MoveLast
    adoRS.MoveFirst
    numRows = adoRS.RecordCount
    Or the brute force way:
    Code:
    If NOT adoRS.BOF AND NOT adRS.EOF Then
      WHILE NOT adoRS.EOF
        numRows = numRows + 1
        adoRS.MoveNext
      WEnd
      adoRS.MoveFirst
    End If
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108
    Seppuku,

    Why do you have to do a MoveLast, then Movefirst before doing the count, in your second example?

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    because he hasn't specified the recordset cursor type or the database he is going to so he doesn't know what how the recordcount property will behave...

    If you move to the last record in most cases you can be sure your recordcount will be accurate.

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Right. I was playing it safe.
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Apr 2002
    Posts
    4
    Using the recordcount property of the recordset object you can find out the number of records in a table.

  7. #7
    Join Date
    Apr 2002
    Posts
    4
    Using the recordcount property of the recordset object, you can find out the number of records in a table.

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    That has already been stated asdf.
    That which does not kill me postpones the inevitable.

  9. #9
    Join Date
    Dec 2003
    Posts
    454
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.ConnectionString = MyConnectionString
    Conn.Open
    Set RS = Server.CreateObject("ADODB.RecordSet")
    RS.CursorLocation = adUseClientBatch
    SQL = "SELECT * FROM MyTable"
    RS.Open strSQL, Conn, adOpenStatic
    TotalRowNumber = RS.RecordCount
    ...
    Set RS = Nothing
    Set Conn = Nothing

  10. #10
    Join Date
    Oct 2003
    Posts
    706
    The bottom line is:
    • If you want to know the number of rows in a table, use SELECT COUNT(*) FROM tablename.
    • If you have issued a query and want to know how many rows are in the result, you must use MoveLast before examining the Count. The reason is that the SQL-engine might deliver the first page of results to you before it has actually finished the entire query.

    If you can possibly avoid it, don't go for that record count. Not unless you need it for some purpose other than giving the user an "interesting" number. Either (a) you are running a redundant query (and an expensive one at that), or (b) you're forcing the user to wait for full query completion (and... forcing the DBMS to actually complete it) ... all to produce, "an interesting but expensive number."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •