none
Visual Studio Datagridview - Finding all rows based on multiple criteria

    Question

  • I am working on an application that imports a database of logistics companies from file Blacklist.accdb (I am very new to Visual Studio and am still learning vb.net)

    The columns are:
    MCN / DOT || Carrier Name || Primary Contact || Secondary Contact || Primary Phone || Secondary Phone || Fax Number

    The goal is for the user to be able to insert strings into the application's TextBoxes 1-7, and click "Find" to see if the carrier they are using is related to any of the carriers in the database in anyway.

    EX: Phone numbers from the user's carrier matches two phone numbers in the Datagridview. Now we know that the user's carrier is potentially affiliated with the carriers found.

    I am currently using a "Find" button which filters backwards (Last row first) and removes columns in which none of the cells have TextBox1.Text or TextBox2.Text or TextBox3.Text etc. - Which works, just incredibly slowly when dealing with hundreds of rows of data.

    Is there a quicker way to do this?

    Wednesday, September 6, 2017 7:45 PM

All replies

  • Have you heard of SQL?


    Sam Hobbs
    SimpleSamples.Info

    Wednesday, September 6, 2017 7:53 PM
  • Not outside of Visual Studio, no.
    Wednesday, September 6, 2017 7:57 PM
  • Is there a quicker way to do this?

    Depending on the complexity of things, maybe not, but the DGV is (or should be) just a display of the data -- not the data itself.

    Go back to the source of the data to run the query.


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Wednesday, September 6, 2017 8:19 PM
  • I have never heard of SQL being inside of Visual Studio but I don't know what "inside" and "outside" means in that contest.

    Note that this question is in the VB forum so we don't know details of what you are using but I assume Windows Forms. You say "imports a database" but otherwise we don't know how the data is being accessed.

    Experienced programmers bind a collection to the DataGridView and work with the collection.  This is what Frank is also saying. You can then use LINQ on the collection. The collection could actually be a database table or the result of a SQL query and you could run a different query to retrieve the data using modified criteria.

    There are various ways to access databases but the most common is Entity Frameworks. It takes time to learn EF but once you have learned it EF will save time.



    Sam Hobbs
    SimpleSamples.Info


    Wednesday, September 6, 2017 8:37 PM
  • My suggestion would be to load the database into a DataSet or DataTable, bind the DataSet/Table to a BindingSource, bind the BindingSource to the DataGridView, and then filter the BindingSource which in turn would be visually represented in your DataGridView. It sounds like a lot, but it isn't. Take a look at this example:

    Private bs As BindingSource
    
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        'Declare the connection object
        Dim con As OleDbConnection
    
        'Create a DataTable to store the data
        Dim dt As DataTable = New DataTable()
    
        'Wrap code in Try/Catch
        Try
            'Set the connection object to a new instance
            'TODO: Change "My Connection String Here" with a valid connection string
            con = New OleDbConnection("My Connection String Here")
    
            'Create a new instance of the command object
            'TODO: Change [MyTable] to a valid table name
            Using cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [MyTable]", con)
    
                'Open the connection
                con.Open()
    
                'Create a new DataAdapter from the OleDbCommand
                Using adapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                    'Fill the DataTable from the DataAdapter
                    adapter.Fill(dt)
                End Using
    
                'Close the connection
                con.Close()
            End Using
    
            'Create a new instance of the BindingSource and setup the data bindings
            bs = New BindingSource()
            bs.DataSource = dt
    
            'Bind the BindingSource to the DataGridView
            DataGridView1.DataSource = bs
        Catch ex As Exception
            'Display the error
            Console.WriteLine(ex.Message)
        Finally
            'Check if the connection object was initialized
            If con IsNot Nothing Then
                If con.State = ConnectionState.Open Then
                    'Close the connection if it was left open(exception thrown)
                    con.Close()
                End If
    
                'Dispose of the connection object
                con.Dispose()
            End If
        End Try
    End Sub
    
    Private Sub btnFind_Click(ByVal sender As Object, ByVal e As EventArgs) handles btnFind.Click
        'Setup the BindingSource's filter
        bs.Filter = $("Column1 = {TextBox1.Text} AND Column2 = {TextBox2.Text} AND  Column3 = {TextBox3.Text} AND Column4 = {TextBox4.Text} AND Column5 = {TextBox5.Text} AND Column6 = {TextBox6.Text} AND Column7 = {TextBox7.Text}")
    End Sub

    This assumes that you have two controls named DataGridView1 and btnFind.

    Wednesday, September 6, 2017 9:01 PM
  • Hi Grimes869,

    You can use linq method to query some filtered data and display in the datagridview, like this:

     Dim db As New DataClasses1DataContext
            Dim table = db.Test14s.Where(Function(x) x.Carrier_Name_ = TextBox1.Text.ToString() OrElse x.Primary_Phone_ = TextBox2.Text.ToString() OrElse x.Secondary_Phone = TextBox3.Text.ToString())
                    DataGridView1.DataSource = table.ToList()

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 7, 2017 3:50 AM
    Moderator
  • Grimes,

    Show how you retrieve the data, than somebody can tell you the rest. 

    There are many ways inside VB to use SQL code which is used for an Access database.


    Success
    Cor

    Thursday, September 7, 2017 7:05 AM
  • You can accomplish this with a dataview filter as well.

    IE

    Imports System.Data.SqlClient
    
    Public Class Form_DV
        Dim DV As New DataView
        Dim DT As New DataTable
        Dim FilterString As String = Nothing
        Private Sub Form_DV_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'Get the data into a datatable'
            Using Conn As New SqlConnection("Data Source=SQLTEST2\STD;Initial Catalog=KPMIT;Integrated Security=True")
                Using DA As New SqlDataAdapter("SELECT * FROM Positions", Conn)
                    DA.Fill(DT)
                    DV = DT.DefaultView
                    DGV.DataSource = DV
                End Using
            End Using
    
            'Add handlers to the search textboxs'
            'Here all textbox are in a group control'
            For Each ctrl As TextBox In GroupBox_TBSearch.Controls
                AddHandler ctrl.KeyUp, AddressOf FilterData
            Next
        End Sub
    
        Private Sub FilterData(sender As Object, e As KeyEventArgs)
            Dim TB As TextBox = CType(sender, TextBox)
    
            'build filter string (this will filter on all columns in data table)'
            If TB.Text.Length > 0 Then
                For Each col As DataColumn In DT.Columns
                    If col.DataType = GetType(System.String) Then
                        FilterString &= col.ColumnName & " LIKE '%" & TB.Text & "%' or "
                    End If
                Next
                'fix the filter string (remove trailing ' or ')'
                FilterString = FilterString.Substring(0, FilterString.Length - 4)
    
            End If
    
            'apply the filter'
            DV.RowFilter = FilterString
    
            FilterString = Nothing
        End Sub
    
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, September 7, 2017 12:43 PM
  • There is a component that might be a viable option. Filtering is done via right clicking on a column header e.g.

    The above the DataGridView was loaded from MS-Access into a DataTable which becomes the DataSource of a BindingSource and the BindingSource component becomes the data source of the DataGridView. One line of code.

    In the code below, Customers class loads data into a public property of the class DataTable. FilterManager is the filter component.

    Dim TheCustomers As New Customers
    
    bsCustomers.DataSource = TheCustomers.DataTable
    OriginalRowCount = TheCustomers.RowCount
    DataGridView1.DataSource = bsCustomers
    'Initialize filter component
    FilterManager = New DgvFilterManager(DataGridView1)

    Customization is supported, e.g. load a list of viable options for a column.

     Code

    Dim TheCustomers As New Customers
    
    bsCustomers.DataSource = TheCustomers.DataTable
    OriginalRowCount = TheCustomers.RowCount
    DataGridView1.DataSource = bsCustomers
    'Initialize filter component
    FilterManager = New DgvFilterManager(DataGridView1)
    
    
    ' Setup ContactTitle filter as a distinct list
    FilterManager("ContactTitle") = New DgvComboBoxColumnFilter
    DirectCast(FilterManager("ContactTitle"), DgvComboBoxColumnFilter).ComboBoxValue.DropDownStyle = ComboBoxStyle.DropDownList

    We can also work together with conventional methods e.g.

    bsCustomers.DataSource = New DataView(CType(bsCustomers.DataSource, DataTable),
        <Filter>SerialNumber LIKE '{<%= TextBox1.Text %>}%'</Filter>.Value, Nothing, DataViewRowState.CurrentRows)

    Link to source code on Code Project.

    https://www.codeproject.com/Articles/33786/DataGridView-Filter-Popup

    I've had this implemented in a project from back in 2007 with hundreds of thousands of records with great success.

    Here is a code sample I did a while back with MS-Access that only begins to touch the surface of this component. I did a few language extensions for the component also

    https://onedrive.live.com/redir?resid=A3D5A9A9A28080D1!319&authkey=!AJPs7VLQIfK8BFE&ithint=file%2czip


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, September 7, 2017 2:04 PM
    Moderator