<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<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
GetAuthors("au_fname asc")
End If
End Sub
Sub GetAuthors(sSortStr as string)
Dim cn as SQLConnection
Dim cmd as SQLCommand
Dim rdr as SQLDataReader
Dim sConnectString as String = ConfigurationSettings.AppSettings("connectionStringPubs")
Dim sSql as String = "SELECT au_fname, au_lname, city, state, zip from authors order by " & sSortStr
' Connect to the database
cn = New SQLConnection(sConnectString)
cn.open()
' execute the SQL
cmd = New SQLCommand(sSQL, cn)
'
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Associate the data grid with the data
DispAuthors.DataSource = rdr
DispAuthors.DataBind()
End Sub
' this method is called when user clicks on any of the column headings
Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs)
Dim SortExprs() As String
Dim CurrentSearchMode As String, NewSearchMode As String
Dim ColumnToSort As String, NewSortExpr as String
' Parse the sort expression - delimiter space
SortExprs = Split(e.SortExpression, " ")
ColumnToSort = SortExprs(0)
' If a sort order is specified get it, else default is descending
If SortExprs.Length() > 1 Then
CurrentSearchMode = SortExprs(1).ToUpper()
If CurrentSearchMode = "ASC" Then
NewSearchMode = "Desc"
Else
NewSearchMode = "Asc"
End If
Else ' If no mode specified, Default is descending
NewSearchMode = "Desc"
End If
' Derive the new sort expression.
NewSortExpr = ColumnToSort & " " & NewSearchMode
' Figure out the column index
Dim iIndex As Integer
Select case ColumnToSort.toUpper()
case "AU_FNAME"
iIndex = 0
case "AU_LNAME"
iIndex = 1
case "STATE"
iIndex = 3
End Select
' alter the column's sort expression
DispAuthors.Columns(iIndex).SortExpression = NewSortExpr
' Sort the data in new order
GetAuthors(NewSortExpr)
End Sub
</script>
<form runat="server">
<ASP:Datagrid id="DispAuthors" runat="server" AutoGenerateColumns="False"
AllowSorting="true"
onSortCommand="SortAuthors">
<AlternatingItemStyle BackColor="Silver"></AlternatingItemStyle>
<ItemStyle Font-Size="Smaller" Font-Names="helvatica" VerticalAlign="Top"></ItemStyle>
<HeaderStyle Font-Size="Medium" Font-Names="Arial" Font-Bold="True" ForeColor="Teal"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="au_fname" SortExpression="au_fname ASC" HeaderText="First Name" />
<asp:BoundColumn DataField="au_lname" SortExpression="au_lname" HeaderText="Last Name" />
<asp:BoundColumn DataField="city" HeaderText="City" />
<asp:BoundColumn DataField="state" SortExpression="state" HeaderText="State" />
<asp:BoundColumn DataField="zip" HeaderText="Zip" />
</Columns>
</asp:datagrid>
</form>
|