Can I page through grouped data in DataGridView?

Question

I have a simple VB.NET 2008 app that helps users to edit fields in the database. Simple enough as a first "real" project in .NET, right?

For one table, I am currently using a DataGridView so it can be edited straight up. However, instead of offering the user the entire table, I'd like to group the data by the 'CompanyNumber' column and use a navigator to page through. In other words, I'd like the DataGridView to show me all the lines related to one company, then click the "next" arrow to show the next company, etc.

(I know I could do this with Xceed DataGrid, but I'm using Windows Forms not WPF, and I'd really prefer to do this with "pure" ADO.NET for this project.)

---

Update 2009-09-28:

So I have created a ComboBox filled from the same BindingSource, and configured its SelectedIndexChanged to change the Filter value on the DataGridView.

But still, filling the ComboBox--which should be easy!--continues to be a problem. I can either:

(a) fill it from the BindingSource, in which case I see multiples of each 'CompanyNumber' and I can't figure out a way to show only distinct values, or

(b) create another TableAdapter in the data source which is just a "Select DISTINCT CompanyNumber..." query, which mostly works, except that that first value of the list changes when I change the selection (e.g. if the ComboBox shows "100, 101, 102, 103" and I pick "102", then the list will show as "102, 101, 102, 103").

Any recommendations?

(Also, bonus if you can suggest how to make the BindingNavigator's arrows page through the 'CompanyNumber' filters instead of the items in the DataGridView... which is what I'd really like to see.)

Answer 1

What you could do is just force the DataGridView to sort CompanyName, this way all rows with the same company name are next to each other and the user can navigate the data grids with the paging that comes with it.

Alternatly, you could follow through with your combobox/DropDownList idea, which would be best. From what I understand when you select an item in the combobox everything in it changes?

Another way is to create two separate buttons, "Previous" "Next", that when clicked, will change the DataGridView's binding source to only show a certain company. You would need to store an array of company names, then store what the current DataGridView's binding source is displaying.

Answer 2

I ended up figuring it out myself, and the solution is clean and simple. Here are the basic steps:

• create a DataView off of the table out of the DataSet
• use the DataView.ToTable() method to create a new table filtered to only distinct values from the needed column ('CompanyNumber')
• create a BindingSource which uses the new DataTable as its DataSource
• bind the ComboBox to the new BindingSource
• bind the BindingNavigator to the new BindingSource

Because the ComboBox and the BindingNavigator use the same BindingSource, they will update each other with the changes automagically.

Here's the rough code:

Private Sub CoNumsComboxBox_LoadData()

    Dim dvCoNums As DataView, dtCoNums As DataTable
    dvCoNums = New DataView(Me.ODBCDataSet.Tables("CompanyFundProfile"))
    dvCoNums.Sort = "CompanyNumber ASC, FundNumber ASC"
    dtCoNums = dvCoNums.ToTable("CompanyFundProfile", True, "CompanyNumber")
    CoNums_BindingSource.DataSource = dtCoNums

    CoNumsComboBox.DataSource = CoNums_BindingSource
    CoNumsComboBox.DisplayMember = "CompanyNumber"
    CoNumsComboBox.ValueMember = "CompanyNumber"

    'attach handler which changes DataGridView1's filter when this changes
    AddHandler ToolStripComboBox1.SelectedIndexChanged, AddressOf CoNumsComboBox_SelectedIndexChanged

    CompanyFundProfile_BindingNavigator.BindingSource = CoNums_BindingSource

End Sub