Complete DataGrid Sorting Demo

This demo shows a complete DataGrid sorting demo. Note that you can sort by both the Views and Date Added columns. One thing you may notice is that when sorting by the Date Added it shows FAQs that are not one of the top 10 most popular FAQs. That is because my SQL query is rather naive and just grabs the TOP 10 records specified via the ORDER BY. One could argue that the semantics of the sort of the 10 most popular FAQs by date added would show the same 10 most popular FAQs ordered by their date added, which indeed makes sense. This could have easily been accomplished using a derived table or a stored procedure that uses a temporary table. In either case, the purpose of this article and demo was to show how to do sorting with a DataGrid, and not to get bogged down with the complexities of SQL.


Category NameFAQ DescriptionViewsAuthorAuthor's EmailDate Added
ASP.NETHow can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency.964,702Scott Mitchellmitchell@4guysfromrolla.com01-19-2002
ArraysHow can I convert a Recordset into an array? Also, how can I convert an array into a Recordset?238,089Scott Mitchellmitchell@4guysfromrolla.com06-14-2001
Databases, ErrorsI am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem?233,530Scott Mitchellmitchell@4guysfromrolla.com01-17-2001
ArraysHow can I quickly sort a VBScript array? 230,033Richard Lowechadich@yahoo.com11-12-2000
ArraysHow do I display data on a web page using arrays instead of Do...While...MoveNext...???...186,006Scott Mitchellmitchell@4guysfromrolla.com04-11-2002
Databases, QueriesHow can I find out if a record already exists in a database? If it doesn't, I want to add it.183,511Steve Ciminosteve_cimino@hotmail.com12-14-2000
FileSystemObjectWhen I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... 168,750Bill Wilkinsonbill@ClearviewDesign.com01-22-2001
Session ObjectFor session variables to work, must the Web visitor have cookies enabled?133,635Scott Mitchellmitchell@4guysfromrolla.com09-26-2000
EmailCan I send emails without using CDONTS?130,322Scott Mitchellmitchell@4guysfromrolla.com09-24-2000
StringsHow can I remove multiple spaces between words in a string? That is, if I have: Hi    there how can I get: Hi there?129,557Scott Mitchellmitchell@4guysfromrolla.com03-31-2001


Source Code
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    If Not Page.IsPostBack then
      BindData("F.ViewCount DESC")
    End If
  End Sub
	
	
  Sub BindData(sortExpr as String)
    '1. Create a connection
    Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    '2. Create the command object, passing in the SQL string
    Dim strSQL as String = "SELECT TOP 10 FAQID, F.Description, F.ViewCount, F.FAQCategoryID, " & _ 
                             "    SubmittedByName, SubmittedByEmail, DateEntered, CatName = C.Name " & _
                             "FROM tblFAQ F (nolock)  " & _
                             "INNER JOIN tblFAQCategory C (nolock) ON  " & _
                             "  C.FAQCategoryID = F.FAQCategoryID  " & _
                             "WHERE Approved=1 ORDER BY " & sortExpr 

    Dim myCommand as New SqlCommand(strSQL, myConnection)

    'Set the datagrid's datasource to the datareader and databind
    myConnection.Open()
    dgPopularFAQs.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    dgPopularFAQs.DataBind()	
  End Sub
  
  Sub SortResults(sender as Object, e as DataGridSortCommandEventArgs)
	BindData(e.SortExpression)
  End Sub
</script>

<form runat="server">
<asp:label id="lblWhatClicked" runat="server" Font-Bold="True" />
<p>
<asp:DataGrid runat="server" id="dgPopularFAQs" 
                BackColor="#eeeeee" Width="85%"
                HorizontalAlign="Center"
                Font-Name="Verdana" CellPadding="4"
                Font-Size="10pt" AutoGenerateColumns="False"
                AllowSorting="True"
                OnSortCommand="SortResults">
  <HeaderStyle BackColor="Black" ForeColor="White" Font-Bold="True" HorizontalAlign="Center" />
  <AlternatingItemStyle BackColor="White" />
  
  <Columns>
    <asp:BoundColumn DataField="CatName" HeaderText="Category Name"  />
    <asp:BoundColumn DataField="Description" HeaderText="FAQ Description" />
    <asp:BoundColumn DataField="ViewCount" SortExpression="F.ViewCount DESC" 
           DataFormatString="{0:#,###}" 
           HeaderText="Views" ItemStyle-HorizontalAlign="Center" />
    <asp:BoundColumn DataField="SubmittedByName" HeaderText="Author"  />
    <asp:BoundColumn DataField="SubmittedByEmail" HeaderText="Author's Email"  />
    <asp:BoundColumn DataField="DateEntered" SortExpression="DateEntered DESC" 
            HeaderText="Date Added"	DataFormatString="{0:MM-dd-yyyy}"  />    
  </Columns>
</asp:datagrid>
</form>
	


[Return to the article]