Simplify or filter data

Hi guys,

I am loading some information from an access database, and showing it in the datagridview forms. Since I am searching in multiple tables (same access file), I have to use the Select feature multiple times, afterwards loading them into the specified datagridviews.

My question is if its possible to run the "Select * FROM" once, and then filter the required information in the datatable then transferring the filtered information into the specified datagridviews.

In short, using the populated datatable then filter its contents and spread the results into multiple datagridviews.

I am using this code:

Code:

---

                        sqlStencil = "SELECT [ID],[ToolLocation],[ToolShelf],[ToolPosition],[ToolID],[ToolSpecialName],[ToolSize] FROM tbl_tools WHERE ToolType LIKE '%' & @type & '%' OR ToolSpecialName LIKE '%' & @type & '%'"

            cmd.Parameters.AddWithValue("@type", txtType.Text)
            cmd.Connection = toolDB
            cmd.CommandText = sqlStencil
            da.SelectCommand = cmd
            da.Fill(dataTable)

            If (dataTable.Rows.Count > 0) Then
                kard = i
                i += 1
                tblResult.Controls.Add(lbKardex, 0, kard)
                tblResult.Controls.Add(dgvKardex, 1, kard)
            End If

            sqlSupport = "SELECT [ID],[ToolLocation],[ToolShelf],[ToolPosition],[ToolID],[ToolSpecialName],[ToolBackColor] FROM tblSupport WHERE ToolType LIKE '%' & @type & '%'"
            cmd.Parameters.AddWithValue("@type", txtType.Text)
            cmd.Connection = toolDB
            cmd.CommandText = sqlSupport
            da.SelectCommand = cmd
            da.Fill(dataTable2)

            If (dataTable2.Rows.Count > 0) Then
                supp += i
                i += 1
                tblResult.Controls.Add(lbSupport, 0, supp)
                tblResult.Controls.Add(dgvSupport, 1, supp)
            End If

            sqlSample = "SELECT [ID],[ToolLocation],[ToolShelf],[ToolPosition],[ToolID],[ToolSpecialName] FROM tblSMPsupport WHERE ToolType LIKE '%' & @type & '%'"
            cmd.Parameters.AddWithValue("@type", txtType.Text)
            cmd.Connection = toolDB
            cmd.CommandText = sqlSample
            da.SelectCommand = cmd
            da.Fill(dataTable3)

            If (dataTable3.Rows.Count > 0) Then
                sample += i
                i += 1
                tblResult.Controls.Add(lbSample, 0, sample)
                tblResult.Controls.Add(dgvSample, 1, sample)
            End If

            sqlKHD = "SELECT [ID],[ToolLocation],[ToolShelf],[ToolPosition],[ToolID],[ToolSpecialName] FROM tblKHD WHERE ToolType LIKE '%' & @type & '%' OR ToolSpecialName LIKE '%' & @ECAMsten & '%'"
            cmd.Parameters.AddWithValue("@type", txtType.Text)
            cmd.Connection = toolDB
            cmd.CommandText = sqlKHD
            da.SelectCommand = cmd
            da.Fill(dataTable4)

            If (dataTable4.Rows.Count > 0) Then
                khd += i
                i += 1
                tblResult.Controls.Add(lbKHD, 0, khd)
                tblResult.Controls.Add(dgvKHD, 1, khd)
            End If

---

As you can see, I am using Select multiple times, and loading the information 1 by 1.
Thank you in advance and have a great weekend!

You would use multiple DataViews for this. Every DataTable has a DefaultView and that is where the data comes from when you bind the DataTable itself. If you want multiple views of the same data though, you should create multiple DataViews explicitly. Add a BindingSource to your form for each DataGridView, create the DataViews in code, bind the DataViews to the BindingSources and the BindingSources to the DataGridViews. You then do the filtering and sorting via the BindingSources. E.g.

vb.net Code:

Dim table As New DataTable
 
Using adapter As New SqlDataAdapter("SELECT * FROM Person", "connection string here")
    adapter.Fill(table)
End Using
 
Dim activeView As New DataView(table)
Dim inactiveView As New DataView(table)
 
activeBindingSource.Filter = "IsActive = True"
activeBindingSource.DataSource = activeView
activeDataGridView.DataSource = activeBindingSource
 
inactiveBindingSource.Filter = "IsActive = False"
inactiveBindingSource.DataSource = inactiveView
inactiveDataGridView.DataSource = inactiveBindingSource

That would display all the active records in one grid and the inactive records in the other. If you were to edit a DataRow in the DataTable, either via either grid or in code, you'd see it disappear from one grid and appear in the other, courtesy of the filters.