-
Sep 25th, 2022, 04:52 AM
#1
Thread Starter
New Member
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!
-
Sep 25th, 2022, 06:38 AM
#2
Re: Simplify or filter data
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|