Another thread on Datagridview sort

[motogpdesmo16]

Hi everyone,
this is another thread about how to sort the Datagridview. In my scenario I've a datagridview and this is the code I use to fill it:

...declaration of cmdStoredProc...
        cn.Open()
        cmdStoredProc.ExecuteNonQuery()
 
    Dim da As OracleDataAdapter = New OracleDataAdapter(cmdStoredProc)
    Dim ds As DataSet = New DataSet
    da.Fill(ds, cNomeSP)
    MyFillDGV(dgvGriglia, ds)
    cn.Close()

and then, this is MyFillDGV:

dgvGriglia.AutoGenerateColumns = False
    dgvGriglia.AllowUserToAddRows = False
    dgvGriglia.AllowUserToDeleteRows = False
    dgvGriglia.AllowUserToResizeRows = False
    dgvGriglia.AllowUserToOrderColumns = True
    dgvGriglia.MultiSelect = False
    dgvGriglia.ReadOnly = True
    dgvGriglia.RowHeadersVisible = False
    dgvGriglia.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
    dgvGriglia.SelectionMode = DataGridViewSelectionMode.FullRowSelect
 
    Dim oCol As New DataGridViewTextBoxColumn
    oCol.Name = cColName
    oCol.DefaultCellStyle.Alignment = oAlignment
    oCol.Width = VB6.TwipsToPixelsX(nTwWidth)
    oCol.HeaderText = cHeaderText
    oCol.DataPropertyName = cDataPropertyName
    dgvGriglia.columns.add(oCol)
    dgvGriglia.Columns(0).SortMode = DataGridViewColumnSortMode.Automatic
 
    dgvGriglia.DataSource = ds.Tables(0).Select(cFilter)

So it works very well only if i set AutoGenerateColumns = True.
Why this code doesn't work with the autogeneratecolumns property set to false and the runtime binding of each column?
Thank you in advance for your reply.

Best regards

[.paul.]

try this:

dgvGriglia.AutoGenerateColumns = False
dgvGriglia.AllowUserToAddRows = False
dgvGriglia.AllowUserToDeleteRows = False
dgvGriglia.AllowUserToResizeRows = False
dgvGriglia.AllowUserToOrderColumns = True
dgvGriglia.MultiSelect = False
dgvGriglia.ReadOnly = True
dgvGriglia.RowHeadersVisible = False
dgvGriglia.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
dgvGriglia.SelectionMode = DataGridViewSelectionMode.FullRowSelect
 
Dim oCol As DataGridViewTextBoxColumn = DirectCast(dgvGriglia.Columns(0), DataGridViewTextBoxColumn)
oCol.Name = cColName
oCol.DefaultCellStyle.Alignment = oAlignment
oCol.Width = VB6.TwipsToPixelsX(nTwWidth)
oCol.HeaderText = cHeaderText
oCol.DataPropertyName = cDataPropertyName
dgvGriglia.Columns(0).SortMode = DataGridViewColumnSortMode.Automatic
 
dgvGriglia.DataSource = ds.Tables(0).Select(cFilter)

[motogpdesmo16]

Hi Paul, thank you for your reply.
These set of instructions:

Dim oCol As New DataGridViewTextBoxColumn
    oCol.Name = cColName
    oCol.DefaultCellStyle.Alignment = oAlignment
    oCol.Width = VB6.TwipsToPixelsX(nTwWidth)
    oCol.HeaderText = cHeaderText
    oCol.DataPropertyName = cDataPropertyName
    dgvGriglia.columns.add(oCol)

is in another function which returns a DataGridViewTextBoxColumn. So I can't call

