
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>

<html><body>
<script language="VB" runat="server">

Sub Page_Load(sender As Object, e As EventArgs)
  ' Only bind the data on the first visit to the page
  If Not Page.IsPostBack
    BindSQL()
  End If
End Sub

'Create an variable in Global scope
Dim ResultCount as Integer


Sub BindSQL()
  Dim MyConnection As SqlConnection
  Dim DS as Dataset
  Dim MyCommand As SqlDataAdapter
  Dim sqlStr As String = "SELECT titles.title, authors.au_lname, authors.au_fname, titles.price FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titleauthor.title_id = titles.title_id"
  Dim strConn As String = "server=(local);uid=sa;pwd=;database=pubs;Trusted_Connection=yes;"
  Dim RcdCount As Integer

  'Open up our connection with our connection object
  MyConnection = New SQLConnection(strConn)

  'To execute our Sql Statement and provide out active connection
  MyCommand = New SqlDataAdapter(sqlStr, MyConnection)

  'Create instance of dataset object and fill our predetermined datagrid with it and we name it

  DS = new Dataset()
  MyCommand.Fill(DS, "pubs")

  RcdCount = DS.Tables("pubs").Rows.Count.ToString()
  Pubs.Pagesize = CInt(ps.SelectedItem.Value)

  If Not Page.IsPostback
    Pubs.CurrentPageIndex = 0
  End if

  ResultCount = RcdCount
  RecordCount.Text = "<b><font color=red>" & RcdCount & "</font> records found"

  'Now we assign the dataview to the datasource of the datagrid and we send it right on it
  Try
    Pubs.DataSource = DS
    Pubs.Databind()
  Catch e As Exception
    Pubs.CurrentPageIndex = 0
  End Try

  If Pubs.CurrentPageIndex <> 0 Then
    Call Prev_Buttons()
    Firstbutton.Visible = true
    Prevbutton.Visible = true
  else
    Firstbutton.Visible = false
    Prevbutton.Visible = false
  end if

  If Pubs.CurrentPageIndex <> (Pubs.PageCount-1) then
    Call Next_Buttons()
    NextButton.Visible = true
    Lastbutton.Visible = true
  else
    NextButton.Visible = false
    Lastbutton.Visible = False
  end if

  lblPageCount.Text = "Page " & Pubs.CurrentPageIndex + 1 & " of " & Pubs.PageCount
End Sub



Sub PagerButtonClick(sender As Object, e As EventArgs)
  'used by external paging UI
  Dim arg As String = sender.CommandArgument

  Select arg
    Case "next":
      If (Pubs.CurrentPageIndex < (Pubs.PageCount - 1)) Then
        Pubs.CurrentPageIndex += 1
      End If 

    Case "prev":
      If (Pubs.CurrentPageIndex > 0) Then
         Pubs.CurrentPageIndex -= 1
      End If

    Case "last":
      Pubs.CurrentPageIndex = (Pubs.PageCount - 1)

    Case Else:
      Pubs.CurrentPageIndex = Convert.ToInt32(arg)
  End Select

  BindSQL()
End Sub



Sub Prev_Buttons()
  Dim PrevSet As String

  If Pubs.CurrentPageIndex+1 <> 1 and ResultCount <> -1 Then
    PrevSet = Pubs.PageSize
    PrevButton.Text = ("< Prev " & PrevSet)
	
    If Pubs.CurrentPageIndex+1 = Pubs.PageCount Then
      FirstButton.Text = ("<< 1st Page")
    End If
  End If
End Sub


Sub Next_Buttons()
  Dim NextSet As String

  If Pubs.CurrentPageIndex+1 < Pubs.PageCount Then
    NextSet = Pubs.PageSize
    NextButton.Text = ("Next " & NextSet & " >")
  End If

  If Pubs.CurrentPageIndex+1 = Pubs.PageCount-1 Then
    Dim EndCount As Integer = ResultCount - (Pubs.PageSize * (Pubs.CurrentPageIndex+1))
    NextButton.Text = ("Next " & EndCount & " >")
  End If
End Sub


Sub Repage(sender As Object, e As EventArgs)
  Pubs.CurrentPageIndex = 0
  BindSQL()
End Sub
</script>


  <asp:Label id="lblPageCount" runat="server" /><br>
  <asp:label id="RecordCount" runat="server" />

  <form runat="server">
    <ASP:Datagrid id="Pubs" runat="server" 
              AllowPaging="True" 
              AllowCustomPaging="False" 
              PagerStyle-Visible = "False"
              HeaderStyle-BackColor="Blue"
              HeaderStyle-ForeColor="White"
    />

    <asp:linkbutton id="Firstbutton" Text="<< 1st Page" CommandArgument="0" runat="server" onClick="PagerButtonClick"/>
    <asp:linkbutton id="Prevbutton" Text= "" CommandArgument="prev" runat="server" onClick="PagerButtonClick"/>
    &nbsp;&nbsp;

    <asp:linkbutton id="Nextbutton" Text= "" CommandArgument="next" runat="server" onClick="PagerButtonClick"/>
    <asp:linkbutton id="Lastbutton" Text="Last Page >>" CommandArgument="last" runat="server" onClick="PagerButtonClick"/>

    <br><br><br><br>

    Change Pagesize
    <asp:DropDownList id="ps" runat="server">
      <asp:ListItem>4</asp:ListItem>
      <asp:ListItem>5</asp:ListItem>
      <asp:ListItem>7</asp:ListItem>
      <asp:ListItem selected>10</asp:ListItem>
      <asp:ListItem>12</asp:ListItem>
      <asp:ListItem>15</asp:ListItem>
      <asp:ListItem>22</asp:ListItem>
    </asp:DropDownList>
    
    <asp:button text="Change Pagesize" runat="server" OnClick="RePage"/>
  </form>
</body>
</html>