I have gridview in which some a column has both alphabets and nulls values coming from database(oracle). But when i perform the sort operation on that column it is sorting the data in ascending with NULL values coming at the last. After some analysis i came
to know that oracle by default sorts the data with NULLs at the end.
Is there any way i can override this default behaviour?
I tried by setting orderby property of Entity Data Source like below.
<asp:EntityDataSource ID="GridDataSource" runat="server" EnableDelete="True" EnableUpdate="True"
ConnectionString="name=HQADataEntities" DefaultContainerName="HQADataEntities" OrderBy="CASE
WHEN it.[GNRC_LKUP_CATEGORY] is null THEN 0
else 1
END,it.[GNRC_LKUP_CATEGORY]"
EnableFlattening="False" EnableInsert="True" EntitySetName="GEN_LOOKUP">
But this woking on initial page load, when i perform sort by clicking the grid header it is not performing the sort correctly.
you need to set the property 'AllowSorting='true'' for your datacontrol. if you only want to set the column which you want , you can try to remove the 'OrderBy' instead set AutoGenerateOrderByClause, please refer to the link for details:
I have set the "AutoGenerateOrderByClause" to false. But on page load it was working fine, when i click on gridview header to perform the sort it is not sorting correctly the NULL values are coming at last for asceding sort.
By the way i am using Oracle database not sql. I think it wont occur with SQL database. Do you have any idea of how to achive this with Oracle database?
Try adding this to the on selecting event as the "OrderBy2 clause will only be executed on the initial load of the page but not when you click the column headers.
Private Sub GridDataSource_Selecting(sender As Object, e As System.Web.UI.WebControls.EntityDataSourceSelectingEventArgs) Handles GridDataSource.Selecting Dim et = e.DataSource et.OrderBy = "CASE WHEN it.[GNRC_LKUP_CATEGORY] IS NULL THEN 0 ELSE 1 END" End Sub
But still it is not sorting the data correctly. Are there any alternatives?
not sure I've never had to do that :( you may get better milage using QueryCreated or even use the query extender. Once we can use EF6 there is the possibility of doing this by adapting the queries as they are run.
Member
4 Points
24 Posts
Ordering Entity datasource wilth Nulls first
Feb 05, 2014 05:10 AM|Praveen Kadali|LINK
Hi,
I have gridview in which some a column has both alphabets and nulls values coming from database(oracle). But when i perform the sort operation on that column it is sorting the data in ascending with NULL values coming at the last. After some analysis i came to know that oracle by default sorts the data with NULLs at the end.
Is there any way i can override this default behaviour?
I tried by setting orderby property of Entity Data Source like below.
<asp:EntityDataSource ID="GridDataSource" runat="server" EnableDelete="True" EnableUpdate="True" ConnectionString="name=HQADataEntities" DefaultContainerName="HQADataEntities" OrderBy="CASE WHEN it.[GNRC_LKUP_CATEGORY] is null THEN 0 else 1 END,it.[GNRC_LKUP_CATEGORY]" EnableFlattening="False" EnableInsert="True" EntitySetName="GEN_LOOKUP">But this woking on initial page load, when i perform sort by clicking the grid header it is not performing the sort correctly.
Thanks,
Praveen.
orderby entitydatasource NullsfirstEntityDataSourceSort
Praveen.
All-Star
15648 Points
2151 Posts
Re: Ordering Entity datasource wilth Nulls first
Feb 05, 2014 10:38 PM|Happy Chen - MSFT|LINK
hi Praveen,
i try the demo with GridView and EntityDataSource , it is working for me.
<asp:GridView ID="GridView1" DataSourceID="EntityDataSource1" runat="server" AllowSorting="True" EnableSortingAndPagingCallbacks="True"></asp:GridView> <asp:EntityDataSource ID="EntityDataSource1" runat="server" ConnectionString="name=MyNorthwindEntities" OrderBy="CASE WHEN it.[FirstName] is null THEN 0 else 1 END,it.[EmployeeID]" DefaultContainerName="MyNorthwindEntities" EnableFlattening="False" EntitySetName="Employees" Select="it.[FirstName], it.[LastName], it.[Title], it.[DataConsegna], it.[Designation], it.[Gender], it.[EmployeeID]" AutoGenerateOrderByClause="false"></asp:EntityDataSource>you need to set the property 'AllowSorting='true'' for your datacontrol. if you only want to set the column which you want , you can try to remove the 'OrderBy' instead set AutoGenerateOrderByClause, please refer to the link for details:
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.entitydatasource.autogenerateorderbyclause(v=vs.110).aspx
Hope it helps.
orderby entitydatasource NullsfirstEntityDataSourceSort
Member
4 Points
24 Posts
Re: Ordering Entity datasource wilth Nulls first
Feb 06, 2014 12:17 AM|Praveen Kadali|LINK
Hi Happy,
I have set the "AutoGenerateOrderByClause" to false. But on page load it was working fine, when i click on gridview header to perform the sort it is not sorting correctly the NULL values are coming at last for asceding sort.
By the way i am using Oracle database not sql. I think it wont occur with SQL database. Do you have any idea of how to achive this with Oracle database?
Thanks,
Praveen.
orderby entitydatasource NullsfirstEntityDataSourceSort
Praveen.
All-Star
17916 Points
5681 Posts
MVP
Re: Ordering Entity datasource wilth Nulls first
Feb 06, 2014 06:47 AM|sjnaughton|LINK
Try adding this to the on selecting event as the "OrderBy2 clause will only be executed on the initial load of the page but not when you click the column headers.
orderby entitydatasource NullsfirstEntityDataSourceSort
Always seeking an elegant solution.
Member
4 Points
24 Posts
Re: Ordering Entity datasource wilth Nulls first
Feb 06, 2014 07:43 AM|Praveen Kadali|LINK
I tried by setting like below.
But still it is not sorting the data correctly. Are there any alternatives?
Thanks,
praveen.
orderby entitydatasource NullsfirstEntityDataSourceSort
Praveen.
All-Star
17916 Points
5681 Posts
MVP
Re: Ordering Entity datasource wilth Nulls first
Feb 06, 2014 09:46 AM|sjnaughton|LINK
not sure I've never had to do that :( you may get better milage using QueryCreated or even use the query extender. Once we can use EF6 there is the possibility of doing this by adapting the queries as they are run.
orderby entitydatasource NullsfirstEntityDataSourceSort
Always seeking an elegant solution.
All-Star
17916 Points
5681 Posts
MVP
Re: Ordering Entity datasource wilth Nulls first
Feb 06, 2014 09:48 AM|sjnaughton|LINK
also try adapting the SortExpression of the EntityDataSourceSelectingEventArgs.
orderby entitydatasource NullsfirstEntityDataSourceSort
Always seeking an elegant solution.
All-Star
15648 Points
2151 Posts
Re: Ordering Entity datasource wilth Nulls first
Feb 06, 2014 09:10 PM|Happy Chen - MSFT|LINK
I'm sorry that i don't reproduce your issue without installing Oracle database Client.
i suggest you that you try to write own codes with parameters to achieve your target in codebehind or change your sqlstatement with orderby.
Please refer to the links for details:
OrderBy Parameter type in EntityDataSource
http://forums.asp.net/t/1530224.aspx
Hope it helps.
orderby entitydatasource NullsfirstEntityDataSourceSort