DirectCast(dgvGriglia.Columns(0), DataGridViewTextBoxColumn

becaus dgvGriglia has no columns.
So finally the code is this:

what.Columns.Add(Add_TextBoxColToDGV("ColNome", 3000, "Nome"))

where:
-the first parameter ColName is the cColName;
-the second parameter 3000 is the nWidth;
-the third parameter Nome is the cHeaderText.
oAlignment and cDataPropertyName are optional parameters of my function.
So how can solve?
Thank you.

[.paul.]

try adding your columns at design time if you don't want to autogenerate them, then try the code i posted in post #2

[.paul.]

does it have to be a function that returns a DataGridViewTextBoxColumn? can't it be a sub where you pass the DataGridViewColumn as a parameter?

[motogpdesmo16]

does it have to be a function that returns a DataGridViewTextBoxColumn? can't it be a sub where you pass the DataGridViewColumn as a parameter?

Sorry but the explanation It's not enough clear (for me)
If I convert it from Function to Sub, and pass dgvGriglia as parameter, it has ever 0 columns, so how can i call

DirectCast(dgvGriglia.Columns(0), DataGridViewTextBoxColumn)

if my grid has no columns??

[motogpdesmo16]

well, the problem is that my columns are not databound columns, because, after the datagridview datasource property assignment, I loop on each datarow to custom each cell content:

For Each oRiga As DataRow In ds.Tables(0).Select(cFilter)
        dgvGriglia.Item(0, nRiga).Value = oRiga.Item("MYCOLUMN0").ToString
        dgvGriglia.Item(1, nRiga).Value = oRiga.Item("MYCOLUMN1").ToString & " _  " &oRiga.Item("MYCOLUMN2").ToString
...
...
        nRiga = nRiga + 1
      Next

So, it's possible to call the sort method on non bound columns??

[motogpdesmo16]

any help about my problem??
Thank you in advance.

[MonkOFox]

If you want to sort, you could re-sort the datatable you have in memory, then redo your looping process to add the rows. if nothing else, add your rows, run your program and see if when you click on the column headers if it re-sorts..

Justin

[kareninstructor]

Personally I would setup my data in a BindingSource and make that the DataGridView source which then can be sorted using the BindingSource sort method. To keep the current row current you can work that out in the BindingSource position changed event.

Another possibility is to use code as shown below

DataGridView1.Sort(DataGridView1.Columns(0), System.ComponentModel.ListSortDirection.Ascending)

[motogpdesmo16]

Sorry for the late reply.
I can't call the sort method of the DataGridView, because the column are all unbounded.
So the error is DataGridView control must be bound to an IBindingList object to be sorted.
Moreover I can't pass the orderby parameter at the Select method of the dataset when I've this instruction:

dgvGriglia.DataSource = ds.Tables(0).Select(cFilter, cOrderBy)

because the order clause is not depending on the database column name, but depending on datagridview column (remember that in a datagridview column I can merge two or more database column)...
It's very hard now...

[kareninstructor]

Here is a suggestion, retrieve your data into a DataTable and manipulate it there rather than directly in the DataGridView columns then assign the DataTable to a BindingSource followed by using the BindingSource as the DataSource for the DataGridView. In short now any changes made to the DataGridView will be made to the underlying data table.

The attached demo is in VS2008.
Create a new Windows project, on form1 place a DataGridView and three buttons cmdTest, cmdSort, cmdClose. Next copy all the code in my Form1 into your form1. Copy Customer.xml from my project to the same location in your project.

Build and run.


Note in Sub LoadData I load the xml file into a DataTable, manually setup the columns for the DataGridView, assign data to the BindingSource which in turn is the data source for the DataGridView. Next I use your settings (I commented this section), sort on column 0 and set focus to the Grid.

cmdSort button flip flops the sort on column zero between ascending/descending.

cmdTest replaces

dgvGriglia.DataSource = ds.Tables(0).Select(cFilter, cOrderBy)

with

      If mCustomersBindingSource.Filter Is Nothing Then
         mCustomersBindingSource.Filter = "City='London'"
      Else
         mCustomersBindingSource.Filter = ""
      End If

which would have been the select then executes code in cmdSort to do the sorting/order by portion of the select statement.


The only thing not included is manipulation of data as you mentioned in the part where you change data in columns of the DataGridView instead of changing it in the DataTable which is easy enough to do.

Hope this helps.

[motogpdesmo16]

Doh, I've just resolved the problem a few hours ago in this way:

dgvGriglia.AutoGenerateColumns = True
Dim dt As New DataTable
dt.Columns.Add(xAdd_ColToDT("Column0", "Header of column0"))
dt.Columns.Add(xAdd_ColToDT("Column1", "Header of column1"))
...
dt.Columns.Add(xAdd_ColToDT("ColumnN", "Header of columnN."))
    For Each origa As DataRow In ds.Tables(0).Select(cFilter)
      Dim oNewRiga As DataRow = dt.NewRow
      oNewRiga.Item(0) = origa.Item("FIELD0")
      oNewRiga.Item(1) = origa.Item("FIELD1")
      dt.Rows.Add(oNewRiga)
    Next
    ds.Tables.Add(dt)
    dgvGriglia.DataSource = ds.Tables(1)

attention: dgvGriglia.DataSource = ds.Tables(1) is not an error because the table 0 is the source table which I manipulate in this procedure.
I resolved anyway, but do you think could be a good solution??
Thank you!