Results 1 to 2 of 2

Thread: Simplify or filter data

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2019
    Posts
    6

    Lightbulb 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!

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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:
    1. Dim table As New DataTable
    2.  
    3. Using adapter As New SqlDataAdapter("SELECT * FROM Person", "connection string here")
    4.     adapter.Fill(table)
    5. End Using
    6.  
    7. Dim activeView As New DataView(table)
    8. Dim inactiveView As New DataView(table)
    9.  
    10. activeBindingSource.Filter = "IsActive = True"
    11. activeBindingSource.DataSource = activeView
    12. activeDataGridView.DataSource = activeBindingSource
    13.  
    14. inactiveBindingSource.Filter = "IsActive = False"
    15. inactiveBindingSource.DataSource = inactiveView
    16. 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
  •  



Click Here to Expand Forum to Full Width