Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: How to create sql for retrieving 10 records per time in .NET

    I want to create sql for retrieving 10 records per time but need to know the total result matches.
    And I have previous and next button for retrieving previous or next 10 records.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a great man once said: Huh?

    I'm sure that your posting made sense to you, but I don't even have a clue what you meant. Could you try to repost and fill in a few of the details that I'm missing? I'd like to help you if I could, but at the moment I don't understand enough of what you want to have a chance!

    -PatP

  3. #3
    Join Date
    Jul 2004
    Posts
    13

    Smile Editing title (my God this site makes it take extra time to post!!)

    Hi,

    as Pat said, this isn't very clear and you really should repost. I just want to comment on what you may need to consider mentioning.

    Almost all databases are used by multiple users at the same time. Selecting the 10 first records in a particular order makes perfect sense, as does selecting record 11 to 20 of that same recordset. But selecting 10 and then (at a later time) selecting "the ten next" doesn't have any clear meaning since the data may well have changed in the meantime. So perhaps it's a good idea to clarify how you want your system to deal with the different change scenarios: records may have been added, removed or updated, or any mix of the three between the two queries.

    One way to "page in the database":
    Initialize:
    - establish a single orderby column for your query (if you have several columns, combine them into one column yielding equivalent sort order)
    - select the top 10 records
    - persist the value of the orderby column for the last record. In ASP.NET, use ViewState for this.

    Each round trip:
    - use the value of the last record in the previous result set to select the next ten:
    SELECT TOP 10 ... WHERE ... AND ordCol > @lastValue ORDER BY ordCol
    - persist the last value again

    Of course, if you require to page the other way (previous page) you must also persist the value of the first record. Furthermore, since there's no BOTTOM keyword to mirror TOP, you must use reverse sort order (otherwise, if you did SELECT TOP 10 ... WHERE ordCol < @firstVal, the first page would be returned even if you tried to get the previous one from, say, page 5). Since you still want to display in-order, fix this by selecting into a temp table and then select in-order from that one:

    SELECT TOP 10 INTO #page ... WHERE ... AND ordCol < @firstVal ORDER BY ordCol DESC
    SELECT * FROM #page ORDER BY ordCol

    I hope this helps!

    Dag
    Last edited by dagjo; 08-04-04 at 11:09. Reason: Forgot to put the ORDER BY in the nextpage query.

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Or you could just set-up paging in a datagrid...

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Huh?

    Just a guess...

    You want to do paging

    http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx

    Make sure to say hi to Jeff for me
    __________________
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    I meant that you could use a .NET DataGrid control that allows you to view a recordset one page at a time. You can define how many records you want displayed on each page. Of course this deals with paging at the Application level - don't know if that's what he wants but just thought I'd mention it.

    I don't have a clue about paging at the SQL Server level...:-)

  7. #7
    Join Date
    Jul 2004
    Posts
    3
    Sorry for unclear question. Yes, what i want to do is paging.
    Brett: The URL http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx is unavailable
    mmcdonald: Thanks for your suggestion but the user don't want the result be shown in datagrid

Posting Permissions

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