Results 1 to 11 of 11

Thread: Searching in Datagridview using textbox

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2017
    Posts
    22

    Exclamation Searching in Datagridview using textbox

    Good day I am a beginner at vb.net programming and I'm having a problem about searching or filtering the datagridview using textbox. I have a one textbox for searching and a two button for retrieving values which is the student and vehicle. When the student button clicked the data for student is displayed and same for vehicle but when I move the "Dim dtaset As New DataTable" outside the events of students,vehicle and searchtxt the search is successfully filtering the data of the datavalues also theres a problem too after moving, when click the button it adds the data for that button in datagridview but when the other button is click after the first button the data not replaced the previous one, it will just add the columns that will extend the datagrid columns. Here is my code Thank you in advance

    Code:
    Private Sub searchtxt_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchtxt.TextChanged
        con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
    
    
        Dim dtaset As New DataTable
    
        Dim DV As New DataView(dtaset)
        DV.RowFilter = String.Format("FirstName Like '%{0}%'", searchtxt.Text)
        DataGridView1.DataSource = DV
    End Sub
    
    Public Sub studattenprint()
        con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
    
        Dim adpter As New MySqlDataAdapter
        Dim dtaset As New DataTable
        Dim bsource As New BindingSource
    
    
        Try
            con.Open()
            Dim query As String
            query = "select idno as 'Student_ID',lastxt as 'LastName',firstxt as 'FirstName',middletxt as 'MiddleName',log as 'Status',timein as 'Timein',crse as 'Course',dates as 'Date' from dat.studdailyhistory"
            cmd = New MySqlCommand(query, con)
    
            adpter.SelectCommand = cmd
            adpter.Fill(dtaset)
            If dtaset.Rows.Count >= 0 Then
                numlog.Text = dtaset.Rows.Count.ToString()
            End If
            bsource.DataSource = dtaset
            DataGridView1.DataSource = bsource
    
            ''DataGridView Design
            DataGridView1.AllowUserToAddRows = False ' Disabled or hide (*) Symbol...
    
            DataGridView1.RowHeadersVisible = False 'To hide Left indicator..
            DataGridView1.DefaultCellStyle.SelectionBackColor = Color.SteelBlue  'Selection backcolor....
            DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGoldenrodYellow 'Alternating Backcolor.
            DataGridView1.AllowUserToResizeRows = False 'Disabled  row resize...
            DataGridView1.ReadOnly = True
            DataGridView1.MultiSelect = False
            DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridView1.ShowRowErrors = False
            DataGridView1.ShowCellErrors = False
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            DataGridView1.Sort(DataGridView1.Columns(7), System.ComponentModel.ListSortDirection.Descending)
    
            adpter.Update(dtaset)
            con.Close()
    
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
        End Try
    End Sub
    
    Public Sub vehicattenprint()
        con = New MySqlConnection
        con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
        Dim SDA As New MySqlDataAdapter
        Dim dtaset As New DataTable
        Dim bSource As New BindingSource
    
    
        Try
    
            con.Open()
            Dim query As String
    
            query = "select tag_no as 'Tag_No.',platenum as 'Plate no.',ownername as 'Owner_name',log as 'Status',timelog as 'Time_log',dates as 'Date' from dat.vehicdailyhistory"
            cmd = New MySqlCommand(query, con)
            SDA.SelectCommand = cmd
            SDA.Fill(dtaset)
            bSource.DataSource = dtaset
            DataGridView1.DataSource = bSource
    
            If dtaset.Rows.Count > 0 Then
                numlog.Text = dtaset.Rows.Count.ToString()
            End If
    
            SDA.Update(dtaset)
            DataGridView1.AllowUserToAddRows = False ' Disabled or hide (*) Symbol...
    
            DataGridView1.RowHeadersVisible = False 'To hide Left indicator..
            DataGridView1.DefaultCellStyle.SelectionBackColor = Color.SteelBlue  'Selection backcolor....
            DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGoldenrodYellow 'Alternating Backcolor.
            DataGridView1.AllowUserToResizeRows = False 'Disabled  row resize...
            DataGridView1.ReadOnly = True
            DataGridView1.MultiSelect = False
            DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridView1.ShowRowErrors = False
            DataGridView1.ShowCellErrors = False
            DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            DataGridView1.Sort(DataGridView1.Columns(5), System.ComponentModel.ListSortDirection.Descending)
    
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
        End Try
    End Sub
    
    Private Sub vehicatten_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles vehicatten.Click
        vehicattenprint()
    End Sub
    
    Private Sub studatten_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles studatten.Click
        studattenprint()
    End Sub

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Searching in Datagridview using textbox

    Hi,

    if you are using "BindingSource" then you should Filter the BindingSource

    Code:
    Private Sub searchtxt_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchtxt.TextChanged
        con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
    
     BindingSource1.Filter = String.Format("{0} LIKE '{1}%'", "Firstname", Textbox1.Text)
            BindingSource1.Sort = "Firstname ASC"
      
    End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Searching in Datagridview using textbox

    What I do in these situations is create a Dataview from the default view of the datatable then set the datasource of the bindingsource to the DV then bind the dgv to the bindingsource. At which point you can iterate through all of the columns and find any string value within any string type column in the table

    Code:
            Private Sub TB_VendorKeyUp(sender As Object, e As KeyEventArgs)
                Dim FilterString As String = Nothing
    
                Dim tb As TextBox = CType(sender, TextBox)
                If tb.Text.Length > 0 Then
                    For Each col As DataColumn In DSET.Tables("VendorLookup").Columns
                        If col.DataType = GetType(System.String) Then
                            FilterString &= col.ColumnName & " LIKE '%" & tb.Text & "%' or "
                        End If
                    Next
    
                    FilterString = FilterString.Substring(0, FilterString.Length - 4)
                    VendorLookupDV.RowFilter = FilterString
                    FilterString = Nothing
                End If
            End Sub

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

    Re: Searching in Datagridview using textbox

    Quote Originally Posted by kpmc View Post
    What I do in these situations is create a Dataview from the default view of the datatable then set the datasource of the bindingsource to the DV then bind the dgv to the bindingsource.
    You don't "create" a DataView from the DefaultView of the DataTable. The DefaultView of the DataTable IS a DataView. There's also no point binding the DefaultView to the BindingSource and then using the DefaultView. The point of the BindingSource is to be the focal point for bound data and to replace various other items, including the DefaultView.

    When you bind a DataTable, the data it exposes actually comes from the DefaultView anyway, so binding the DataTable or its DefaultView makes no difference to what the BindingSource sees. That's because complex data-binding actually works with an IList or an IListSource. A DataTable is a IListSource and its GetList method returns its DefaultView.

    If you're using a BindingSource then you would generally filter by setting its Filter property rather than the RowFilter of the DataView. Otherwise, you're wasting the BindingSource. Using the DataView directly isn't wrong but it is less right.

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

    Re: Searching in Datagridview using textbox

    On another note, I would suggest that it's a very bad idea to filter on TextChanged or any other event that occurs on every keystroke. Let's say that the user wants data that contains "abcde". That means that you're going to filter the data five times when all you need is one. Particularly if the amount of data is large, that may actually degrade the user experience. I always suggest using a Timer and filtering on the Tick event of that. You can then reset the Timer on TextChanged and then filtering will only take place when the user stops or pauses typing. You need to get the Interval right so that they don't have to wait too long or slow typists get too many filters but a bit of trial and error can help there.
    vb.net Code:
    1. Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    2.     'Start or restart the filter timer.
    3.     Timer1.Stop()
    4.     Timer1.Start()
    5. End Sub
    6.  
    7. Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    8.     Timer1.Stop()
    9.  
    10.     'Perform the filtering.
    11.     BindingSource1.Filter = $"SomeColumn LIKE '%{TextBox1.Text}%'"
    12. End Sub

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Searching in Datagridview using textbox

    Quote Originally Posted by jmcilhinney View Post
    On another note, I would suggest that it's a very bad idea to filter on TextChanged or any other event that occurs on every keystroke. Let's say that the user wants data that contains "abcde". That means that you're going to filter the data five times when all you need is one. Particularly if the amount of data is large, that may actually degrade the user experience. I always suggest using a Timer and filtering on the Tick event of that. You can then reset the Timer on TextChanged and then filtering will only take place when the user stops or pauses typing. You need to get the Interval right so that they don't have to wait too long or slow typists get too many filters but a bit of trial and error can help there.
    vb.net Code:
    1. Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    2.     'Start or restart the filter timer.
    3.     Timer1.Stop()
    4.     Timer1.Start()
    5. End Sub
    6.  
    7. Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    8.     Timer1.Stop()
    9.  
    10.     'Perform the filtering.
    11.     BindingSource1.Filter = $"SomeColumn LIKE '%{TextBox1.Text}%'"
    12. End Sub
    I like that Idea

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Searching in Datagridview using textbox

    Actually I use that Sub on very large datasets and it does not degrade the user experience. I also include procedures that color the DGV cells when they contain the string being filtered in any of the columns. This works just fine on tables with rows well over 194k. You are right where the dataview is actually the default view of the datatable and you could work directly with that instead of a dataview. At the end of the day it doesnt matter in the least.

    As for your time tick I find that approach too degrade the user experience whereas now your waiting on ticks... Your procedure run across 300 machines doing searches waiting on those ticks would actually start costing $

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Searching in Datagridview using textbox

    Quote Originally Posted by kpmc View Post
    As for your time tick I find that approach too degrade the user experience whereas now your waiting on ticks... Your procedure run across 300 machines doing searches waiting on those ticks would actually start costing $
    The idea of the timer approach is that you set the Interval to be fairly small (perhaps 0.3 seconds), and while that does delay the user slightly after the final character they type, it is a negligible amount that very few people would notice, and is extremely unlikely to make a detectable negative impact.

    It does however create a positive impact in performance compared to searching after each key press, because lots of searches (that are "immediately" discarded and replaced by another) are not being done at all. That means less work for the database (which does reduces costs in terms of electricity, and potentially also hardware etc), and that the user isn't forced to wait while searches they don't care about are run and processed.

    After having used both on multiple occasions (including while converting applications to to the timer approach), I am used to seeing a notable improvement in performance by switching to the timer... and only see the non-timer approach being better if the value of the timer interval is very wrong.
    Last edited by si_the_geek; Jan 16th, 2018 at 11:00 AM.

  9. #9
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Searching in Datagridview using textbox

    The procedure is not pulling from a database, it is filtering a dataview.

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Searching in Datagridview using textbox

    Indeed it is, it seems my mind drifted slightly at that point... but apart from one word, the rest of my post stands.

  11. #11
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: Searching in Datagridview using textbox

    I respect your position, and am unaware of your personal experience. My experience has been positive without the timer, there literally are no performance problems in the slightest and applying the timer would then replicate a performance problem where as you would be waiting unnecessarily no matter the interval.

    There are time i do include a timer in the sub, but that is to call an update to the table after X amount of ms after a keypress which resets after each keystroke so the update is not triggering with each keyup event

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