[RESOLVED] How do you sort a column in a DataGridView control that contains DBNull values?

[J2Tuner]

I am trying to sort a DataGridView control by a column that contains DBNull values. This DataGridView control is bound to an SQL Server 2012 Database.

When I click the headercell (to sort in ascending order), the DBNull values are sorted ahead of the rest of the integer values, so that the top rows in the column are all blank, and then after that the integer values 1,2,3 etc are in ascending order. (DBNull, DBNull, 1, 2, 3)

How do I get around this? I would rather the DataGridView control sort the rows with values at the top, followed by the DBNulls. (1, 2, 3, DBNull, DBNull)

I have tried to insert a higher value into the blank cells, which then sorts them correctly, but when I return those values back to System.DBNull.value, the sort order reverts back to the way above.

My code is as follows:

If dgv.Columns(e.ColumnIndex).Name.EndsWith("Position") Then
    intSortingRunningPosition = 1000
    dgv.Sort(baseColumn, System.ComponentModel.ListSortDirection.Ascending)
    newColumn.HeaderCell.SortGlyphDirection = Windows.Forms.SortOrder.Ascending
    For Each dr As DataGridViewRow In dgv.Rows
        If dr.Cells(e.ColumnIndex).Value Is System.DBNull.Value Then 
            dr.Cells(e.ColumnIndex).Value = intSortingRunningPosition
            intSortingRunningPosition += 1
        End If
    Next
    dgv.Sort(newColumn, System.ComponentModel.ListSortDirection.Ascending)
    For Each dr As DataGridViewRow In dgv.Rows
        If dr.Cells(e.ColumnIndex).Value >= 1000 Then
            dr.Cells(e.ColumnIndex).Value = System.DBNull.Value
        End If
    Next
End If

I have also tried the following, using the Sort(IComparer) Method, but had little success:

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 CompareResult As Integer
     Dim intDGV1Cell0Value As Integer = Nothing
     Dim intDGV1Cell1Value As Integer = Nothing
     Dim intDGV2Cell0Value As Integer = Nothing
     Dim intDGV2Cell1Value As Integer = Nothing

     Try
         intDGV1Cell0Value = CInt(DataGridViewRow1.Cells(0).Value)
     Catch ex As Exception
         intDGV1Cell0Value = Int32.MaxValue
     End Try
     Try
         intDGV1Cell1Value = CInt(DataGridViewRow1.Cells(1).Value)
     Catch ex As Exception
         intDGV1Cell1Value = Int32.MaxValue
     End Try
     Try
         intDGV2Cell0Value = CInt(DataGridViewRow2.Cells(0).Value)
     Catch ex As Exception
         intDGV2Cell0Value = Int32.MaxValue
     End Try
     Try
         intDGV2Cell1Value = CInt(DataGridViewRow2.Cells(1).Value)
     Catch ex As Exception
         intDGV2Cell1Value = Int32.MaxValue
     End Try

     ' Try to sort based on the Last Name column.
     CompareResult = System.String.Compare(intDGV1Cell1Value, intDGV2Cell1Value)

     ' If the Last Names are equal, sort based on the First Name. 
         If CompareResult = 0 Then
             CompareResult = System.String.Compare(intDGV1Cell0Value, intDGV2Cell0Value)
         End If

     Return CompareResult * sortOrderModifier
 End Function

Any ideas on what the best way to achieve this may be?

Thanks

[dunfiddlin]

Why do you allow DbNull values in an integer field at all? Is this meant to represent something esoteric like zero squared? The value is zero as far as the sort's concerned so you might as well make it explicitly so. If you want to sort in this rather arcane way, the easiest solution is to add a column with dependent values that would give you the sort order you need so zero/null is 9999, but 1 is 1, 2 is 2 etc.

[J2Tuner]

Ok, I added a column to the database so that I can get away from the issues with just adding it to the datagridview control, and it seems to be working for the most part. The only issue that I have now, is that when I sort specific columns with DBNull values, the row that is currently selected (along with a few other random rows) does not get sorted until I click off of it, and then click it again. Then it seems to fall right where it is supposed to be. Do I need to refresh the table or something along those lines?

My code now is as follows:

