Sorting or Filtering a DataGridView leaves the Underlying Dataset unchanged

Question

When I sort or filter a datagridview, the underlying Dataset remains unchanged so I can not use NewTableRow as a way to update the database:

The following is an example of my sort
Dim SortSQL As String
Dim SaveDataSource As BindingSource
Dim NewRow As VideoDBDataSet.RecTableRow

    SaveDataSource = dgvRec.DataSource 'dgvRec is my DataGridView
    SortSQL = "Title ASC, Release ASC"
    adoMovieRS.Sort = SortSQL
    adoMovieRS.Requery()

    Validate()
    RecTableBindingSource.EndEdit()
    RecTableBindingSource.Sort = SortSQL
    RecTableTableAdapter.Fill(VideoDBDataSet.RecTable)
    RecTableTableAdapter.Update(VideoDBDataSet.RecTable)
    RecTableAdapterManager.UpdateAll(VideoDBDataSet)
    VideoDBDataSet.AcceptChanges()
    NewRow = VideoDBDataSet.RecTable.Rows(0)

The NewRow is filled with the Original first record in the Dataset, not the sorted one.
I use the record within the datagridview to display a form that allows me to modify
the record content but sending that record back to the database via the DataSet
is not possible this way because the dataset indexes do not match the datagridview now.

BTW. The DataGridView sorts and filters just fine and I do not want to create a temporary
TableAdapter and Dataset as that will mess up the changes I have made to the DGV's
column order.

Thanks for any help, Lance

Answer 1

That is by design. A DGV is a UI around a data source which is the master data. Sorting and filtering have no impact on the underlying data, they are just UI things. You could easily imagine a scenario where 2 different controls are mapped to the same data source but sort and filter the data differently.

The proper approach in all cases is to use the underlying unique identifier of the item that is contained in your data source and never rely on any UI aspects (like row index). In order to update a row in a DB you have to provide the WHERE clause that uniquely identifies the row. This is your key and must be stored in your data source. Whether you show it in the DGV or not is irrelevant. I'm going to assume you're using Winforms here but it doesn't really matter other than the name of the members being used.

Given a DGV row you can get to the underlying data source item using the DataBoundItem. What this type actually is depends upon how you bound the data originally. If you're using a dataset, for example, then it would be a DataRow. Now you have access to the original item and therefore the key of the object. You can use this when you make calls back to your DB. For a new object, the key will not be set and will have its default value. You can use this to determine whether an edit is an add or update, if needed.

You didn't clarify how you're getting into the code that you ultimately need the key for but if you're reacting to DGV events then most events have the item being worked on as part of the event arguments. Alternatively the DGV tracks the current row (and cell) so you can get to either given either the event arguments or DGV itself. Of course if you're using BindingSource and you add the new item using the AddNew then the corresponding event has the new item as well.