-
Dec 1st, 2017, 03:54 PM
#1
Thread Starter
Lively Member
[RESOLVED] Filter a DataSet by criteria in specific column
Hello,
I have a DataSet with about 1,000 rows. The first column of this DataSet (other than the key column) has the numbers 1 through 4 depending on the row. I'm trying to display the row information in some text boxes dependent upon what number that first column has.
So for example if I press button 1 (btnDisplayNumbers1), my textboxes should display information from rows containing the number 1 in column 1.
Is splitting the DataSet into four different DataSets the best way to do this? So for example:
Dim ds as New DataSet
Dim ds1 as New DataSet 'contains all rows that have the number 1 in the first column
Dim ds2 as New DataSet 'contains all rows that have the number 2 in the first column
Dim ds3 as New DataSet 'contains all rows that have the number 3 in the first column
Dim ds4 as New DataSet 'contains all rows that have the number 4 in the first column
There are about 1,000 rows in total, so this needs to be done preferably in the most efficient and quickest manner possible.
Can anyone help please?
-
Dec 1st, 2017, 04:36 PM
#2
Re: Filter a DataSet by criteria in specific column
First, let's get your terminology correct because if you don't, it's going to cause issues down the road. Datasets do NOT contain rows. Period. Datasets contain datatables. No more, no less. It's the DataTables that contains rows.
Now for the specific problem - no you should not be splitting up the data like that. What you should be doing is creating a DataView off of the DataTable, assigniung it to the DataSource of the grid (assuming you are display the data in a DataGidView) and then when you need to, setting the RowFilter of the DV accordingly. That's going to be the quickest and easiest way to go about it.
Code:
'Sets the grid to the default view of the datatable
Me.DataGridView1.DataSource = gridData.DefaultView
Code:
'Changes the RowFilter to only show rows where the col titled "Col2" is set to value of 1
gridData.DefaultView.RowFilter ="Col2 = 1"
-tg
-
Dec 1st, 2017, 04:43 PM
#3
Thread Starter
Lively Member
Re: Filter a DataSet by criteria in specific column
@techgnome, thank you! I'm actually not displaying the data in a DataGridView. Instead, I'm going to be pulling information from there into textboxes.
So for example, I have 5 columns filled out for each row in the DataTable within ds (did I get the terminology right?). The goal here is to filter ds' DataTable into then DataViews (as you suggest) dependent upon what number is in column 2 of those rows and THEN display information of say, DataView1 into Textbox1, Textbox2, etc.
So ultimately, I would have four different DataViews containing different information (again, dependent upon what number is in column 2).
Last edited by lolikols; Dec 1st, 2017 at 04:46 PM.
-
Dec 2nd, 2017, 06:53 AM
#4
Re: Filter a DataSet by criteria in specific column
You don't need different DataViews, you just need to set the RowFilter to suit whatever you want to display.
So, based on this:
So for example if I press button 1 (btnDisplayNumbers1), my textboxes should display information from rows containing the number 1 in column 1.
...in btnDisplayNumbers1, you would have this code:
Code:
gridData.DefaultView.RowFilter ="Col2 = 1"
...and if you have btnDisplayNumbers2 it would have this:
Code:
gridData.DefaultView.RowFilter ="Col2 = 2"
(note that gridData isn't actually related to a grid at all, it is just the name tg picked for the DataTable)
-
Dec 2nd, 2017, 09:20 PM
#5
Re: Filter a DataSet by criteria in specific column
Indeed... as si mentioned, you don't need multuiple gridviews.... all you need is the datatable... just set the .RowFilter of the .DefaultView of the datatable, and BAM! Bob's your uncle. Your datatable will be filtered. And for the record, I NEVER suggested multiple views... on the contrary, I suggested filtering ONE view, which is the .DefaultView... if you then had it bound to a grid, you would see the changes to the datatable. Either way, once you have set the .RowFilter, you can then loop through the rows in the datatable and get the data you need.
-tg
-
Dec 4th, 2017, 08:39 AM
#6
Thread Starter
Lively Member
Re: Filter a DataSet by criteria in specific column
Thank you both very much - quick question - how do I reference the datatable that is within my dataset? In the original post, I have declared "Dim DataSet As New DataSet" which is the DataSet that I am using, and DefaultView is not a property of DataSet .
-
Dec 4th, 2017, 09:05 AM
#7
Re: Filter a DataSet by criteria in specific column
What did I say? "Datasets contain datatables" ... Datasets are nothing more than a collection of Table (<-- hint hint)...
I know that DefaultView isn't a member of DataSet, I never said it was. That's also why I was trying to correct your terminology upfront. A couple of questions - if you didn't know how to access a table in a dataset, why were you using the dataset in the first place? 2) To be honest, the ONLY reason I have ever found to use a DataSet is when I have multiple DataTables that I need to be related to each other somehow. Other than that, it's generally far easier to just create a DataTable and skip the DataSet.
-tg
-
Dec 4th, 2017, 09:07 AM
#8
Fanatic Member
Re: Filter a DataSet by criteria in specific column
try dataset.tables(0) to access the first table in the dataset...
and just to add onto what techgnome and si are saying....you may also look into binding sources which are great for filtering and creating views on a datatable, IF you need more filtering capability. otherwise rowfilter will do.
Yes!!!
Working from home is so much better than working in an office...
Nothing can beat the combined stress of getting your work done on time whilst
1. one toddler keeps pressing your AVR's power button
2. one baby keeps crying for milk
3. one child keeps running in and out of the house screaming and shouting
4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
5. working at 1 O'clock in the morning because nobody is awake at that time
6. being grossly underpaid for all your hard work
-
Dec 4th, 2017, 09:37 AM
#9
Thread Starter
Lively Member
Re: Filter a DataSet by criteria in specific column
@techgnome Honestly, I was just following a tutorial online. I did as you suggested and just skipped the DataSet.
@GBeats Thanks! I just skipped the DataSet.
So I am using the following code now. Col1 contains the numbers 1-4, and Col2 has a string.
dt.DefaultView.RowFilter = "Col1 = 1"
TextBox1.Text = Convert.ToString(DataTable(0)("Col2"))
The output on TextBox1 is "Cheque" and the value on Col1 for "Cheque" is indeed 1.
If I change that to dt.DefaultView.RowFilter = "Col1 = 2", however, the output is still "Cheque" when it should be "Tupperware", since we're filtering by 2 in Col1 now.
-
Dec 4th, 2017, 11:38 AM
#10
Re: Filter a DataSet by criteria in specific column
Show your complete code on how you're setting and changing it... there could be a couple reasons, I want to make sure we go down the right rabbit hole.
-tg
-
Dec 4th, 2017, 11:59 AM
#11
Thread Starter
Lively Member
Re: Filter a DataSet by criteria in specific column
@techgnome Sure! Sorry - I probably should have done that from the start.
Declarations
Code:
Dim Connection As New OleDb.OleDbConnection
Dim dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
Dim Database As String = "C:\Folder\Folder2\Database.mdb"
Dim dbSource As String = "Data Source = " & Database
Dim DataTable As New DataTable
Dim DataAdapter As OleDb.OleDbDataAdapter
Dim sql As String
We click Button1 to start loading the Database:
Code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
sql = "SELECT * FROM " & ComboBox1.Text
bwLoad.RunWorkerAsync()
End Sub
My BackgroundWorkers:
Code:
Private Sub bwLoad_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles bwLoad.DoWork
Try
Connection.ConnectionString = dbProvider & dbSource
Connection.Open()
DataAdapter = New OleDb.OleDbDataAdapter(sql, Connection)
DataAdapter.Fill(DataTable)
Connection.Close()
MaxRows = DataTable.Rows.Count
Catch
MessageBox.Show("Error!")
End Try
End Sub
Private Sub bwLoad_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles bwLoad.RunWorkerCompleted
Call DisplayInfo()
End Sub
And then we attempt to display the information:
Code:
Sub DisplayInfo()
DataTable.DefaultView.RowFilter = "Col1 = 1"
TextBox1.Text = Convert.ToString(DataTable(0)("Col2"))
End Sub
That last block of code is where I'm experiencing issues. When running this line of code (from the above block): TextBox1.Text = Convert.ToString(DataTable(0)("Col2"))
- If DataTable.DefaultView.RowFilter = "Col1 = 1" then
- Textbox1.text = "Cheque" - This is correct!
- If DataTable.DefaultView.RowFilter = "Col1 = 2" then
- Textbox1.text = "Cheque" - This is incorrect! Textbox1.text should equal "Tupperware".
The end goal here is to be able to pull information dependent on Col1, make changes if necessary to DataTable:
Code:
DataTable(0)("Col2") = TextBox1.Text
DataTable(1)("Col2") = TextBox2.Text
DataTable(2)("Col2") = TextBox3.Text
DataTable(3)("Col2") = TextBox4.Text
And then to update the database with any changes. Is there a smart way to do this? Like, update the database with only changes that have been made to the DataTable? Because at the moment, I am planning to do this:
Code:
Dim cb As New OleDb.OleDbCommandBuilder(DataAdapter)
DataTable(0)("Col2") = Convert.ToString(DataTable(0)("Col2"))
DataTable(1)("Col2")) = Convert.ToString(DataTable(1)("Col2"))
DataTable(2)("Col2")) = Convert.ToString(DataTable(2)("Col2"))
DataTable(3)("Col2")) = Convert.ToString(DataTable(3)("Col2"))
DataAdapter.Update(DataTable)
Because eventually, I plan to have about 100 textboxes, and updating each and every single one is going to be time consuming.
Last edited by lolikols; Dec 4th, 2017 at 02:37 PM.
-
Dec 4th, 2017, 03:13 PM
#12
Re: Filter a DataSet by criteria in specific column
I really feel like we should be taking the bindingsource rabbit hole. Seems to me you're doing too much work
I don't 100% know your requirement, and sorry if I am interrupting.
It may be worth a look to build off of something more like this.
Code:
Public Class BindingExample
Dim dset As New DataSet With {.DataSetName = "dset"}
Dim bs As New BindingSource
Private Sub BindingExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
dset.Tables.Add(New DataTable With {.TableName = "YourTable"})
'Use your connection to fill table
'I will fill this one manually instead
With dset.Tables("YourTable")
.Columns.Add("Col1", GetType(String))
.Columns.Add("Col2", GetType(String))
For i As Integer = 0 To 10
.Rows.Add("Col1Value" & i, "Col2Value" & i)
Next i
End With
bs.DataSource = dset.Tables("YourTable")
With ComboBox1
.DataSource = bs
.DisplayMember = "Col1"
.ValueMember = "Col1"
.DataBindings.Add("Text", bs, "Col1")
End With
With TextBox1
.DataBindings.Add("Text", bs, "Col2")
End With
End Sub
End Class
-
Dec 4th, 2017, 03:24 PM
#13
Thread Starter
Lively Member
Re: Filter a DataSet by criteria in specific column
@kpmc Thank you! I'll just clarify a little bit!
Basically, I have a database of miscellaneous household items with about 100 rows and 20 columns. Those household items are numerically categorized by numbers 1-4 in column 1. So for example:
1 Cheque etc etc etc etc etc
2 Tupperware etc etc etc etc etc
1 Plate etc etc etc etc etc
So on and so forth.
So I am trying to build an application that will load the database into an easily editable and readable manner in the way of textboxes. Because I have 100ish rows, and I don't want my application to be a nightmare to look at, I plan to split these into pages of 4 entries. So on page one, I will have the first 4 entries, on page 2, the next 4, so on and so forth.
But I would like to be able to filter all of these by their first column. So if I click on say, btnFilterNumber1, I'd like to be able to "ignore" any entries that don't have "1" in the first column when I display the information in the textboxes.
So for example, I load up my Database, I press btnFilterNumber1, and it only shows me entries that have the number "1" in the first four textboxes. I click on btnNext, and it shows me the next 4 entries that have the number "1", etc.
I hope to commit any changes to the DataTable and then by clicking a Save Button, apply those changes to the Database.
Last edited by lolikols; Dec 4th, 2017 at 03:34 PM.
-
Dec 4th, 2017, 03:30 PM
#14
Re: Filter a DataSet by criteria in specific column
I will wait to see what tg thinks. I don't want to send you down the wrong rabbit hole.
-
Dec 4th, 2017, 03:45 PM
#15
Re: Filter a DataSet by criteria in specific column
I just read your edit, why are you not using a datagridview?
-
Dec 4th, 2017, 03:48 PM
#16
Thread Starter
Lively Member
Re: Filter a DataSet by criteria in specific column
@kpmc Well, from my very limited experience with DataGridView, it's just a little clunky to edit and multiline entries (which I have several of) are a mess.
-
Dec 4th, 2017, 04:21 PM
#17
Re: Filter a DataSet by criteria in specific column
So don't use the grid for the edits....
Display the grid, when the user then selects a row, populate textboxes with the data... give them a save button. when they click it, push the data back into the datatable.
Orrrrr...... bind everything. I was originally thinking you had just a row that you were displaying in the textboxes... not 4 at a time.... use the grid. set the .Datasourse to the .DefaultView... and use .RowFilter...
Strike that... use a BindingSource, like kpmc showed... then filter the BindingSource itself... that will then cause the grid to refresh with the rows, and since the textboxes (for the ONE row) is also bound to that same BindingSource, it will show the current row.
-tg
-
Dec 4th, 2017, 04:56 PM
#18
Re: Filter a DataSet by criteria in specific column
My opinion is to scratch everything... sorry. Maybe start a new solution for some practicing...
Start from your (1) ONE datatable, and after you've filled it.
Place a combobox,textbox, and a datagridview
Follow the comments and devise your own logic.
What you will end up with is an understanding of how binding saved the world... er... well, saved your arse anyway...
Code:
Public Class BindingExample
'Create you dataset/binding where it's available to the entire class
Dim dset As New DataSet With {.DataSetName = "dset"}
Dim bs As New BindingSource
Private Sub BindingExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'Add a table to your dataset and name it during initialization
dset.Tables.Add(New DataTable With {.TableName = "YourTable"})
'Use your connection to fill table
'I will fill this one manually instead
With dset.Tables("YourTable")
'create columns
.Columns.Add("Col1", GetType(String))
.Columns.Add("Col2", GetType(String))
.Columns.Add("Col3", GetType(String))
'create 5 rows
For i As Integer = 0 To 5
.Rows.Add("Col1Value" & i, "Col2Value" & i, "Col3_LOOOOOOOOOONG_Value" & i)
Next i
'Duplicate the rows for DISTINCT test
'This datatable will have 10 rows total
For i As Integer = 0 To 5
.Rows.Add("Col1Value" & i, "Col2Value" & i, "Col3_LOOOOOOOOOONG_Value" & i)
Next i
End With
'set the datasource of the bindingsource
bs.DataSource = dset.Tables("YourTable")
'set the datasource of the Datagridview
DataGridView1.DataSource = bs
'Hide all of the columns in the Datagridview (Optional)
For Each DGVCol As DataGridViewColumn In DataGridView1.Columns
DGVCol.Visible = False
Next
'Show only Columns you prefer (You could also limit your cols by SQL query)
With DataGridView1
.Columns("Col1").Visible = True
End With
'Get distinct catagory values from your dataset.
'Make a copy as so not to mess with original set
'This view will have 5 distinct (unique) "rows"
Dim CatagoryDV As DataView = dset.Tables("YourTable").Copy.DefaultView.ToTable(True, "Col1").DefaultView
'Set the combobox to hold all distinct values from catagory
With ComboBox1
.DataSource = CatagoryDV
.DisplayMember = "Col1"
.ValueMember = "Col1"
End With
'Create some handles to handle the combobox index change event
AddHandler ComboBox1.SelectedIndexChanged, AddressOf ComboChanged
'This is a multiline textbox
'bind it to your column3
With TextBox1
.DataBindings.Add("Text", bs, "Col3")
End With
End Sub
'Create a sub to run when combobox index changed
Private Sub ComboChanged(Sender As Object, e As EventArgs)
Dim CmbBox As ComboBox = CType(Sender, ComboBox)
Dim FilterVal As String = CmbBox.Text
If FilterVal IsNot Nothing Then
dset.Tables("YourTable").DefaultView.RowFilter = "Col1 = '" & FilterVal & "'"
End If
End Sub
End Class
-
Dec 5th, 2017, 11:26 AM
#19
Thread Starter
Lively Member
Re: Filter a DataSet by criteria in specific column
Thank you very much to you both!
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
|