<%@ 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"/>
<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>
This article was originally published on 4guysfromrolla.com.
Comments