Can I build a DataGridView using an existing dataset without disturbing the data"

[Darby]

I have a Visual Basic app I started to write several years ago,( Visual Studio Express 2017) and have continually upgraded it to Visual Studio Community 2022.

The app uses a SQL Database to hold the movie data

The program keeps track of my 1000+ DVD collection, where it is in storage, and allows me to search using specific criteria entered by the user. The user can also play the movie if desired.

When I open the program, it opens the database, fills a dataset (DVD_ListDataSet) and then closes the database.

There is a Sub Routine called TSSearch that allows the user to enter specific criteria for the app to locate and display all the records that match that specific criteria on a DataGridView. Currently once I set the search string, I reopen the database and create a new DataSet. Is it possible to use the original DataSet
(DVD_ListDataSet) so I don’t need to reopen the database?

What I have now:

	Private ReadOnly srchDataAdapter As New SqlDataAdapter()
Dim srchString As String = "SELECT Movie, Title, Plot, Cast,Rated,Genre,Book,Page,Slot FROM DVD_List Where "
Private ReadOnly srchDataset As New DataSet
Private ReadOnly srchConnection As New SqlConnection("Data Source = .\SQLEXPRESS;  
        AttachDBFilename='D:\Data Files 092015\DVD_List.MDF';
        Integrated security = True;
        Connect Timeout = 30")

Here I build the search string using the ‘Where’ clause to get data in each column that fits the criteria entered by the user.

   'ENTER ITEMS FOR GRIDVIEW SEARCH

            ' IF USER ENTERS TITLE INFO
            '
            If Form1.tbTitle <> "" Then

If Form1.tbTitle = "ALL" And Form1.tbCast = "" And Form1.tbRate = "" And Form1.tbGenre = "" Then
srchString = "SELECT Movie,Title,Plot,Cast,Rated,Genre,Trim(Book),Trim(Page),Trim(Slot) FROM DVD_List "
                Else
                    srchString = srchString & "Title Like '%" & Form1.tbTitle & "%'"
                End If

            End If
            '
            ' IF USER ENTERS CAST INFO
            '
            If Form1.tbCast <> "" And Form1.tbTitle <> "ALL" Then
                If Form1.tbTitle <> "" Then
                    srchString &= " AND "
                End If
                srchString = srchString & "Cast Like '%" & Form1.tbCast & "%'"
            End If
            '
            ' IF USER ENTERS A RATING
            '
            If Form1.tbRate <> "" And Form1.tbTitle <> "ALL" Then
    If Form1.tbTitle <> "" Or Form1.tbCast <> "" Or Form1.tbMovie <> ""   Then
                srchString &= " AND "
                End If
                srchString = srchString & "Rated = '" & Form1.tbRate & "'"
            End If
            '
            ' IF USER ENTERS A GENRE
            '
            If Form1.tbGenre <> "" And Form1.tbTitle <> "ALL" Then
                If Form1.tbTitle <> "" Or Form1.tbCast <> "" Or Form1.tbRate <> "" Then
                srchString &= " AND "
                End If
                srchString = srchString & "Genre Like '%" & Form1.tbGenre & "%'"
            End If
        End If

Then I open the connection and fill the srchDataSet and close the connection
'SET THE COMMAND PROPERTIES

        srchDataAdapter.SelectCommand = New SqlCommand With
            {
            .Connection = srchConnection,
            .CommandText = srchString,
            .CommandType = CommandType.Text
            }

        'OPEN THE DATA CONNECTION

        Try
            srchConnection.Open()
        Catch ex As Exception

MessageBox.Show(ex.Message, "OPEN ERROR", MessageBoxButtons.OK,    MessageBoxIcon.Error)
        End Try

        ' FILL THE DATASET

        srchDataAdapter.Fill(srchDataset, "DVD_LIST")

        'Close THE DATA CONNECTION

        srchConnection.Close()

What I would like to do is, instead of reopening the database and filling the srchDataSet, would be to use the original ‘DVD_ListDataSet’
What I have tried:
I remove the code that creates the connection and opens the database, creates the dataset, and closes the connection. Then set srchDataSet to the original dataset, DVD_ListDataset.
Private ReadOnly srchDataAdapter As New SqlDataAdapter()
Dim srchString As String = ""
Private ReadOnly srchDataset As New DVD_ListDataSet

That just copies THE ORIGINAL DATASET into the srchDataSet which I thought would work but, nothing happens, goes to never-neverland and doesn’t come back.

Not sure what I am doing wrong…or nif it would even work!!!

[wes4dbt]

Short answer is yes.

A Dataset is a collection of DataTables. A datatable has a "Select" method. So,

Dim rows() = yourTable.Select(expression)

Edit:

I should add, if you are using the same datagridview to display the search results as the dgv that displays the original results then you should put the original datatable into a BindingSource, use the BS as the DGV Datasource and then when you search, just filter the BS.