First rows of DataView not sorting

[mycroc]

Hi, Im coding a small report parsing utility for one of my colleagues. It imports an excel spreadsheet, and populates a DataSet, which then is bound to a DataView, which in turn bound to a DataGrid. I then check some flags, and concatenate a string into a newly-made column (the one Im having trouble sorting). The problem I'm having is why I try to sort by the new column, a couple of rows are out of place, while all the rest are properly ordered. Since sorting is a 1-liner, I dont even know how to go about debugging it. Any thoughts?

(...)
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Imitation Desktop\Parser_VB\Proj.xls; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"SELECT L_Region, P_Site, UserName, CompName, MSProject2000, MSProject2002, MSProject2003 from [Sheet1$]", MyConnection)
DS = New System.Data.DataSet()
MyCommand.Fill(DS)
MyConnection.Close()
'Dispose OLE controls
MyConnection.Dispose()
MyCommand.Dispose()
(...)
DS.Tables(0).Columns().Add("Versions", System.Type.GetType("System.String"))
(...)
ForEach row As DataRowView In dvView
row.Item("Versions") = ""
'Check flags to see if this version exists
If (alVers.Item(i).MS2000 = 1) Then
row.Item("Versions") = "MS Project 2000"
EndIf
If (alVers.Item(i).MS2002 = 1) Then
If (row.Item("Versions") = "") Then
row.Item("Versions") = "MS Project 2002"
Else
row.Item("Versions") = String.Concat(row.Item("Versions"), ", MS Project 2002")
EndIf
EndIf

If (alVers.Item(i).MS2003 = 1) Then
If (row.Item("Versions") = "") Then
row.Item("Versions") = "MS Project 2003"
Else
row.Item("Versions") = String.Concat(row.Item("Versions"), ", MS Project 2003")
EndIf
EndIf

i += 1

Next


Thanks!!

Dave

 

[JohnH]

There is a DGV.Sort method overload that takes an instance of a IComparer, where you make the comparison yourself. See DataGridView.Sort Method (IComparer) for explanation and code example.

You don't say what's wrong, but the most common sorting irregularity is numbers sorted as text (like this: 1,2,22,3 when you wanted 1,2,3,22) in which case depending on the value you can make either plain number sort directly from the value or extract the number part from a combined string before making the plain number sort. The other 'common' sorting issue is culture specific alpha sorting, which is even easier by just creating a new instance of Comparer class setting CultureInfo.

Moved post to Winforms Grids forum.

 

[mycroc]

I will try what you said John, but to clarify what the exact issue is; I end up with a sorted list that looks something like this:

MS Project 2003
MS Project 2003
MS Project 2000, MS PRoject 2002
MS Project 2002
MS Project 2002
MS Project 2002
...
...
MS project 2003
MS Project 2003
...
...

As you can see, I get these stray '2003' rows at the top for no reason. Even more odd is that if I add an empty row at the very top, the list sorts perfectly.

Also, will the contents of the DataView be updated if I sort the DataView grid using the overloaded sort? I ask this because I plan on outputting the contents of the DataView to an excel spreadsheet.