Clearing DataGridView bound to DataSet without losing headers VB.NET

Question

I've currently got an application that you can search for a specific location, owner, etc. which will return some basic information about the device(s). I have this information pulled via a SQL query and placed into a DataSet which is then placed into a DataGridView object. I am trying to implement a Clear button which will remove the search terms as well as the data in the DataGridView object.

Unfortunately, I cannot use DataGridView.Rows.Clear() as my DataGridView is bound to a DataSet. I am running into difficulty using DataGridView.DataSource = Nothing as this is not only removing the data that was displayed, but also removes my headers for columns that I have created for the DataGridView. I have tried a few solutions I've found such as DataGridView.Refresh() and DataGridView.AutoGenerateColumns = false but these have thus far removed the column headers.

This is the code I am using for adding rows to the DataGridView object;

    Dim con As New SqlClient.SqlConnection(getConnectionString)
        Dim cmd As New SqlCommand("SELECT asset.assetID, owner.ownerName, model.brand, asset.modelID, deviceOwner.serialNumber, deviceOwner.location, asset.dateAssessed from asset 
                                  join deviceOwner on asset.assetID = deviceOwner.assetID
                                  join owner on deviceOwner.ownerID = owner.OwnerID
                                  join model on asset.modelID = model.modelID where asset.dateAssessed = @checkDate", con)
        cmd.Parameters.AddWithValue("@checkDate", searchEnterDate.Text.ToString)
        con.Open()
        Dim myDA As New SqlDataAdapter(cmd)
        myDA.Fill(myDataSet, "MyTable")
        displayResults.DataSource = myDataSet.Tables("MyTable").DefaultView
        con.Close()
        con = Nothing

I am looking for a solution that will allow me to clear the rows from the DataGridView without removing the column headers so I can use the clear button if necessary but continue to search with column headers still displaying.

How about creating a DataTable with just the column headers and assign it as your DataSource when you want to "blank" it? — Andrew Mortimer
If the col headers are the same it means the query may essentially the same, just filtered. Rather than running a new query you could try to use the DefaultView.RowFilter. — Ňɏssa Pøngjǣrdenlarp
I use a DataView derived from the DataTable and set the filter as needed. — rheitzman

Tim Schmelter Tim Schmelter · Accepted Answer · 2016-06-29T15:13:11

You could Clone the DataTable which keeps columns but "removes" data:

Dim oldTable As DataTable = CType(displayResults.DataSource, DataView).Table
Dim emptyTable As DataTable = oldTable.Clone()
displayResults.DataSource = emptyTable ' or emptyTable.DefaultView

or use DataRowCollection.Clear on the old table which could cause more side-effects:

oldTable.Rows.Clear()

Clearing DataGridView bound to DataSet without losing headers VB.NET

1 Answers