[RESOLVED] Sorting DataBound DataGridView Columns

[J2Tuner]

I have multiple runtime generated DataGridView, and I would like to sort columns with null values. These DataGridView are bound to a Sql Server 2008 database. I have tried using the ICompare Method, which did not work because the DataGridView was bound. Is there a better option?

How does the automatic sort work? The problem with automatic sorting is that the NULL values are placed at the top of an ascending list, and I would like them to be at the bottom of the list.

The code I have tried so far is:

Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) _
    Handles Button1.Click
        If RadioButton1.Checked = True Then
            DataGridView1.Sort(New RowComparer(SortOrder.Ascending))
        ElseIf RadioButton2.Checked = True Then
            DataGridView1.Sort(New RowComparer(SortOrder.Descending))
        End If
    End Sub

    Private Class RowComparer
        Implements System.Collections.IComparer

        Private sortOrderModifier As Integer = 1
        Private sortCompareModifier As Integer = 1

        Public Sub New(ByVal sortOrder As SortOrder)
            If sortOrder = sortOrder.Descending Then
                sortOrderModifier = -1
                sortCompareModifier = -1
            ElseIf sortOrder = sortOrder.Ascending Then
                sortOrderModifier = 1
                sortOrderModifier = 1
            End If
        End Sub

        Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer _
            Implements System.Collections.IComparer.Compare

            Dim DataGridViewRow1 As DataGridViewRow = CType(x, DataGridViewRow)
            Dim DataGridViewRow2 As DataGridViewRow = CType(y, DataGridViewRow)
            Dim intCompareResult As Integer = Nothing

            ' Try to sort based on the Last Name column. 

            If DataGridViewRow1.Cells(1).Value.ToString = "" And Not DataGridViewRow2.Cells(1).Value.ToString = "" Then
                intCompareResult = 1 * sortCompareModifier
            ElseIf Not DataGridViewRow1.Cells(1).Value.ToString = "" And DataGridViewRow2.Cells(1).Value.ToString = "" Then
                intCompareResult = -1 * sortCompareModifier
            Else
                intCompareResult = System.String.Compare(DataGridViewRow1.Cells(1).Value.ToString(), DataGridViewRow2.Cells(1).Value.ToString())
            End If
            



            ' If the Last Names are equal, sort based on the First Name. 
            If intCompareResult = 0 Then
                intCompareResult = System.String.Compare(DataGridViewRow1.Cells(0).Value.ToString(), DataGridViewRow2.Cells(0).Value.ToString())
            End If
            Return intCompareResult * sortOrderModifier
        End Function
    End Class

This code works well for a non-bound DataGridView.

I have also tried adding a column to my database, giving the column high values for Null Integers and maxdates for Null Dates for ascending, and the opposite for decending sorts, and then sorting with this column, but this does not seem to be the best way to achieve my goal.

I have also tried:

    Private Sub dgvOverview_SortCompare(sender As Object, e As System.Windows.Forms.DataGridViewSortCompareEventArgs) Handles dgvOverview.SortCompare
        Dim dgv As DataGridView = sender
        Dim intCheckpointNumberLength As Integer
        Dim intCheckpointNumber As Integer
        Dim strCheckpointName As String

        If strProjectType = strCircuit Then
            intCheckpointNumberLength = Len(dgv.Name) - 6
            intCheckpointNumber = Microsoft.VisualBasic.Right(dgv.Name, intCheckpointNumberLength)
        ElseIf strProjectType = strP2P Then
            intCheckpointNumberLength = Len(dgv.Name) - 13
            intCheckpointNumber = Microsoft.VisualBasic.Right(dgv.Name, intCheckpointNumberLength)
        End If

        strCheckpointName = "Checkpoint " & intCheckpointNumber & " "

        For Each col As DataGridViewColumn In dgv.Columns
            If col.Index = e.Column.Index Then
                If col.Name = strCheckpointName & "Time" Then
                    e.SortResult = System.DateTime.Compare(SortToNull(e.CellValue1, "Time"), SortToNull(e.CellValue2, "Time"))
                ElseIf col.Name.EndsWith("Position") Then
                    e.SortResult = System.DateTime.Compare(SortToNull(e.CellValue1, "Position"), SortToNull(e.CellValue2, "Position"))
                End If


                e.Handled = True

                Exit Sub
            End If
        Next

    End Sub

but I am not sure what event will call this procedure. I also don't think that the SortCompare method works with DataBound DataGridView.

Any ideas? I am pretty lost here...

Thanks

[J2Tuner]

Sorry to revive this dead thread, but I did come up with a solution to this problem.

I was fairly unsucessful using the Sort(ICompare) method to sort this database because it was bound to an external dataset. I ended up modifying my connection string and refilling the datatable.

My code is as follows:

If DataType = "Integer" Then
    If Column = "Vehicle Number" Then
        strConnectionStringCase = "Select * From Timing ORDER BY CAST([Class] AS NVARCHAR(MAX)) " & Direction & ", CONVERT(int, RTRIM(CAST([" & Column & "] AS NVARCHAR(MAX)))) " & Direction
    End If        
ElseIf DataType = "DateTime" Then
    strConnectionStringCase = "Select * From Timing ORDER BY CASE WHEN [" & Column & "] is NULL THEN 1 ELSE 0 END, [" & Column & "] " & Direction
ElseIf DataType = "String" Then
    If Column = "Vehicle Number" Then
        strConnectionStringCase = "Select * From Timing ORDER BY LEN(CAST([" & Column & "] AS NVARCHAR(MAX))) " & Direction & ", CAST([Class] AS NVARCHAR(MAX)) " & Direction & ", CAST([" & Column & "] AS NVARCHAR(MAX)) " & Direction
    End If
End If

I call this subroutine on the header cell click event. I send the Column name, sort direction, and datatype to this subroutine.

This code also allows me to sort the column depending on the type of data. If there are alphanumeric strings, I can sort them in a similar order as if they were only integers. The DBNull values are always placed at the end of the sort.

Hope this can help somebody else!

[dunfiddlin]

Hope this can help somebody else!

Well done on finding a solution but I'm not sure there's gonna be too many applications for a sort that makes zero the highest number?

[J2Tuner]

Agreed. Maybe this is why help was in short supply? Lol.

Regardless of the null value issue that I had faced, this can possibly at least help anybody trying to custom sort a DataGridView that is bound to an external dataset.