Private Sub dgvHeader_Click(ByVal sender As Object, ByVal e As DataGridViewCellMouseEventArgs) Handles dgvOverview.ColumnHeaderMouseClick
        Dim newColumn As DataGridViewColumn = Nothing
        Dim baseColumn As DataGridViewColumn = Nothing
        Dim sortColumn As DataGridViewColumn = Nothing
        Dim intSortColumnIndex As Integer = Nothing
        Dim intSortArraySize As Integer = 0
        Dim strSortArray(intSortArraySize) As String
        Dim boolExitFor As Boolean = False
        Dim intCurrentRow As Integer = 0
        Dim intTotalRows As Integer = Nothing
        Dim intSortArrayPosition As Integer = 0
        Dim m_CopyDataTable As DataTable = Nothing
        Dim boolSkipAdd As Boolean = False
        Dim intSortingRunningPosition As Integer = Nothing

        For Each tbp As TabPage In tabctrlTimingTable.TabPages
            For Each dgv As DataGridView In tbp.Controls
                If dgv.CanFocus Then
                    newColumn = dgv.Columns(e.ColumnIndex)
                    baseColumn = dgv.Columns(0)
                    For Each col As DataGridViewColumn In dgv.Columns
                        If col.Name = "Sort" Then
                            sortColumn = col
                            intSortColumnIndex = col.Index
                        End If
                    Next
                    For Each dr As DataGridViewRow In dgv.Rows
                        dr.Cells(intSortColumnIndex).Value = System.DBNull.Value
                    Next

                    If newColumn.HeaderCell.SortGlyphDirection = Windows.Forms.SortOrder.Descending Then
                        If dgv.Columns(e.ColumnIndex).Name.EndsWith("Position") And Not dgv.Columns(e.ColumnIndex).Name = "Starting Position" Then
                            intSortingRunningPosition = 1000
                            dgv.Sort(baseColumn, System.ComponentModel.ListSortDirection.Ascending)
                            For Each dr As DataGridViewRow In dgv.Rows
                                If dr.Cells(e.ColumnIndex).Value Is System.DBNull.Value Then
                                    dr.Cells(intSortColumnIndex).Value = intSortingRunningPosition
                                    intSortingRunningPosition += 1
                                Else
                                    dr.Cells(intSortColumnIndex).Value = dr.Cells(e.ColumnIndex).Value
                                End If
                            Next
                            dgv.Sort(sortColumn, System.ComponentModel.ListSortDirection.Ascending)
                        Else
                            dgv.Sort(newColumn, System.ComponentModel.ListSortDirection.Ascending)
                        End If
                        newColumn.HeaderCell.SortGlyphDirection = Windows.Forms.SortOrder.Ascending
                    ElseIf newColumn.HeaderCell.SortGlyphDirection = Windows.Forms.SortOrder.Ascending Then
                        If dgv.Columns(e.ColumnIndex).Name.EndsWith("Position") And Not dgv.Columns(e.ColumnIndex).Name = "Starting Position" Then
                            intSortingRunningPosition = -1000
                            dgv.Sort(baseColumn, System.ComponentModel.ListSortDirection.Ascending)
                            For Each dr As DataGridViewRow In dgv.Rows
                                If dr.Cells(e.ColumnIndex).Value Is System.DBNull.Value Then
                                    dr.Cells(intSortColumnIndex).Value = intSortingRunningPosition
                                    intSortingRunningPosition -= 1
                                Else
                                    dr.Cells(intSortColumnIndex).Value = dr.Cells(e.ColumnIndex).Value
                                End If
                            Next
                            dgv.Sort(sortColumn, System.ComponentModel.ListSortDirection.Descending)
                        Else
                            dgv.Sort(newColumn, System.ComponentModel.ListSortDirection.Descending)
                        End If
                        newColumn.HeaderCell.SortGlyphDirection = Windows.Forms.SortOrder.Descending
                    Else
                        If dgv.Columns(e.ColumnIndex).Name.EndsWith("Position") And Not dgv.Columns(e.ColumnIndex).Name = "Starting Position" Then
                            intSortingRunningPosition = 1000
                            dgv.Sort(baseColumn, System.ComponentModel.ListSortDirection.Ascending)
                            For Each dr As DataGridViewRow In dgv.Rows
                                If dr.Cells(e.ColumnIndex).Value Is System.DBNull.Value Then
                                    dr.Cells(intSortColumnIndex).Value = intSortingRunningPosition
                                    intSortingRunningPosition += 1
                                Else
                                    dr.Cells(intSortColumnIndex).Value = dr.Cells(e.ColumnIndex).Value
                                End If
                            Next
                            dgv.Sort(sortColumn, System.ComponentModel.ListSortDirection.Ascending)
                        Else
                            dgv.Sort(newColumn, System.ComponentModel.ListSortDirection.Ascending)
                        End If
                        newColumn.HeaderCell.SortGlyphDirection = Windows.Forms.SortOrder.Ascending
                    End If
                    boolExitFor = True
                    Exit For
                End If
            Next
            If boolExitFor = True Then
                Exit For
            End If
        Next

    End Sub

    Private Sub dgvOverview_DataBindingComplete(ByVal sender As Object, ByVal e As DataGridViewBindingCompleteEventArgs) Handles dgvOverview.DataBindingComplete

        ' Put each of the columns into programmatic sort mode. 
        For Each column As DataGridViewColumn In dgvOverview.Columns
            column.SortMode = DataGridViewColumnSortMode.Programmatic
        Next
    End Sub

    Public Class myReverserClass
        Implements IComparer

        ' Calls CaseInsensitiveComparer.Compare with the parameters reversed. 
        Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
            Return New CaseInsensitiveComparer().Compare(y, x)
        End Function

    End Class

[dunfiddlin]

Well, here's an off the wall, straight out of left field suggestion. Try it and then you can tell us!

[J2Tuner]

Using the following method:

dgv.Refresh()

Doesn't seem to do anything. I heard that this only forces a repaint of the DataGridView, but that is what I expected to need. I am not quite sure why I am having this issue. It seems to sort fine for the first few sorts, and then it starts to skip rows randomly, and it keeps skipping more rows the more you try to sort the column.

I've been pretty lost trying to manually sort.

[dunfiddlin]

I doubt whether it's truly random. There'll be a pattern to it even if it's not obvious and my guess would be that it is rows where you have made changes not yet committed to the datatable and database so I would suggest you run an update before the sort (whether it's 'needed' or not). If that's not the problem, the dgv automatic sort releases all selections by default so it might help if you did likewise at the start of your programmatic sort (you'll have to find a way to keep a record of selections if you want them to carry over a sort though).

[J2Tuner]

I don't have a reason to keep the selections during the sort. How do you release them? I tried to set the dgv.selecteditems property to none, but I guess this property is read-only. I'm sure I am missing something simple.

[J2Tuner]

Aha! Clearing the selection was the trick. The problem was that I sort the table by a column before I check to see if another column has empty values, give values to those empty cells, and then re-sort by this column. It seems that I have to clear the selection before the first sort, and again, before the second sort, because a cell seems to get selected immediately after a sort.

I ended up adding the following code before each sort.

dgv.ClearSelection()
dgv.CurrentCell = Nothing

Oh, and I had to use both of these lines of code, otherwise it would not work.

Thanks for the help!