Refreshing Datagridview From Dataset Issue

Question

I seem to be pulling my hair out over something that seems pretty straight forward in my eyes. I cannot get a datagridview control to update correctly after altering a dataset.

I initially populate a datagridview from a dataset and auto generate the columns no problem. However, if I regenerate my dataset with new data, the datagridview will display the new data correctly, but won't remove the old column headers.

For example, I have a query that gives me Store Name and Manager and auto populates my datagridview. Then if I change my query to just give me Store Name and Supervisor, it gives me Store Name, Manager (with blank column) and Supervisor.

I have tried datagridview.refresh() and datagridview.update() but nothing seems to work. Here is my code:

    MySQLConn.Open()

    Dim ExQry As New MySqlCommand(QryStr, MySQLConn)
    ExQry.CommandType = CommandType.Text
    Dim da As New MySqlDataAdapter(ExQry)
    dasCustomQryData.Clear() 'my dataset is called dasCustomQryData

    da.Fill(dasCustomQryData, "QryData")
    da.Update(dasCustomQryData, "QryData")

    With dgvCustomQuery
        .DataSource = Nothing
        .Dock = DockStyle.Fill
        .AutoGenerateColumns = True
        .DataSource = dasCustomQryData
        .DataMember = "QryData"
        .Refresh()
        .Visible = True
    End With

    MySQLConn.Close()
    da.Dispose()
    dasCustomQryData.Dispose()

So, when I want to update my datagridview, I plugin a new QryStr to the above code and it rebuilds my dataset. The dataset is getting updated, because the datagridview contains the correct data, however, my problem is that the datagridview isn't clearing the old columns that aren't populated anymore.

All help appreciated. Thanks

Just cause there is no data in a column does not mean it will stop showing a column that exists in the dataTable. — OneFineDay
So how do I check what data is in my dataset? My query makes no reference to the manager field and I am clearing my dataset before repopulating it with the new query, so how can I check if the field still exists in my dataset? — Riples
You could probably loop thru all records returned and if the number of empty fields matches the rows.Count then make that column not visible. — OneFineDay
Okay, I can try that, but it still doesn't explain how I'm clearing the dataset before running a new query and ending up with old data? Surely clearing the dataset and running an entirely different query shouldn't give me old data. I think the problem is more related to the datagridview than the dataset. — Riples

Manny265 Manny265 · Accepted Answer · 2013-08-13T04:45:44

I would recommend you create a connection and a dataset straight from VB and the in the dataset it shows the tables. At the bottom of the tables is a standard query but u can create your own...with variables as parameters and then you can call the query through the table adapter and assign this straight to the datagridview.
That always seems to work for my programs. What sort of database are u using? Might be obvious but just to make sure, the datagridview is not bound right?
Sample from one of my projects
dgData.DataSource = TblEventsTableAdapter.ViewEvent(cmbEvents.SelectedItem.ToString)
where dgdata is obviously datagridview. ViewEvent is a custom query created in the dataset and takes in 1argument. Hope this is of use to you

Refreshing Datagridview From Dataset Issue

2 Answers