Home | 2.0 Content | Code Samples | Tips and Tricks | -- Tutorials -- | ASP.Net Books | Resources | Hosting
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 :  
 
ASP Express WebHost4Life DotNetSlackers
 
     

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


     
 
Alphabetized Filtering with a DataGrid
Last NameFirst NameAddressCity
BennetAbraham6223 Bateman St.Berkeley
Blotchet-HallsReginald55 Hillsdale Bl.Corvallis
CarsonCheryl589 Darwin Ln.Berkeley
DeFranceMichel3 Balding Pl.Gary
del CastilloInnes2286 Cram Pl. #86Ann 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

(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>
	
 
     
Home | 2.0 Content | Code Samples | Tips and Tricks | -- Tutorials -- | ASP.Net Books | Resources | Hosting
Send us your comments, questions or suggestions : Suggestions
Copyright © 2010 ASPNet101.com All Rights Reserved