SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot vodica's Avatar
    Join Date
    Jul 2001
    Location
    SLovenia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Limiting amount of rows displayed - Access

    Hello again!

    Anyone by any chance knows how to limit the amount of rows that are displayed in ASP.NET with the Access database. We have about 10.000 entries in the DB and are sorted by ID. We did try this:

    Code:
    SELECT * FROM entries WHERE id > 10 AND id < 100
    but the problem is it just doesn't do. We also have a field called recordListID and it's value is crucial to the data displayed ... So, two entries with following IDs like 30, 31 may have a different recordListID, for example one may have 1 and the other 2. We only need to display 30 results per page where the recordListID is 1.

    Hope you understand me

    I know that in MySQL we could just use LIMIT 10, 90 at the end of the statement but we tried it and it just doesn't work !

    Regards,
    Vodica
    while(true) sheep++;

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not just do a simple loop? Let's say you want the first 30 rows to display ...

    SQLString = "SELECT * FROM entries"
    Set objRecordSet = objConnection.Execute(SQLString)

    For x = 1 to 30

    '### whatever data you want to display here

    Next

    Set objRecordSet = Nothing


    I am assuming you want to display in a web page -- hope this will maybe help. And even if you're working straight in Access you can still use this.

    G

  3. #3
    SitePoint Zealot vodica's Avatar
    Join Date
    Jul 2001
    Location
    SLovenia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    heh, we tried this to, but at 10.000 records that takes quite some time to complete (the loop I mean)... The problem is that it's not the presentation logic that takes so much time to complete it's the query itself ...

    Thanks for your effort though,

    regards,
    Vodica
    while(true) sheep++;

  4. #4
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah. Sorry. I had a suspiscious feeling you might be several steps ahead of me there!

    I'm surprised it takes so long though. It shouldn't -- not if you're just after the next thirty records ......

    Oh well.

    G

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    in MySQL we could just use LIMIT 10, 90
    yes, that's very handy syntax, too bad it's not standard

    neither is TOP, but that's what you're looking for in Access

    TOP isn't as flexible as LIMIT but it can be used quite successfully to page through tables

    with neither TOP nor LIMIT available, the query is somewhat more complex (but it's still better than returning 10,000 rows and looping through them -- eeewwwwww!)

    for the first 30, use
    Code:
    select top 30 id
      from yourtable
     where recordListID = 1
    order by id asc
    for the next 30, you "pass in" the value of the 30th row that you got from the previous execution of the query
    Code:
    select top 30 id
      from yourtable
     where recordListID = 1
       and id > id30
    order by id asc
    if you cannot "pass in" the id value, the query is more complex -- but you have to pass some value in, and it might as well be that one

    to go the other direction, see Paging backwards through result sets

  6. #6
    SitePoint Zealot vodica's Avatar
    Join Date
    Jul 2001
    Location
    SLovenia
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you all for replying,
    rather then make complicated queries we decided in our company that it would be better and more cost efficient to change the layout of the base! (go figure ... )
    And so...

    Well, thanks for helping though
    I hope someone else find's it usefull.

    P.S> What we also found was that systax like this can be used also:

    Code:
    SELECT * FROM entries WHERE id > 0 AND id < 100
    which is basicly what you said r937 but more complicated

    Regards,
    Vodica
    while(true) sheep++;

  7. #7
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by vodica
    heh, we tried this to, but at 10.000 records that takes quite some time to complete (the loop I mean)... The problem is that it's not the presentation logic that takes so much time to complete it's the query itself ...
    getrows() GetRows() GETROWS()!!!

    Now still, to transfer data for 1000 records when only 30 are going to be displayed is a bit silly, but GetRows() will speed up loops like that (even if they're only 90 records in the database) a great deal!

    Hope that helps,
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  8. #8
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Surely a For ... Next loop doesn't transfer the data for 1000 records?! I'm still learning every day about ASP, but I didn't think that was the case -- otherwise I wouldn't do it!

    G

  9. #9
    SitePoint Guru
    Join Date
    Sep 1999
    Location
    Singapore
    Posts
    854
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by greg.harvey
    Surely a For ... Next loop doesn't transfer the data for 1000 records?! I'm still learning every day about ASP, but I didn't think that was the case -- otherwise I wouldn't do it!

    G
    No, but this:
    Code:
    Set objRecordSet = objConnection.Execute(SQLString)
    will.

    You have to limit the records returned at your statement, else your database will just hand every record over to ADO.

  10. #10
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah .... I see! Learning, learning, learning .... thanks!


Bookmarks

Posting Permissions

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