#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    39
    Rep Power
    5

    Pagination with the database records


    Hi
    Can anyone help me with the databse recirds pagination in asp.
    here is the code i have used.
    but it is showing an error at the end.
    can anyone give simple example and expalin me what it is?
    I don't know anything about pagination.
    I have used sample code given in an another website....
    getting an error at End if



    <% @ Language = VBScript %>
    <!-- #INCLUDE FILE="include/adovbs.asp" --> [db connection and functions described here]
    <!-- #INCLUDE FILE="include/functions.asp" -->
    <html>

    <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type" >
    <title>Items </title>
    </head>

    <LINK href="include/default.css" type=text/css rel=stylesheet>

    <body>
    <div align="center">

    <form method="post" action="items.asp" name="form1">
    <p>&nbsp;</p>
    <p><br>
    </p>
    <br>
    </p>
    <table border="2" width=50%>
    <th colspan="3">Items used</th>
    <tr><th>Itemid</th>
    <th>Item</th>
    <th>quantity </th>
    <%
    Dim ipagesize
    Dim ipagecount
    Dim ipagecurrent
    Dim strorderby
    Dim irecordsshown
    dim i
    OpenSQLConn
    ipagesize=10

    if Request.QueryString("page")="" Then
    ipagecurrent=1
    else
    ipagecurrent=cint(Request.QueryString("page"))
    end if



    sql_query="Select * from items"

    Set rs=Conn.Execute(sql_query)
    rs.Pagesize = ipagesize
    ipagecount=rs.pagecount
    if ipagecurrent > ipagecount then
    ipagecurrent=ipagecount
    if ipagecurrent < 1 then
    ipagecurrent=1

    If ipagecount=0 then
    Response.Write("No records found")
    else
    rs.Absolutepage=ipagecurrent
    %>
    <font size="+1">page<strong><%=ipagecurrent%></strong>
    <%
    Response.Write vbcrlf
    Response.write "<table border=""1"">"&vbcrlf
    Response.Write vbtab&"<tr>"&vbcrlf
    for i=0 to rs.fields.Count -1
    Response.Write vbtab & vbtab &"<th>"
    Response.write rs.Fields(i).Name
    Response.Write "</th>" &vbcrlf
    Next
    Response.Write vbtab &"</tr>"&vbcrlf

    irecordshown=0
    Do while irecordshown < ipagesize and not rs.EOF
    Response.Write vbtab& "<tr>" &vbcrlf
    for i=0 to rs.Fields.count -1
    Response.Write vbtab &vbtab& "<td>"
    Response.Write rs.Fields(i)
    Response.Write "</td>"&vbcrlf
    Next
    Response.Write vbtab &"</tr>"&vbcrlf
    irecordsshown=irecordsshown+1
    rs.MoveNext
    Loop
    Response.Write"</table>" &vbcrlf
    End If

    %>
    <a href="items.asp?page=<%=ipagecurrent-1%></a>
    End If
    For i=1 to ipagecount
    if i=ipagecurrent then
    %>
    <%=i%><%
    else %>
    <a href="items.asp?page=<%=i%></a>
    <% End if



    If ipagecurrent < ipagecount then
    %>
    <a href="items.asp?page=<%= ipagecurrent +1 %>
    </a>
    <%End If %>
    <%
    do while not rs.EOF
    %>

    <tr><td><%=rs("itemid")%></td>
    <td><%=rs("item")%></td>
    <td><%=rs("quantity")%></td>
    <%rs.MoveNext%>
    </tr>

    <%
    loop

    rs.close
    Conn.close

    %>

    </table>

    <table>
    <tr>
    <td><a href="add.asp">Add Item</a></td>
    </tr>
    </table>
    <p>&nbsp;</p>
    <p>
    <input type="submit" value="Back">
    <input type="submit" value="Cancel">
    </p>
    </form>
    </div>
    </body>

    </html>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    39
    Rep Power
    5
    I got result which i need...
    But here my problem is page: 1,2,3,back,Next is displaying at the top of the page instead of bottom..

    Can anyone give an idea about this..to print the page:1,2 to next on the bottom of the page



    <% @ Language = VBScript %>
    <!-- #INCLUDE FILE="include/adovbs.asp" -->
    <!-- #INCLUDE FILE="include/functions.asp" -->
    <html>

    <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type" >
    <title>Items</title>
    </head>

    <LINK href="include/default.css" type=text/css rel=stylesheet>

    <body>

    <div align="center">

    <form method="post" action="item.asp" name="form1">
    <p>&nbsp;</p>
    <p><br>
    </p>
    <br>
    </p>
    <table cellspacing="0" cellpadding="1" width="950" bgcolor="#006d31" border="0" align="center">
    <tbody>
    <tr>
    <td><table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#fbf3c5" style="WIDTH: 100%">
    </table>
    <table border="1" >
    <tr><td colspan="7"><span class="PageHeader">items list</span></td></tr>

    <tr><th>Itemid</th>
    <th>Item</th>
    <th>Unit</th>
    <tfoot align="right">
    <tr>
    <%
    OpenSQLConn
    Dim iPageSize
    Dim iPageCount
    Dim iPageCurrent
    Dim strOrderBy
    Dim iRecordsShown
    Dim iFieldCount
    Dim iRecordCount
    Dim LoopRecordCount
    Dim pageNum
    Dim counter
    Dim markShowPage
    Dim I, J
    iPageSize = 12
    If Request.QueryString("page") = "" Then
    iPageCurrent = 1
    Else
    iPageCurrent = CInt(Request.QueryString("page"))
    End If

    strSQL = "SELECT * from items"

    Set rs = Server.CreateObject("ADODB.Recordset")

    rs.PageSize = iPageSize
    rs.CacheSize = iPageSize
    rs.Open strSQL, Conn, adOpenStatic, adLockReadOnly, adCmdText

    iPageCount = rs.PageCount
    iFieldCount = rs.Fields.Count
    iRecordCount = rs.RecordCount

    If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
    If iPageCurrent < 1 Then iPageCurrent = 1

    If iPageCount = 0 Then
    Response.Write "No records found!"
    Else
    rs.AbsolutePage = iPageCurrent

    Dim arrDBData
    ReDim arrDBData(iFieldCount, iRecordCount)
    LoopRecordCount = 0

    'arrDBData = rs.GetRows()

    Do While LoopRecordCount < iPageSize And Not rs.EOF
    For I = 0 To rs.Fields.Count - 1
    arrDBData(I, LoopRecordCount) = rs.Fields(I)
    Next
    LoopRecordCount = LoopRecordCount + 1
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Conn.Close

    End If

    iRecordCount = iRecordCount - 1
    iFieldCount = iFieldCount - 1
    LoopRecordCount = LoopRecordCount - 1

    pageNum = Round(iRecordCount/iPageSize)
    If pageNum < (iRecordCount/iPageSize) Then pageNum = pageNum + 1

    Response.Write "&nbsp;&nbsp;<b>Page: "
    For counter = 1 To pageNum
    if counter=iPageCurrent then
    Response.Write "&nbsp;<span class=activepage>&nbsp;"
    Response.Write counter
    Response.Write "&nbsp;</span>"
    markShowPage=1
    else
    Response.Write "&nbsp;<a onclick=exitoff() href=item.asp?page="
    Response.Write counter
    Response.Write " class=listingLink>"
    Response.Write counter
    Response.Write "</a>"
    end if
    Next
    Response.Write "</b>"

    Response.Write "&nbsp;&nbsp;"

    Response.Write "<b>"

    Response.Write "<a href=item.asp?page="
    If iPageCurrent = 1 Then
    Response.Write pageNum
    else
    Response.Write iPageCurrent-1
    end if
    Response.Write " class=listingLink>Back</a>&nbsp;&nbsp;"
    Response.Write "<a href=item.asp?page="
    If iPageCurrent = pageNum Then
    Response.Write "1"
    else
    Response.Write iPageCurrent+1
    end if
    Response.Write " class=listingLink>Next</a>"

    Response.Write "</b>"
    %></td></tr></tfoot>
    <%


    For I = 0 To LoopRecordCount
    Response.Write "<tr>" & vbCrLf
    For J = 0 To ifieldCount
    Response.Write vbTab & "<td>" & arrDBData(J, I) & "</td>" & vbCrLf
    Next ' J
    Response.Write "</tr>" & vbCrLf
    Next ' I


    'Const adOpenStatic = 3
    'Const adLockReadOnly = 1
    'Const adCmdText = &H0001


    %>
    </table>
    </td></table>
    <p>

    </table>
    <table>
    <tr>
    <td><a href="add.asp">Add New </a></td>

    </tr>
    </table>
    </form>
    </body>
    </html>
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    Look at your html page source in your browser and see what's wrong with the html for the page. Once you determine what's wrong in the html, adjust your asp code to output the correct html to the browser.

    As far as what html is wrong, something to do with tables or css positioning or something would be my guess where to look.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    39
    Rep Power
    5
    will look for that doug..Thank you..

IMN logo majestic logo threadwatch logo seochat tools logo