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
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
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
Re: Searching in Datagridview using textbox
Quote:
Originally Posted by
kpmc
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.
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:
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
'Start or restart the filter timer.
Timer1.Stop()
Timer1.Start()
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Timer1.Stop()
'Perform the filtering.
BindingSource1.Filter = $"SomeColumn LIKE '%{TextBox1.Text}%'"
End Sub
Re: Searching in Datagridview using textbox
Quote:
Originally Posted by
jmcilhinney
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:
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
'Start or restart the filter timer.
Timer1.Stop()
Timer1.Start()
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Timer1.Stop()
'Perform the filtering.
BindingSource1.Filter = $"SomeColumn LIKE '%{TextBox1.Text}%'"
End Sub
I like that Idea
regards
Chris
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 $
Re: Searching in Datagridview using textbox
Quote:
Originally Posted by
kpmc
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.
Re: Searching in Datagridview using textbox
The procedure is not pulling from a database, it is filtering a dataview.
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.
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
