Have you seen those nifty tricks where a data grid view or listbox is filtered as you type in a text box? Believe it or not its really really easy using both Visual Basic and C#. I thought it was hard too… that i have to run through nested for loops… but Visual Studio it self has done the hardest part and made it easy for us!
Difficulty : Easy
Time required : Less than 5 minutes
Prerequisites : Connecting to a database, Filling in a DataSet/DataTable
Ok lets get it started, lets first do it in Visual Basic
All you need is a data grid view and a text box! Its simple as that! Now lets load the data! Make sure you have created an SQL Server database, and filled it with data!
OK lets fill the data grid view with the data!
Imports System.Data.SqlClient
Public Class Form1
Dim ds As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=true; Initial Catalog=Bodgett")
con.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Employee", con)
da.Fill(ds, "Emp")
DataGridView1.DataSource = ds.Tables("Emp")
End Sub
End Class
Ok…now run the project and you will be (you should! ) see the data grid view filled with the data! Like this…
Assuming that everyone went as planned, lets do the actual filtering!
Before actually coding, here is a heads up on the syntax used for the filtering.
Lets say we want to find the employee with the first name ‘Lilak’
Then the row filter would be Emp_FName = 'Lilak'
Or lets say that we want to find some one with the email address equals to ‘emiley@bodgett.com’
then the row filter would be Emp_Email = 'emiley@bodgett.com'
But how do we search for both at the same time, simple combine them with a “and”
eg: Emp_FName = ‘Lilak’ AND Emp_Email = 'lilak@bodgett.com'
But as you can see, there is no use of filtering without the use of WILD CARDS! (I love ‘em!)
The wild cards that can be used in a Data Grid View are % and *, which does the same job!
Now lets say we want to filter records based on the email provider.
Its going to be like Emp_Email LIKE '%yahoo%'
the % sign represents 0 or more characters.
So now hoping that you guys know how to write the filtering syntax, lets get wet with the actual coding… well, there is no CODING that you to do, as it is something that could be done with…yes my friends, 1 LINE OF CODE :)
ds.Tables("Emp").DefaultView.RowFilterds is our DataSet, but we are actually applying the coding to a DataTable’s (which is “Emp”) DataView. We are changing the RowFilter property of the DataView.
ds.Tables("Emp").DefaultView.RowFilter = "Emp_FName LIKE '*" & TextBox1.Text & "*'"
This changes the RowFilter while text is being typed on to the textbox, which dynamically filters the records according to the text. Notice that i have used * but remember % can be used too. For an example if the user enters the word Jason to the textbox, the rowFilter would look like
Emp_FName LIKE '*Jason*'
including the quotes ' '
Now run the program and see how it is…
So how do you filter based on multiple attributes. Easy! Just separate the multiple attributes with a OR
So if we want to filter using both First name and Last name we would use something like
ds.Tables("Emp").DefaultView.RowFilter = "Emp_FName LIKE '*" & TextBox1.Text & "*' OR Emp_Lname LIKE '*" & TextBox1.Text & "*'"
For the input “Jason” for the textbox this would output something like
Emp_FName LIKE '*Jason*' OR Emp_LName LIKE '*Jason*'
So this is it folks, the coding is over and so is the tutorial :)
For more information on rowFilter and rowFilter expression read these articles on MSDN
http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
Here is the complete coding for whoever wants it :D
Imports System.Data.SqlClient
Public Class Form1
Dim ds As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=true; Initial Catalog=yourDBName")
con.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Employee", con)
da.Fill(ds, "Emp")
DataGridView1.DataSource = ds.Tables("Emp")
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
ds.Tables("Emp").DefaultView.RowFilter = "Emp_FName LIKE '*" & TextBox1.Text & "*' OR Emp_Lname LIKE '*" & TextBox1.Text & "*'"
End Sub
End Class
Blogger Labels:
Dynamic,
DataGridView,
ListBox,
tricks,
data,
grid,
text,
Visual,
Basic,
Studio,
self,
Easy,
Time,
Prerequisites,
database,
DataSet,
DataTable,
Server,
System,
SqlClient,
Public,
Class,
Private,
sender,
Object,
EventArgs,
MyBase,
Load,
SqlConnection,
Source,
Initial,
Catalog,
Bodgett,
Open,
SqlDataAdapter,
SELECT,
FROM,
Employee,
Fill,
DataSource,
syntax,
WILD,
CARDS,
View,
records,
provider,
characters,
DefaultView,
RowFilter,
DataView
