Last post Feb 06, 2014 09:10 PM by Happy Chen - MSFT
Feb 05, 2014 05:10 AM|Praveen Kadali|LINK
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
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.
Feb 05, 2014 10:38 PM|Happy Chen - MSFT|LINK
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"
WHEN it.[FirstName] is null THEN 0
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:
Hope it helps.
Feb 06, 2014 12:17 AM|Praveen Kadali|LINK
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?
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.
Feb 06, 2014 07:43 AM|Praveen Kadali|LINK
I tried by setting like below.
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?
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.
Feb 06, 2014 09:48 AM|sjnaughton|LINK
also try adapting the SortExpression of the EntityDataSourceSelectingEventArgs.
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