locked
Sort Grid on Custom Property RRS feed

  • Question

  • User-418270074 posted

    Hi,

    One of the fields in my tables is Ranking, it is basically there to promote items to the top of lists (other items are sorted alphabetically). Now it may well be that my logic is flawed here, but in order to make things easiest for the people who are going to use it I made a ranking of 1 be the highest value (will show up at the top) and 2 the second (will show up second) and so on.

    The problem I now have is that when I sort by ranking it sorts them by the un-ranked items first (null), then any ranked 1, any ranked 2 etc etc. Or the opposite way around if sorted desc. What I would ideally like is for the items to be sorted 1, 2, 3, 4... and then null.

    Essentially I would like the equivalent of a SQL ORDER BY ISNULL(Ranking, 255). I've tried implementing the GridViews on_sorting method but I can't quite figure out how I might do this. I've tried adding an new Property to the Entity but that didnt work due to it not knowing how to create the SQL for that property, and I tried setting the e.SortExpression to ISNULL(..) but was told the Entity didnt have this method. I can totally understand why I got these errors, but does anyone know if anything like this is possible?

    Thanks

    Rob 

    Tuesday, August 12, 2008 12:07 PM

Answers

  • User-330204900 posted

    The computed column would look like this

    [SortRank]  AS (isnull([Ranking],(255)))

    See the full listing for creating the table dbo.Test:

    USE [Northwind]
    GO
    
    /****** Object:  Table [dbo].[Test]    Script Date: 08/12/2008 22:21:31 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Test](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Test] [nvarchar](50) NOT NULL,
    	[Ranking] [int] NULL,
    	[SortRank]  AS (isnull([Ranking],(255))),
     CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH 
    (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY] ) ON [PRIMARY] GO
    Hope this helps [:D]
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 12, 2008 5:25 PM

All replies

  • User-324533553 posted

    did u try this

    select ISNULL(Ranking, 255) as ranking ....
    from ....
    order by ranking 
     
    Tuesday, August 12, 2008 2:12 PM
  • User-330204900 posted

    did u try this

    select ISNULL(Ranking, 255) as ranking ....
    from ....
    order by ranking 

     

    I think Robroe is after a Dynamic Data/Linq to SQL solution [:D]

    Tuesday, August 12, 2008 2:21 PM
  • User-330204900 posted

    Hi Robroe, I've had a thought why not create a Calculated Column on the table is' self and then sort on that instead of the actual column, or alternativly set a default value of 255 on the Ranking column so it always has a value of 255 unless set by the user.

    Then you could just use the LinqDataSource's AutoGenerateOrderByClause and OrderByParameters to sort the grid.

    Hope this helps [:D]

    Tuesday, August 12, 2008 2:41 PM
  • User-330204900 posted

    The computed column would look like this

    [SortRank]  AS (isnull([Ranking],(255)))

    See the full listing for creating the table dbo.Test:

    USE [Northwind]
    GO
    
    /****** Object:  Table [dbo].[Test]    Script Date: 08/12/2008 22:21:31 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Test](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Test] [nvarchar](50) NOT NULL,
    	[Ranking] [int] NULL,
    	[SortRank]  AS (isnull([Ranking],(255))),
     CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH 
    (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY] ) ON [PRIMARY] GO
    Hope this helps [:D]
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 12, 2008 5:25 PM
  • User-418270074 posted

    Hi,

    Thanks for your help, yep I was looking for a LINQ solution, but it might well be that its just because my db is counter intuative that I'm having these problems. I'm starting to think that it might be better to set the default value of the actual db column to be 255 or some high number rather than doing the whole isnull thing all over the place. I should be able to shield the user from knowing anything about this by using a customised slider user control or similar.

    Thanks again, if there was an assisted/partial answer button I would give you that, but as there isn't I'll be generous and mark it as answer ;)

    Rob

    Thursday, August 14, 2008 9:29 AM