Creating a Reverse Order for a DataGrid in ASP.Net using vb code

I searched high and dry for a code example of this, so here is the scenario: I have a calendar that (of course) can record any number of appointments, but, I only wanted to show the most recent 4 entries on a home page. So easy enough I tell the SQLDatasource to give me the top 4 based on the start date in descending order. Now the problem: well, since I'm displaying the entries in a templated gridview I want the next four events listed from the next one to the last one (reverse order).

My solution: Here is the Code from the web page (aspx):

<asp:HiddenField ID="CurrentDate" runat="server" />
<asp:SqlDataSource ID="AppointmentData" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT top 4 [StartDate], [Subject], [UniqueId] FROM [Appointments] WHERE ([StartDate] &gt;= @StartDate) ORDER BY [StartDate] desc">
        <asp:ControlParameter ControlID="CurrentDate" Name="StartDate" PropertyName="Value" Type="DateTime" />
<asp:DataGrid ID="AppointDataGrid" runat="server" DataSourceID="AppointmentData" AutoGenerateColumns="False" ShowHeader="False" Style="margin-right: 31px" Width="200px" BorderStyle="None" BorderWidth="0px" GridLines="None" PageSize="4" AllowSorting="True">
                <ul class="list-style2">
                    <li class="first">
                        <asp:HiddenField ID="StartDate" runat="server" Value='<%# Eval("StartDate")%>' />
                        <asp:HiddenField ID="Subject" runat="server" Value='<%# Eval("Subject")%>' />
                        <strong class="date" style="padding-right: 5px"><span class="month"><%# DataBinder.Eval(Container.DataItem, "StartDate", "{0:MMM}")%></span> <span class="day"><%# DataBinder.Eval(Container.DataItem, "StartDate", "{0:dd}")%></span></strong>
                        <%# Eval("Subject")%>

Here is the code behind:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            CurrentDate.Value = Now
        End If
    End Sub

    Sub Get_GridData_RVS()
        Dim ds As New DataSet()
        Dim dt As New DataTable()
        dt.Columns.Add("StartDate", GetType(Date))
        dt.Columns.Add("Subject", GetType(String))
        For i = AppointDataGrid.Items.Count - 1 To 0 Step -1
            Dim nw As DataRow = ds.Tables(0).NewRow()
            nw("StartDate") = Convert.ToDateTime(DirectCast(AppointDataGrid.Items(i).FindControl("StartDate"), HiddenField).Value)
            nw("Subject") = DirectCast(AppointDataGrid.Items(i).FindControl("Subject"), HiddenField).Value
        AppointDataGrid.DataSourceID = Nothing
        AppointDataGrid.DataSource = ds
    End Sub

Which Looks Like:


Now some explaining:

  1. I first layout my SQLData source and attach my database criteria.
  2. I add a hidden filed to store todays date in.
  3. I add the datagrid and create the template column for displaying the data as if it wasn't in a datagrid at all.
  4. In the page load event I store todays date to the hidden field (I added in step 2), and bind the data to the SQLDatasource.
  5. Call the code to change the order of the data and bind that changed data to the grid.
  6. The code that changes the order creates a temporary data table, then we loop through the datagrid in reverse order and load the data table with that data, then it binds the data table to the datagrid and "wha la" done!!!

I know this will help someone, because there were so many questions on Google about it and no real complete answers, just partial answers.

Add comment