none
DataGridView does not display newly sorted data after clicking on heading to sort RRS feed

  • Question

  • I have a DataGridView that is filled with data from a dataset.  I have a Windows Form button that when clicked, runs an SQL that sorts the data and refills the grid.  The following is the code:

    strSQL = "SELECT * FROM AccessTable WHERE ID = 'P' OR ID = 'PD' OR ID = 'PN' ORDER BY [autoseq], [row_seq]"
    
    Dim cn as New OleDbConnection(My.Settings.ConnString)
    cn.Open()
    Using daDB As New OleDbDataAdapter(strSQL, cn)
         ds.Clear()
         daDB.Fill(ds)
    End Using
    cn.Close()
    
    DataGridView1.DataSource = ds.Tables(0)
    DataGridView1.Refresh
    

    All works well.  The data displays in the grid like so:

    autoseq     ID      Description       Code

    60             P        Description1     Code1     .....

    61             PD      Description2     Code2     .....

    62             PN      Description3     Code3     .....

    63             P        Description4     Code4     .....

    ...

    When I click on the Description column heading, the grid sorts by Description, either ascending or descending.  No problem.  When I click on the form button that executes the above code, the DataGridView continues to display in description ascending or descending order.  The data in the DataGridView does not display sorted by autoseq.  I tried clearing DataGridView1 in a variety of ways but that didn't work.

    I stopped the code at DataGridView1.Refresh.  I used the Immediate window to see what was in some of the dataset records and they appeared to be in the desired sorted order.  That is, ds.Tables(0).rows(0).item(0) was 60, ds.Tables(0).rows(1).item(0) was 61, ...  but yet the Grid still displayed the data in Description asc or desc order with the appropriate glyph.

    I then researched if I could somehow turn off SelectedColumn sort.  I tried the following code and tried it before the cn.Open() and just before binding the datasource to the dataset:

    If DataGridView1.SortedColumn IsNot Nothing Then
         DataGridView1.Columns("Description").SortMode = DataGridViewColumnSortMode.NotSortable
         DataGridView1.Columns("Description").ReadOnly = True
    End If

    It didn't work.  The glyph went away, but the data order stayed the same.

    When I restart the program, sorted using the form button code and without clicking on the heading sort, I stopped the code and saw in the Immediate window that

    DataGridView1.SortedColumn = Nothing

    When I click on the Description column heading to sort the column data by description and stopped the code,

    DataGridView1.SortedColumn was not nothing anymore.

    I think I am looking for a way to reset DataGridView1.SortedColumn back to Nothing so that the grid will fill up with data from the dataset in the SQL sorted order.  Can someone let me know if I am on the right track?

    Thursday, April 11, 2019 8:54 PM

Answers

  • Thanks Les,  Sorry it took me so long to respond.

    I added the following button to your example to load the table with different data in a different order.

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            With dt
                .Rows.Clear()
                .Rows.Add(60, "P", "Description5", "Code5")
                .Rows.Add(61, "PD", "Description6", "Code6")
                .Rows.Add(62, "PN", "Description7", "Code7")
                .Rows.Add(63, "P", "Description8", "Code8")
            End With
            'dt.DefaultView.Sort = String.Empty
            DataGridView1.DataSource = ds.Tables(0)
        End Sub
    

    If you run your form load, the grid is in the order that the table was loaded.  If the user clicks on the Description heading twice, the sort order is changed to desc, i.e., Description4 to Description1 with the glyph pointing down. Button2 reloads the table, this time in a different order, Description5 to Description8.  But the grid displays the data Description8 to Description5.  No matter how the table is loaded, the grid will display sort order descending.  What I want to do is reset the grid sort order when reloading the table in a different order.  The answer is to uncomment

    'dt.DefaultView.Sort = String.Empty

    and when button2 is clicked, the order of the grid is the order that it is loaded, Description5 to Description8.

    Your answer pushed me in the right direction.  Thanks.

    • Marked as answer by Gunner359 Tuesday, May 7, 2019 7:43 PM
    Tuesday, May 7, 2019 7:42 PM

All replies

  • Bottom line is a DataGridView does best with sorting on one column rather than multiples via a SQL ORDER BY. It would be good to test things without the multiple order by on a DataGridView with nothing set property wise and no events.

    Here is a possible solution

    https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.datagridview.sort?view=netframework-4.7.2#System_Windows_Forms_DataGridView_Sort_System_Collections_IComparer_


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, April 11, 2019 9:32 PM
    Moderator
  • Hi

    This seems to work, but I am unsure if this answers your question as I don't fully follow your logic. Click the Button1 to sort DataGridView on ascending "autoseq" column

    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim ds As New DataSet
        Dim dt As New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With dt
                .Columns.Add("autoseq", GetType(Integer))
                .Columns.Add("ID", GetType(String))
                .Columns.Add("Description", GetType(String))
                .Columns.Add("Code", GetType(String))
    
                .Rows.Add(60, "P", "Description1", "Code1")
                .Rows.Add(61, "PD", "Description2", "Code2")
                .Rows.Add(62, "PN", "Description3", "Code3")
                .Rows.Add(63, "P", "Description4", "Code4")
    
            End With
            ds.Tables.Add(dt)
    
            DataGridView1.DataSource = ds.Tables(0)
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView1.Sort(DataGridView1.Columns("autoseq"), ComponentModel.ListSortDirection.Ascending)
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    • Marked as answer by Gunner359 Tuesday, May 7, 2019 7:23 PM
    • Unmarked as answer by Gunner359 Tuesday, May 7, 2019 7:23 PM
    Thursday, April 11, 2019 9:38 PM
  • Thanks Les,  Sorry it took me so long to respond.

    I added the following button to your example to load the table with different data in a different order.

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            With dt
                .Rows.Clear()
                .Rows.Add(60, "P", "Description5", "Code5")
                .Rows.Add(61, "PD", "Description6", "Code6")
                .Rows.Add(62, "PN", "Description7", "Code7")
                .Rows.Add(63, "P", "Description8", "Code8")
            End With
            'dt.DefaultView.Sort = String.Empty
            DataGridView1.DataSource = ds.Tables(0)
        End Sub
    

    If you run your form load, the grid is in the order that the table was loaded.  If the user clicks on the Description heading twice, the sort order is changed to desc, i.e., Description4 to Description1 with the glyph pointing down. Button2 reloads the table, this time in a different order, Description5 to Description8.  But the grid displays the data Description8 to Description5.  No matter how the table is loaded, the grid will display sort order descending.  What I want to do is reset the grid sort order when reloading the table in a different order.  The answer is to uncomment

    'dt.DefaultView.Sort = String.Empty

    and when button2 is clicked, the order of the grid is the order that it is loaded, Description5 to Description8.

    Your answer pushed me in the right direction.  Thanks.

    • Marked as answer by Gunner359 Tuesday, May 7, 2019 7:43 PM
    Tuesday, May 7, 2019 7:42 PM