Paging using Datasets and DataAdapters (1 Viewer)

Simon_MT

Registered User.
Local time
Today, 14:30
Joined
Feb 26, 2007
Messages
2,177
To start each Page has a Page Reference in the QueryString in this OG so we test for LESS THAN 0:

Code:
    If Request.QueryString("OG") < 0 Then
        intPage = 1
    Else
        intPage = Request.QueryString("OG")
    End If
We will later test for Page GREATER THAN PageCount

Then declare variable from strGrouping from Request.QueryString and some counters
Code:
    strGrouping = Request.QueryString("Grouping")
    Dim intCurrentRecord As Integer = 0
    Dim intRecordPos As Integer = 1
    Dim intPageSize As Integer = 6
Construct Connection String and SQL statement. Note that Microsoft.Jet.OLEDB.4.0 is no longer available on some hosting sites.
Code:
    connString = "Provider=Microsoft.ACE.OLEDB.12.0;DATA SOURCE= " & Server.MapPath(".\Data\DbName.mdb") & ";Persist Security Info=False;"
Code:
   rsSql = "SELECT * FROM OriginalsGrouping WHERE OriginalsGrouping.[Artist Grouping] = '" & strGrouping & "'"
Then populate the DataSet and DataAdapter
Code:
    Using conn As New OleDbConnection(connString)
        conn.Open()

        Dim da As OleDbDataAdapter = New OleDbDataAdapter(rsSql, conn)
        Dim ds As DataSet = New DataSet()

        da.SelectCommand.CommandText = rsSql
        da.Fill(ds, "OriginalsGrouping")
Count the Rows
Code:
        intRowCount = ds.Tables(0).Rows.Count
Now determine the Number of Pages, the trick here that as there 6 rows/records per page a new is required on the 7/13/19/25 etc records. Therefore 7+5, 13+5, 19+5, 25+5 need a new Page.
Code:
        intPageCount = Int((intRowCount + (intPageSize - 1)) / intPageSize)
Now we test for Request.QueryString("OG") exceeding the number of actual Pages
Code:
        If intPage > intPageCount Then
            intPage = intPageCount
        End If
We now set-up the Variables for the For statement
Code:
        intStart = intPageSize * (intPage - 1)
        intFinish = intStart + (intPageSize - 1)
Here before the For Statement go the Doc Type and Header MetaTags Body declaration and any container declarations, I use divs.
now for the For statement and counters
Code:
    For intRecord = intStart To intFinish
        intRecordPos = intRecord + 1
        intCurrentRecord = intCurrentRecord + 1
At the end of Page including the Exit on the last Row/Record.
Code:
End If
If intRecordPos = intRowCount Then
    Exit For
End If
Next
da.Dispose()
conn.Close()
End Using
If you are using right hand side navigation then all you need to do is wait until the Rows/Records have displayed and do this and then inculde the navigational links:
Code:
<%  If intCurrentRecord = intPageSize Or intRecordPos = intRowCount Then%>
</div>
<div containernav>
These have been declared form the at the beginning :
strGrouping = ds.Tables(0).Rows(0).Item(0).ToString
strGroupingDesc = ds.Tables(0).Rows(0).Item(1).ToString

To add arrows to navigate between pages using Back and Forward arrows and to remove the appropraite arrows on the first and last page:
Code:
<%  If CInt(intPage) > 1 Then%>
<a href="<%=Session("Link")%>/Originals_Groupings.aspx?Grouping=<%=strGrouping%>&OG=<%=intPage-1%>" title="<%=Application("keyweb")%> <%=strGroupingDesc%> Large Page <%=intPage-1%>">
<img class="imagenavleft" src="<%=Session("Link")%>/include/back.png" width="11" height="12" alt="<%=Application("keyweb")%> <%=strGroupingDesc%> Large Page <%=intPage-1%>" /></a>
<%  Else%>
<img class="imagenavleft" src="<%=Session("Link")%>/include/fill.gif" width="16" height="12" alt="" />
<%  End If%>
<%  If CInt(intPage) < CInt(intPageCount) Then%>
<a href="<%=Session("Link")%>/Originals_Groupings.aspx?Grouping=<%=strGrouping%>&OG=<%=intPage+1%>">
<img class="imagenavright" src="<%=Session("Link")%>/include/forward.png" width="11" height="12" alt="<%=Application("keyweb")%> <%=strGroupingDesc%> Large Page <%=intPage+1%>" /></a>
<%  Else%>
<img class="imagenavright" src="<%=Session("Link")%>/include/fill.gif" width="16" height="12" alt="" />
<%  End If%>
<div class="cell_nav2_record">
<div class="inforecord">(<%=intStart+1%>-<%=intRecordPos%> of <%=intRowCount%>)</div>
</div>

To see how this works here is the actual page:

http://www.trevorsutton.com/Originals_Groupings.aspx?Grouping=09TSP&OG=1

I forgot to mention to go back to the correct Originals_Grouping.aspx page no matter if you have tranversed pages in the Originals_Grouping_Large.aspx again using 6 images per page

Code:
        Session("OG") = Int((intPage - 1) / 6) + 1

and

Code:
<a href="<%=Session("Link")%>/Originals_Groupings.aspx?Grouping=<%=strGrouping%>&OG=<%=Session("OG")%>" title="<%=Application("keyweb")%> <%=strGroupingDesc%> Page <%=Session("OG")%>">

Simon
 
Last edited:

Users who are viewing this thread

Top Bottom