DataGridView Row Header number disappears when the Columns are sorted at runtime

Question

I have a DataGridView control for which I have set the DataGridViewRow.HeaderCell.Value equal to an integer. The rows are numbered 1, 2, 3... (This might be irrelevant, I experience the same behavior no matter what I set the value to).

At runtime, when I click on the Column's header to toggle the sort order, the row headers disappear. More accurately, the numeric value assigned to the header is cleared.

I can easily set the SortMode propery of each DataGridViewColumn to NotSortable in a For Each loop, but I would like to keep the sort functionality.

Is there a way to handle this?

Here's the code I have:

Imports System.IO
Imports System.Data.SqlClient

Public Class Form1

    Dim CMD As New SqlCommand()
    Dim ADP As New SqlDataAdapter()
    Dim TBL As New DataTable

    Dim serverStr As String = "Server"
    Dim databaseStr As String = "Database"
    Dim dbLoginStr As String = "Login"
    Dim dbPassStr As String = "Password"

    Dim qry As String

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim connectionString As String =
            "Server=" & serverStr & ";" &
            "Database=" & databaseStr & ";" &
            "User Id=" & dbLoginStr & ";" &
            "Password=" & dbPassStr & ";" &
            "Timeout=1"

        qry = "SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME ASC"

        Try
            Using SQL As New SqlConnection
                SQL.ConnectionString = connectionString

                CMD.Connection = SQL
                CMD.CommandText = qry
                ADP.SelectCommand = CMD

                SQL.Open()
                ADP.SelectCommand = CMD
                TBL.Clear()
                TBL.Columns.Clear()
                ADP.Fill(TBL)
                SQL.Close()
            End Using
        Catch ex As Exception
            MsgBox("Could not connect to " & databaseStr & " on server: " & serverStr & vbNewLine & vbNewLine & ex.Message)
            Exit Sub
        End Try

        DataGridView1.DataSource = TBL
        DataGridView1.AutoResizeColumns()
        SetRowNumber(DataGridView1)
        DataGridView1.Columns(DataGridView1.Columns.Count - 1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
    End Sub

    Private Sub SetRowNumber(myDGV As DataGridView)
        For Each row As DataGridViewRow In myDGV.Rows
            row.HeaderCell.Value = (row.Index + 1).ToString()
        Next
        myDGV.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)

        'Remove Sorting ability
        'For Each col As DataGridViewColumn In myDGV.Columns
        '    col.SortMode = DataGridViewColumnSortMode.NotSortable
        'Next
    End Sub

    'See if value got cleared upon sorting
    'Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick

    '    Dim myDGV = DirectCast(sender, DataGridView)

    '    For Each row As DataGridViewRow In myDGV.Rows
    '        MsgBox(row.HeaderCell.Value)
    '    Next
    'End Sub
End Class

Answer 1

You can draw the Row number in the header using the RowPostPaint event.
This event is rised each time a Row needs to be re-painted, so the numbers will be redrawn each time it's necessary and will persist.

You may want to set the RowHeadersWidthSizeMode property:

[DataGridView].RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing

This will prevent a User from resizing the Rows Header, messing up the header's label (well, the numbers will be just hidden/partially visible. It's nonetheless an option).

The numbers are draw right-aligned and vertical-centered, so the number will expand on the left. You can change this behaviour changing the StringFormat's StringAlignment from Far (right) to Near (left):

 [StringFormat].Alignment = StringAlignment.Far   ' Align right
 [StringFormat].Alignment = StringAlignment.Near  ' Align left

The Color of the numbers is set to the DataGridView's dgv.RowTemplate.DefaultCellStyle.ForeColor.
Change this default style to set a different color.

The Font is set to the DataGridView's DefaultCellStyle.Font. Same consideration.

The number itself is formatted as $"{(e.RowIndex + 1):00}". Change it as required.

Private Sub DataGridView1_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridView1.RowPostPaint
    Dim dgv = DirectCast(sender, DataGridView)
    Dim rowHeader = $"{(e.RowIndex + 1):00}"

    Dim headerBounds As Rectangle =
        New Rectangle(e.RowBounds.Left, e.RowBounds.Top, dgv.RowHeadersWidth - 6, e.RowBounds.Height)

    Using format = New StringFormat(StringFormatFlags.NoWrap)
        format.Alignment = StringAlignment.Far
        format.LineAlignment = StringAlignment.Center
        Using brush As SolidBrush = New SolidBrush(dgv.RowTemplate.DefaultCellStyle.ForeColor)
            e.Graphics.DrawString(rowHeader, dgv.DefaultCellStyle.Font, brush, headerBounds, format)
        End Using
    End Using
End Sub