dcsimg
Results 1 to 19 of 19

Thread: [RESOLVED] Filter a DataSet by criteria in specific column

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    Resolved [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?

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,945

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    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.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,341

    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)

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,945

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    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 .

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,945

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    911

    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


  9. #9

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    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.

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,945

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    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.

  12. #12
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    680

    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

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    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.

  14. #14
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    680

    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.

  15. #15
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    680

    Re: Filter a DataSet by criteria in specific column

    I just read your edit, why are you not using a datagridview?

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    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.

  17. #17
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,945

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    680

    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

  19. #19

    Thread Starter
    Lively Member
    Join Date
    Oct 2016
    Posts
    84

    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
  •  



Featured


Click Here to Expand Forum to Full Width