Sorting a datatable before filter

[Kevmeister]

Hi all,

I have got some code i have put together which is three parts there but i am having some problem with the sort aspect of it.

What i am doing is to enable a user to specify in a combobox on a form a threshold (integer). On a button click the code steps through a datatable until the cumilative value of a field in the records row are equal or dont exceed the figure specified. It then selects these rows and presents them in a datgridview in another form. This all works just fine.

What i am struggling with is undertaking a sort on another field before it starts the filter part. It appears to revert to the default order. I know this is me, hence if someone can highlight where im going wrong be most appreciated. Here is my code:

If ComboBox1.Text = "Co2 saving in tonnes" Then
            Dim table As New DataTable
            Dim limit As Integer = ComboBox2.Text ' Your threshold
            Dim r As Integer ' Iterator
            Dim s As Integer = 0 ' Holds Sum. Change to Decimal or Double if needed.
            dtOpportunity.DefaultView.Sort = "Payback"
            table = dtOpportunity
            
            For r = 0 To table.Rows.Count - 1
                s += CInt(table.Rows(r).Item("Co2Saving"))
                If s < limit Then
                    newdataTable.Rows.Add(table.Rows(r).ItemArray)
                Else
                    Exit For
                End If
            Next
            Form9.DataGridView1.DataSource = newdataTable
            Form9.Show()

[jmcilhinney]

When you sort a DataTable's DefaultView you are sorting the DefaultView, not the DataTable itself. If you want to access the rows in sorted order you need to loop through the DefaultView, not the Rows.

[Kevmeister]

Thanks for this, as you can tell i am relatively new to vb. Hence apologies for stupid questions. I can see what your saying about the default view. I can see the difference when i reference the table and default view as the DataGridView datasource. However, i am struggling with stepping through records/rows and reference a field in the defaultview in the same way as my code above. I have experimented but not having any joy. I have had a look on the web for an example to give me a clue, but no joy. Will keep searching but a clue would be great.

Kev

[jmcilhinney]

Actually, there is no difference between binding a DataTable and binding its DefaultView. When you bind a DataTable it's actually the data from the DeafultView that you see. That's how the data in the UI can be sorted and filtered, even though you can't sort and filter a DataTable.

Instead of looping through the Rows of the DataTable, you simply loop through the DefaultView. Each item is a DataRowView rather than a DataRow. They can be used in pretty much the same way in most cases, e.g. indexing by column name or ordinal to get a field value. In situations where you specifically need a DataRow, e.g. to get the ItemArray, the DataRowView has a Row property that returns the underlying DataRow.

That said, you shouldn't be using ItemArray anyway. The DataTable has an ImportRow method that will import a row from another DataTable with the same schema. By the way, in case you're not currently, you should be using the existing DataTable's Clone method to create another DataTable with the same schema.

[Kevmeister]

many thanks again. I will give this a go.

[Kevmeister]

I am sorry to be a pain again, i realise this is a relativley simple task, but I have made many attempts to do this and while through the debug window it can see the data. I have referred to my manuals etc but simply cant work out how to grab the row from the default view to add to the table (will use the clone table, but just trying to get this to work before refining).

I have altered my code to point at the default view (and it works fine) but fall down when it comes to actually selecting the relevant row from the defaultview. Can anyone help.

Kev

[jmcilhinney]

you simply loop through the DefaultView. Each item is a DataRowView rather than a DataRow.

the DataRowView has a Row property that returns the underlying DataRow.

The DataTable has an ImportRow method that will import a row from another DataTable with the same schema.

you should be using the existing DataTable's Clone method to create another DataTable with the same schema.

table2 = table1.Clone
 
For Each rowView As DataRowView in table1.DefaultView
    table2.ImportRow(rowView.Row)
Next

[Kevmeister]

I apologise for the repetition in this thread, i am finding my way with vb and often have troubles joining up the dots. However, your patience, persistence and help is most appreciated. Many thanks Kev