Handling Null Values when Sorting

[ColinBowern]

One of our testers just caught an issue with RadGrid where Oracle is returning the data set sorting NULL values last.  RadGrid, however, is positioning null values first.  It appers to be an option on the Oracle side that can be controlled.  In our app I think the thing that makes sense is to sort the null's last.  Is it possible to modify the RadGrid behavior to treat NULL values in this manner?

Thanks,
Colin

[Iana Tsolova]

Hi Colin,

You can try setting the EnableLinqExpressions property of the grid to false and see if it makes any difference.

Additionally, in order to achieve your goal, I would suggest that you implement custom sorting for your RadGrid. You can find the below articles for more information on that matter:

http://www.telerik.com/help/aspnet-ajax/grdapplycustomsortcriteria.html
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/sort/defaultcs.aspx

Give it a try and let me know if any issues arise.

Regards,
Iana
the Telerik team

[RD]

I am also running into this issue where, by default, Oracle returns NULLs last when sorting...   However the RadGrid sorts NULLs to the top.  This causes a really ugly problem when combined with paging as the data looks incorrect.

Changing the EnableLinqExpressions property doesn't help - what is needed is a way to tell the RadGrid to sort NULLs to the bottom - is there such a setting?

[Iana Tsolova]

Hello RD,

RadGrid does not provide such biult-in setting. What you can try is to implement custom sorting for your case.
You can find more information on how to apply custom sort criteria for the grid here.

Regards,
Iana
the Telerik team

[Mani]

Hi,
What if i dont want to sort it descending or acending but i want those to add them first in the grid...?

When i add new record i wna it to be added to the first column but its adding in the last column in the last page. 
How do i get it?

Thanks.

[Pavlina]

Hi Mani,

This is actually the default behavior of RadGrid control which is data-bound control and its items are created based on the records in its datasource. However you can use TableView's InsertItemPageIndexAction property to modify it to better match your requirements. For more information how to customize the insert form display position you can refer to the second grid from this online example .

Regards,
Pavlina
Telerik

[Abhijit Shetty]

Hi,
The link you provide http://www.telerik.com/help/aspnet-ajax/grdapplycustomsortcriteria.html is broken. I have a situation to sort column having NULL value at bottom in both(ASC and DESC) order. How can i change sort expression for same?

[Pavlina]

Hi,

Please excuse me for the broken link, here is the correct one:
http://www.telerik.com/help/aspnet-ajax/grid-apply-custom-sort-criteria.html

Regards,
Pavlina
Telerik

 

 

[Hichem]

Hello,

Did anyone found a solution please ?

I'm facing the same problem. I want that the null value appears at the last of the grid when I sort ascending.

Thanks in advance.

[Attila Antal]

Hi Hichem,

There are no known problems with sorting null values. We were only making suggestions by disabling the LinqExpressions. If there is an issue, it is most likely being caused by something else. An incorrect data binding procedure, custom code that conflicts with the Grid's implementation. Before we think of this as a problem in the Grid, we should first check how the Grid is configured.

Data binding is the most essential as incorrect binding causes 70% of the problems. The best would be if you could share more details about the current setup you have. I need to see the Grid's declaration, how the data is bound, and also any other logic that interacts with the Grid. Based on that I may be able to tell you more.

 

Here is a fully working example you can test out quickly. Notice that I have marked with yellow the EnableLinqExpressions property. It will work regardless of this setting.

<telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" Width="800px" AllowSorting="true"  OnNeedDataSource="RadGrid1_NeedDataSource" EnableLinqExpressions="false">  
</telerik:RadGrid>

 

C# - Code behind. Notice my comments. There I am assigning DBNull.Value to every 3rd row in the Grid, null value to every 2nd row, and normal value to all other rows.

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    (sender as RadGrid).DataSource = OrdersTable();
}

private DataTable OrdersTable()
{
    DataTable dt = new DataTable();

    dt.Columns.Add(new DataColumn("OrderID", typeof(int)));
    dt.Columns.Add(new DataColumn("OrderDate", typeof(DateTime)));
    dt.Columns.Add(new DataColumn("Freight", typeof(decimal)));
    dt.Columns.Add(new DataColumn("ShipName", typeof(string)));
    dt.Columns.Add(new DataColumn("ShipCountry", typeof(string)));

    dt.PrimaryKey = new DataColumn[] { dt.Columns["OrderID"] };

    for (int i = 0; i < 70; i++)
    {
        int index = i + 1;

        DataRow row = dt.NewRow();

        row["OrderID"] = index;
        row["OrderDate"] = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddHours(index);
        row["Freight"] = index * 0.1 + index * 0.01;
        row["ShipName"] = "Name " + index;

        if (index % 3 == 0)
        {
            // Here is one way to assing null value
            row["ShipCountry"] = DBNull.Value;
        }
        else if (index % 2 == 0)
        {
            // Here is another way to assing null value
            row["ShipCountry"] = null;
        }
        else
        {
            // Assign a proper value
            row["ShipCountry"] = "Country " + index;
        }


        dt.Rows.Add(row);
    }

    return dt;
}

Regards,
Attila Antal
Progress Telerik