Page 1 of 2

Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 8:09 am
by mtartaglia
I have a datagridview from a MS-SQL database that gets displayed in a form. I used the code that the database connection object created automatically. I want to sort by one of the columns as soon as the form opens. I assume it's done in the last list in the code below. Just not sure what to change.

$datagridviewResults_ColumnHeaderMouseClick=[System.Windows.Forms.DataGridViewCellMouseEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellMouseEventArgs]
if($datagridviewResults.DataSource -is [System.Data.DataTable])
{
$column = $datagridviewResults.Columns[$_.ColumnIndex]
$direction = [System.ComponentModel.ListSortDirection]::Ascending

if($column.HeaderCell.SortGlyphDirection -eq 'Descending')
{
$direction = [System.ComponentModel.ListSortDirection]::Descending
}

$datagridviewResults.Sort($datagridviewResults.Columns[$_.ColumnIndex], $direction)
}
}

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 9:58 am
by jvierra
Just click the column header. If the table is loaded cor4rectly it wil sort. If it is not loaded correctly it will not sort.
You must add a DataTable to the grid to allow it to sort correctly.

$datagridview.DataSource = $datatable

"$datatable" should be the object returned from the SQL.

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 10:22 am
by mtartaglia
Yes I know how to do that, that was way too obvious. But what I am trying to do is have the form reload using a timer (say 1 minute). But when it does reload, the sort reverts back to the data in order of when the data was entered not by the sort how you mentioned.

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 10:29 am
by jvierra
Set the sort for the source.

$datatable.DefaultView.Sort = 'Name' # 'Name' is column name.

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 10:38 am
by mtartaglia
I put it in at the bottom of the previously posted code, but since it's not working, I assume that is not the correct place.

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 10:45 am
by jvierra
When you reload the data add it to the DataTable at that point. Remove the click event.
You an also just add an "ORDER BY" to your SQL and the table will be sorted when it is retrieved.

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 10:51 am
by mtartaglia
Awesome. I will try that. On another note, is there a way for the form to "reload" and keep the existing sort? That's ultimately what should be done in this instance.

Another words, if I click one of the columns and the form reloads, can the existing sort stay some how?

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 10:55 am
by jvierra
Yes = change the SQL and add an "ORDER BY" to the SQL.

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 10:58 am
by mtartaglia
Sorry,

I am not being clear enough...

If I click on the 'Name' column to sort after the form loads the first time, then the form reloads (because the timer ran out), how can I keep the sort by 'Name' without doing it in SQL. The reason why I say that is I could have another column sorted, not necessarily the 'Name' column. I hope that was clearer.

Re: Sorting by column in a datagridview

Posted: Wed Sep 18, 2019 11:01 am
by jvierra
You can also save the sort that is currently defined:

$currentsort = $datagridview1.DataSource.DefaultView.Sort
# reload
$datagridview1.DataSource.DefaultView.Sort = $currentsort