<%@ 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>