Alphanumeric DataView Sort

[Alex_AMF]

My DataGridView's DataSource is bound to a DataView. The DataView is equal to my dtBills DataTable. Like so:

Dim View As New DataView
View.Table = DataSet1.Tables("dtBills")
dgvBills.DataSource = View

I have multiple columns in this DataGridView. One in particular has strings and integers as information. When I click on the DataGridView Column Header to sort the column, it sorts as strings like the column on the left:

'Curr Col  >>>   ' Wanted Result
10001      >>>   10001
100012     >>>   11000
11000      >>>   12000
110049     >>>   100012
12000      >>>   110049
E-1234     >>>   E-1234
T-12345    >>>   T-1235
T-1235     >>>   T-12345

How would I go about sorting a bound DataGridView Column when pressing on the Column Header as I normally would? Should I use my DataView to help me out?

More simply put, I need the DataGridView columns to be able to sort aplhanumerically, while having a bound DataSource...
Thank you!

[dunfiddlin]

I need the DataGridView columns to be able to sort aplhanumerically

Er ... no ... you want them to be able to sort magically according to a design which exists only in your head! Alphanumeric is exactly how they are being sorted now! The only way to sort in this way would be to make the DGV non-sortableand the binding to a DataView sorted only on this column using a custom sorting method but I doubt whether it's possible to define the DataView sort method using the standard methods so you may have to go even further and create a whole new IComparable class and pre-sort the values before you even put them into the datatable. There simply is no 'natural' order to a mishmash value set like this.

[Alex_AMF]

Okay ... Forgive me for thinking that they were being sorted as strings only since my DataType in my DataTable is set to "String" ... I assumed that it was being sorted as a String and that my goal was to sort them Alphanumerically. I tried creating an IComparable class as well but I seem to be failing in my attempt to do so.

[dunfiddlin]

No, they are being sorted as Strings! That's what an alphanumeric sort is! What you want is a numeric sort which magically takes account of non-numeric characters (both alphabetic and punctuation). Unsurprisingly no such method exists in native code! The problem, in a nutshell, is the non-standardisation of these values either in format or type. If there is no sane system for interpreting these character strings in the first place you shouldn't be in the least bit shocked that there's no way to sort them consistently. As I said, there simply is no natural order to a bunch of arbitrary and apparently random character strings. That should have been taken into account when the reference system was originally devised. It's way too late now!

[si_the_geek]

One of the best methods I've seen for this kind of thing is: for each item, detect whether it is purely numeric, and if it is then put extra spaces in front of it (so that all numeric-only items have the same total length).

If you do that, the standard String based sorting will do what you want... but depending on how/when you add the extra spaces, it might make storing and/or working with the data a bit more awkward.

[Shaggy Hiker]

That will work for some things, but not for all. The strings E-1, E-2, E-10 will sort out in this fashion using any alphanumeric sort:

E-1
E-10
E-2

Since that example corresponds to the issue you are having with the last three items in your sample, it is a problem you will have to deal with, and it can't be overcome by prepending spaces, though prepending spaces will help with sorting the numeric values prior to the final three rows.

As dunfiddlin said, there is no natural sort mechanism that will solve this problem. I had to deal with the same thing for a project I was working on. My solution was to build a class that decomposed the string into alpha and numeric components, and the class had a custom comparer that did a fairly complicated sort. Also, my situation appeared to be simpler than the one you have, as all my sorts were much more regular than what you are dealing with.

[si_the_geek]

Since that example corresponds to the issue you are having with the last three items in your sample,.

oops, I didn't look carefully enough - I didn't spot the sorting in that part

[Shaggy Hiker]

Well, it probably doesn't matter, as the OP is a bit out of sorts with this problem. Sorting assorted strings is a sordid undertaking at the best of times.

This is a remarkably common problem, but there is no good answer...unless: The thing you must do is look for a pattern to the strings you are sorting. Your example showed no particular pattern at all, but perhaps that was just contrived. If all your strings fit some pattern such as A-NNNNN, or AA-NNNNN, then you might be able to come up with something, but it will be ugly and involve a fair amount of work. If the example you showed is a good representation of the values you have, then the sorting is going to be very difficult indeed. After all, you showed two types of patterns: A-NNNN, and NNNNN. Therefore, you might be able to examine the first character and be able to determine whether or not the whole string is the first type or the second type. The next step would be totally miserable for a bound dataview, though, as far as I can tell. You could certainly come up with a custom comparer that would sort those two patterns as you wanted, though it would also be a bit ugly. The problem I would have is that, once you had that list of values sorted....how do you sort the datatable based on the sorting you got for the list?

[Alex_AMF]

Yeah my problem is exactly that ... There are many different types of patterns and if ever it is sorted, I need to replicate it to the DataTable or DataView ... Also, this issue applies for customer addresses. In any case, adding spaces in front of the numbers does solve quite a few issues. I'll keep at it and let you all know if I get a solution, even if it is quite complicated.