ASP.Net Code Sample -

The Best Place Yet for ASP.Net - ASPNet101.com

Power Search

Don't Miss any New Content!
Sign up for Newsletter!

	Menu
	Code Samples Tips and Tricks Tutorials ASP.Net FAQ Training Resources Hosting Recommended Books Home

Our Sponsors :

If you find this site helpful, please donate to help keep it online

Alphabetized Filtering with a DataGrid

Last Name	First Name	Address	City
Bennet	Abraham	6223 Bateman St.	Berkeley
Blotchet-Halls	Reginald	55 Hillsdale Bl.	Corvallis
Carson	Cheryl	589 Darwin Ln.	Berkeley
DeFrance	Michel	3 Balding Pl.	Gary
del Castillo	Innes	2286 Cram Pl. #86	Ann Arbor
1 2 3 4 5

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | | All Names

Back to Code Samples Main Page

(All Code Samples created with ASP Express)
Check out ASP Express for creating and editing your ASP/ASP.Net and HTML code!

Code Sample for
Alphabetized Filtering with a DataGrid

Sometimes, there's a need (let's say for an online phone book) to sort a database by the first letter of one of the database fields. For a phone book, this would normally be the last name field.

Here we have an example of doing just that. It's hitting the Authors table of the Pubs database. For this specific example, it's using SQL Server. All you'll need to do is to copy the code to your page, name the file 'alpha.aspx', change the connection string to your db connection string and run it!

See the Code Below:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.0">
	<title>Alphabetized DataGrid Filtering</TITLE>
	<Link REL=STYLESHEET HREF="/basicArial.css" TYPE="text/css">
<script language="VB" runat="server">
Dim strAlpha as string
Dim MySQL as string

Sub Page_Load(Source As Object, E As EventArgs)
strAlpha=request.Querystring("alpha") 
	If Not Page.IsPostBack Then
		BindData()
	End If
End Sub

Sub BindData()
	strAlpha=request.Querystring("alpha") 
		if strAlpha="" then 
			MySQL="Select au_lname, au_Fname, Address, City, State from Authors order by au_lname"
		else
			MySQL="Select au_lname, au_Fname, Address, City, State from Authors where " & _ 
"au_lname Like '" & strAlpha & "%" & "'"
		End If
	Dim strConn as string = "server=yourserver;uid=userid;pwd=password;database=pubs"
	Dim ds as DataSet=New DataSet()
	Dim Cmd as New SQLDataAdapter(MySQL,strConn)
	Cmd.Fill(ds,"authors")
	MyDataGrid.Datasource=ds.Tables("authors").DefaultView
	MyDataGrid.DataBind()

End Sub

Sub MyDataGrid_ItemDataBound(Source as Object, E as DataGridItemEventArgs)
	If e.Item.ItemType = ListItemType.Footer then
		Dim x as Integer
		SqlHolder.Text = string.empty
		for x=65 to 90	
			SQLHolder.text=SQLHolder.text & "<a href=""alpha.aspx?alpha=" & chr(x) & chr(34) & _
" class=""LinkClass"">" & chr(x) &  "</a>  | "
		next
		SQLHolder.text=SQLHolder.text & " | <a href=""alpha.aspx"">All Names</a>"
	End If
End Sub

Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
	MyDataGrid.CurrentPageIndex = e.NewPageIndex
	BindData
End Sub

</script>
</head>
<body>

<div align="center"><b><font Size="5">Alphabetized DataGrid Filtering</font></b> </div>
<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="12"
	BackColor="#8080FF"
	Font-Name="Arial"
	Font-Bold="True"
	Font-Size="10"
	AlternatingItemStyle-BackColor="#CFCFCF"
	AlternatingItemStyle-Font-Name="Arial"
	AlternatingItemStyle-Font-Bold="True"
	AlternatingItemStyle-Font-Size="10"
	BorderColor="Black"
	AllowPaging = "True"
	PageSize = "5"
	PagerStyle-Mode = "NumericPages"
	PagerStyle-BackColor="#F0d2F0"
	PagerStyle-HorizontalAlign="Center"
	PagerStyle-PageButtonCount = "5"
	OnPageIndexChanged = "Page_Change"
	AutogenerateColumns="False"
	OnItemDataBound="MyDataGrid_ItemDataBound"
	Width="100%">
	<Columns>
		<asp:BoundColumn DataField="au_lname" SortExpression="au_lname" HeaderText="Last Name">
                             </asp:BoundColumn>
		<asp:BoundColumn DataField="au_fname" SortExpression="au_fname" HeaderText="First Name">
                             </asp:BoundColumn>
		<asp:BoundColumn DataField="address" SortExpression="address" HeaderText="Address">
                             </asp:BoundColumn>
		<asp:BoundColumn DataField="city" SortExpression="city" HeaderText="City">
                              </asp:BoundColumn>
	</Columns>
</asp:DataGrid><div align="center"><hr>
<asp:Label id="SqlHolder" runat="server" Visible="true" /><hr>
</div>
</form>
</body>
</html>