Sorting an Alphanumeric DataView Column

Question

My DataGridView DataSource is bound to a DataView . DataView is equal to my dtBills DataTable . For instance:

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

I have several columns in this DataGridView. In particular, strings and integers are contained as information. When I click on the column heading of a DataGridView to sort the column, it is sorted as rows, such as 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 can I sort the linked column of a DataGridView when I click on the column heading, as usual? Should I use my DataView to help me?

Answer 1

When a DataGridView is a database binding, you cannot sort it, and you need to sort the original data. Sorting is a bit complicated, so I need two helper columns.

 dgvBills.AutoGenerateColumns = False tbl.Columns.Add(New DataColumn("Scol1", GetType(String))) tbl.Columns.Add(New DataColumn("Scol2", GetType(Integer))) 

The first will contain leading letters (or an empty string). The second will only contain the number contained in the string. Scol1, Scol2 .

Now we set all the columns to Programatic mode ( DataGridViewColumnSortMode Enumeration )

 For Each column As DataGridViewColumn In dgvBills.Columns column.SortMode = DataGridViewColumnSortMode.Programmatic Next 

And custom sorting is achieved in the ColumnHeaderMouseClick handler ( DataGridView.Sort (IComparer) method ). We will use the main view sort instead of the Grid sort.

 Private Sub dgvBills_ColumnHeaderMouseClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvBills.ColumnHeaderMouseClick Dim newColumn As DataGridViewColumn = dgvBills.Columns(e.ColumnIndex) Dim direction As ListSortDirection Dim Modifier As String = "" If newColumn.HeaderCell.SortGlyphDirection = SortOrder.Ascending Then direction = ListSortDirection.Descending Modifier = " desc" Else direction = ListSortDirection.Ascending End If Dim View As DataView = dgvBills.DataSource If {"JobNumber", "JobNumber1"}.Contains(dgvBills.Columns(e.ColumnIndex).Name) Then View.Table.Columns("Scol2").Expression = String.Format("Convert(iif (substring({0},1,2) like '*-',substring({0},3,len({0})-1),{0}), 'System.Int32')", dgvBills.Columns(e.ColumnIndex).Name) View.Table.Columns("Scol1").Expression = String.Format("iif (substring({0},1,2) like '*-',substring({0},1,2),'')", dgvBills.Columns(e.ColumnIndex).Name) View.Sort = String.Format("Scol1 {0},Scol2 {0}", Modifier) Else dgvBills.Sort(newColumn, direction) End If If direction = ListSortDirection.Ascending Then newColumn.HeaderCell.SortGlyphDirection = SortOrder.Ascending Else newColumn.HeaderCell.SortGlyphDirection = SortOrder.Descending End If End Sub 

In {"JobNumber", "JobNumber1"}.Contains ... you can set columns that are sorted differently. Other columns are sorted by default Grid type, or you can create another custom sort.

Note. I have a fully working example, but I hope the snippets are good enough.

Answer 2

The column is correctly sorted as rows, and I assume that you want to sort it as numbers. The problem is that it seems that the lines you have are combining numbers and characters. The result is the need for a fairly sophisticated sort for a DataView .

 Dim tbl As New DataTable("dtBills") Dim DataSet1 As New DataSet DataSet1.Tables.Add(tbl) tbl.Columns.Add(New DataColumn("MyCol", GetType(String))) Dim vals As String() = {"10001", "100012", "11000", "110049", "12000", "E-1234", "T-12345", "T-1235"} For qq = 0 To vals.Length - 1 Dim row As DataRow row = tbl.NewRow row(0) = vals(qq) tbl.Rows.Add(vals(qq)) Next tbl = DataSet1.Tables("dtBills") tbl.Columns.Add(New DataColumn("Scol2", GetType(Integer)) With {.Expression = "Convert(iif (substring(MyCol,1,2) like '*-',substring(MyCol,3,len(MyCol)-1),MyCol), 'System.Int32')"}) tbl.Columns.Add(New DataColumn("Scol1", GetType(String)) With {.Expression = "iif (substring(MyCol,1,2) like '*-',substring(MyCol,1,2),'')"}) Dim View As New DataView(tbl) View.Sort = "Scol1,Scol2" View.Table = DataSet1.Tables("dtBills") 

So, two new columns are added. First, keep the initial sorting second, to enable sorting by the number contained in the string.