Paging

Discussion in 'ASP General' started by Eugene Anthony, Nov 3, 2005.

  1. I have created a table is ms sql:

    create table categories
    (
    CategoryID int IDENTITY,
    CategoryDescription varchar(30),
    ParentCategoryID int
    );

    I have also created a stored procedure in ms sql:



    I have created an asp file with the following code to perform paging:

    <!--#include file="database_Function.asp"-->
    <%create procedure sp_PagedCategories
    @Page int,
    @RecsPerPage int
    AS SET NOCOUNT ON

    CREATE TABLE #TempItems
    (
    CategoryID int IDENTITY,
    CategoryDescription varchar(30),
    ParentCategoryID int
    )

    INSERT INTO #TempItems (CategoryDescription, ParentCategoryID) SELECT
    CategoryDescription, ParentCategoryID FROM Categories

    Declare @FirstRecord int, @LastRecord int

    Select @FirstRecord = (@Page - 1) * @RecsPerPage
    Select @LastRecord = (@Page * @RecsPerPage + 1)

    SELECT *,MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE
    TI.CategoryID >= @LastRecord) FROM #TempItems WHERE CategoryID >
    @FirstRecord AND CategoryID < @LastRecord

    SET NOCOUNT OFF

    Return
    GO


    Const iRecordsPerPage = 10

    Dim currentPage
    Dim bolLastPage

    if Len(Request.QueryString("Page")) = 0 then
    currentPage = 1
    else
    currentPage = CInt(Request.QueryString("Page"))
    end if

    openDB()

    Set rs = Server.CreateObject("ADODB.Recordset")
    objConn.sp_PagedCategories currentPage, iRecordsPerPage, rs

    if not rs.EOF then

    if CInt(rs("MoreRecords"))>0 then
    bolLastPage = false
    else
    bolLastPage = true
    end if

    end if

    %>

    <TABLE>
    <% do while not rs.eof %>
    <TR><TD><a
    href="modifyCategories.asp?CategoryID=<%=rs("CategoryID")%>"><%=rs("Cate
    goryDescription")%></a></TD></TR>
    <%
    rs.movenext
    loop
    %>
    </TABLE>

    <% if currentPage > 1 then %>
    <a href="listCategories.asp?Page=<%=currentPage-1%>">Previous</a>
    <% end if %>

    <% if not bolLastPage then %>
    <a href="listCategories.asp?Page=<%=currentPage+1%>">Next</a>
    <% end if %><%
    closeRS()
    closeDB()
    %>



    Problem:

    Everything works fine. However when I change my stored procedure as in:

    create procedure sp_PagedCategories
    @Page int,
    @RecsPerPage int
    AS SET NOCOUNT ON

    CREATE TABLE #TempItems
    (
    CategoryID int,
    CategoryDescription varchar(30),
    ParentCategoryID int
    )

    INSERT INTO #TempItems (CategoryID, CategoryDescription,
    ParentCategoryID) SELECT CategoryID, CategoryDescription,
    ParentCategoryID FROM Categories

    Declare @FirstRecord int, @LastRecord int

    Select @FirstRecord = (@Page - 1) * @RecsPerPage
    Select @LastRecord = (@Page * @RecsPerPage + 1)

    SELECT *,MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE
    TI.CategoryID >= @LastRecord) FROM #TempItems WHERE CategoryID >
    @FirstRecord AND CategoryID < @LastRecord

    SET NOCOUNT OFF

    Return
    GO


    The result is different. The listing is incorrect.

    Your help is kindly appreciated.


    Eugene Anthony

    *** Sent via Developersdex http://www.developersdex.com ***
    Eugene Anthony, Nov 3, 2005
    #1
    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. Yangtsi River

    using Paging in Datagrid

    Yangtsi River, Jul 9, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    340
    Benjie Fallar
    Jul 9, 2003
  2. Saravana

    Re: Custom paging

    Saravana, Jul 10, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    417
    Saravana
    Jul 10, 2003
  3. =?Utf-8?B?UGF0cmljay5PLklnZQ==?=

    DataSet paging vs Datareader paging

    =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Oct 8, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    10,684
    Scott Allen
    Oct 8, 2004
  4. wh1974
    Replies:
    0
    Views:
    2,430
    wh1974
    Jan 12, 2005
  5. Red
    Replies:
    1
    Views:
    716
Loading...

Share This Page