Sometimes, you may find the need to change the number of rows shown on a DataGrid programmatically. The number of rows is known as the page size. This code sample shows how to do this using the Northwind Database customers table.
Just copy the code to your new page, change the connection string to your SQL Server and you’re up and running!
<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SQLClient” %>
<!– Created: 8/14/2002 12:02:04 PM –>
<html>
<head>
<meta name=”GENERATOR” Content=”ASP Express 2.0″>
<title>Change DataGrid Page Size</title>
<script language=”VB” runat=”server”>
Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
BindData
End Sub
Sub Page_Load(Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Sub changepagesize(Source as Object, E as EventArgs)
dosize
End Sub
Sub dosize()
if pagesize.selecteditem.text <>”" then
MyDataGrid.pagesize=pagesize.selecteditem.text
End If
With MyDataGrid
Try
binddata
Catch
.currentpageindex=0
finally
binddata
End Try
end with
End Sub
Sub BindData()
Dim strConn as string = “(local);uid=userID;pwd=pwd;database=Northwind”
Dim sql as string = “Select CustomerID, CompanyName, ContactName, Phone from Customers”
Dim conn as New SQLConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New SQLDataAdapter(sql,conn)
Cmd.Fill(ds,”Customers”)
MyDataGrid.Datasource=ds.Tables(“Customers”).DefaultView
MyDataGrid.DataBind()
End Sub
</script>
</head>
<body>
<div align=”center”><form runat=”server” method=”post”>
<asp:Datagrid runat=”server”
Id=”MyDataGrid”
GridLines=”Both”
cellpadding=”0″
cellspacing=”0″
Headerstyle-BackColor=”#8080C0″
Headerstyle-Font-Name=”Arial”
Headerstyle-Font-Bold=”True”
Headerstyle-Font-Size=”10″
BackColor=”#8080FF”
Font-Name=”Arial”
Font-Bold=”True”
Font-Size=”10″
BorderColor=”Black”
AllowPaging = “True”
PagerStyle-Mode = “NumericPages”
OnPageIndexChanged = “Page_Change”
AutogenerateColumns=”False”>
<Columns>
<asp:BoundColumn DataField=”CustomerID” SortExpression=”CustomerID” HeaderText=”CustomerID”></asp:BoundColumn>
<asp:BoundColumn DataField=”CompanyName” SortExpression=”CompanyName” HeaderText=”CompanyName”></asp:BoundColumn>
<asp:BoundColumn DataField=”ContactName” SortExpression=”ContactName” HeaderText=”ContactName”></asp:BoundColumn>
<asp:BoundColumn DataField=”Phone” SortExpression=”Phone” HeaderText=”Phone”></asp:BoundColumn>
</Columns>
</asp:DataGrid>
<table> <tr>
<td align=”center” valign=”Top”><b><i>Change Page Size:</i></b>
<asp:dropdownlist id=”pagesize” runat=”server” autopostback=”true” OnSelectedIndexChanged=”changepagesize”>
<asp:listitem selected=”true”></asp:listitem>
<asp:listitem>5</asp:listitem>
<asp:listitem>10</asp:listitem>
<asp:listitem>15</asp:listitem>
<asp:listitem>20</asp:listitem>
<asp:listitem>30</asp:listitem>
<asp:listitem>40</asp:listitem>
</asp:dropdownlist> <i>(number of rows shown in DataGrid)</i></td>
</tr>
</table>
</form></div>
</body>
</html>