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