Dataview Sort

[JoelB]

Apparently CF doesn't support sorting a dataview on multiple columns. The
following all work:

MyDV.Sort = "Col1"
MyDV.Sort = "Col2 DESC"

....but this doesn't:

MyDV.Sort = "Col1, Col2 DESC"

No error occurs.

I am using the Dataview to sort my data before binding it to a combo. I
need the two column sort because the dataset is built with a UNION query (I
won't get into the details).

Has anyone else encountered this, or does anyone have any comments?

Joel

 

[Alex Feinman [MVP]]

It works. Please provide a snippet of code.
For a working sample see
http://www.alexfeinman.com/download.asp?doc=CBQuickFind.zip

 

[JoelB]

Hi, Alex. Here's what I'm working with:

CommandText = "SELECT 1 as SortOrder, DisplayNumber FROM MyTable " _
"UNION SELECT 2 as SortOrder, OtherNumber as DisplayNumber FROM
MyOtherTable"

Dim Command As New SqlCeCommand(CommandText, ConnectionToLocalDB)
Command.CommandType = CommandType.Text
Dim Adapter As New SqlServerCe.SqlCeDataAdapter(Command)
Adapter.Fill(dsNumbers)
dvNumbersSorted = New DataView(dsNumbers.Tables("Table"))
dvNumbersSorted.Sort = "SortOrder, DisplayNumber"
cmb.DataSource = dvNumbersSorted
cmb.DisplayMember = "DisplayNumber"

The output from the SQL statement is this:

1 12345
2 1-0284

The combo displays this...

12345
1-0284

....regardless of whether I use "SortOrder, DisplayNumber DESC" or
"SortOrder, DisplayNumber ASC"

If I drop either column and flip ASC/DESC (sorting on just one column), the
sort flips as expected.

Joel

 

[Alex Feinman [MVP]]

I must be missing something here. You have these data:
SortOrder DisplayNumber
1 12345
2 1-0284

Why do you expect that the order with "SortOrder, DisplayNumber ASC" will be
different from "SortOrder, DisplayNumber DESC"
The secondary sort criterium (Displaynumber) is taken into account if the
primary one is the same for affected records. If your data were
SortOrder DisplayNumber
2 12345
2 1-0284

then having DisplayNumber ASC vs DisplayNumber DESC would have mattered. As
it is the records are sorted first on SortOrder field.

 

[JoelB]

Alex,

The problem is that it's not sorting on the SortOrder column either. If it
were, the two different sorts would come out like this, yes?

1 12345
2 1-0284

2 1-0284
1 12345

The sort occurs before the dataview is associated with the combo, so the
fact that the sort order column is not the diplay field should not affect
the sort, should it? In other words, I would expect the combo to diplay as
follows:

ASC
12345
1-0284

DESC
1-0284
12345

Joel

 

[Sergey Bogdanov]

Follow Alex suggestions. Remove SortOrder order criteria and leave only
"DisplayNumber DESC" / "DisplayNumber" then you will get a resulset
that you wanted.

Best regards,
Sergey Bogdanov
http://www.sergeybogdanov.com

 

[Chris Tacke, eMVP]

Actually no. ASC and DESC are modifiers on a specific column, not the whole
set of columns. The default is ASC.

So in your case:
SortOrder, DisplayNumber ASC
and
SortOrder, DisplayNumber DESC

Both will order by SortOrder in ascending order first. As Alex said, only
when there are multiples of the first column does the sort order of the
second make any difference.

You should modify it to something like this:
SortOrder DESC, DisplayNumber DESC

--
Chris Tacke
Co-founder
OpenNETCF.org
Has OpenNETCF helped you? Consider donating to support us!
http://www.opennetcf.org/donate

 

[JoelB]

Thank you gentlemen. Chris -- I think you nailed it -- I did not understand
that it didn't pertain to all columns, but to each column! I'll give that a
shot.

Sergey, your suggestion would not have produced the results I was after, but
my example was at fault. I just didn't provide enough data to make it
clear.

Anyway, thanks to both of you.

Joel