DataGridView does not refresh after dataset update vb.net

Question

I have a vb.net form with a dataGridView

The dataGridView data source is the dgvTableAdapter with this sql statement

SELECT membres.ID, membres.refere_par, bands.titre, 
       membres_1.prenom & ' ' & membres_1.nom  AS reference_nom
FROM ((bands INNER JOIN membres ON bands.ID = membres.[band]) 
      INNER JOIN membres membres_1 ON membres.refere_par = membres_1.ID)

I delete membres from the membres Table like this

' Get member id 
Dim userId As Integer 
userId = DataGridView1.Item( 0,0).Value

' Delete the member
Me.MeoshowDataSet2.membres.FindByID(userId).Delete()
Me.MembresTableAdapter.Update(Me.MeoshowDataSet2)

' Refresh datagrid
dataGridView1.Refresh() ' does nothing

I know the delete statement works because I saw the changes in the database. If I close the form and reopen it, the dataGridView is up to date.

The membres table is an access table

I'm running the app in visual 2010 debug mode.

Solution 1

The usual way of doing this is to reset the DataSource of the DataGridView.

Try like this code (with correct code to provide the right table from the dataset):

dataGridView1.DataSource = typeof(List); 
dataGridView1.DataSource = dataset.Tables["your table"];

Calling .Refresh() doesn't work since it only forces a repaint, but the code that paints the grid doesn't know of the changes.

Solution 2

I stumbled upon this while searching for exact same problem. Didn't find it online though. Here's what worked for me:

Public Sub RefreshData()
    dTable.Clear()
    dAdapter.Fill(dTable)
    dtaDataGrid.DataSource = dTable
End Sub

Private Sub btnRefresh_Click(sender As System.Object, e As System.EventArgs) Handles btnRefresh.Click
    RefreshData()

    ClearAllTextBox(Me)
End Sub

Cleared all the data in the data table first then refilled it with the data from the data adapter since you said that the database was updated with your code, only that it didn't refresh.

Solution 3

You can also use this:

DirectCast(dataGridView1.DataSource, DataTable).AcceptChanges()

Just replace dataGridView1. The 2nd parameter is the DataTable class.

Comments

[pec]

I did not use dataGridView1.DataSource = typeof(List); but reassigning the dataset did work. Thanks!

[David Hall]

I think you need to - that is the part that fixes the problem! Essentially you need to empty the datasource - you could set the datasource to null, but using typeof(List) will maintain the autogenerated columns. However, if it does work without that, great :)

[pec]

I used dataGridView1.Datasouce = Nothing

[David Hall]

Ah - yes, that is roughly the same (forgot that you were working in vb.net).

[Origin]

Couldn't this also be solved with a BindingSource to notify the DGV of the changes in the DataTable?

[David Hall]

@Origin I've seen the BindingSource work and not work - that is what I usually try first (and probably have a few answers on here suggesting that) and I'm honestly not 100% sure when it will or won't work.

[David Hall]

@Origin I seem to remember that part of the issue is that some source of data just don't tell anyone that they have changed (they don't raise the list changed event) so you are out of luck - EVEN when you know that the list has changed. One solution is to use the bindingsource and tell it to raise that event. Last encountered this with an Oracle TableAdapter, can't recall if others behave in the same annoying way.

[David Hall]

@Origin final comment - just browsed through you answers and saw the one where you advocate BindingList<T> over table adapters. That is exactly what I do too, for reasons like this issue - I find using the BindingList very trouble free compared to table adapters.

[LuckyLuke82]

thanks, It worked for me too. Reseting datasource to nothing just scrambles my Datagrid because I have DataPropertyName set for each column. Your suggestion works perfect !