There are many ways to filter data in a grid, here one of them...
All you need is to create a DataSet connected with a table and a little code!
(in the example I use "AdventureWorks" as database and "Person.CountryRegion" as table)
Let's start!
As usual, we have to write an object to connect a GridView with a source like a dataset/database.
Public Function GetCountryRegion(Optional ByVal CountryRegionCode As String = "%", _
Optional ByVal Name As String = "%") As ds_CountryRegion.CountryRegionDataTable
Dim cnn As SqlConnection = Nothing
Dim cmd As SqlCommand = Nothing
Dim da As SqlDataAdapter = Nothing
Dim sqlStatement As String
Try
sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"
cnn = New SqlConnection(Me._connectionString)
cmd = New SqlCommand(sqlStatement, cnn)
da = New SqlDataAdapter(cmd)
cnn.Open()
_ds.CountryRegion.Rows.Clear()
da.Fill(_ds, "CountryRegion")
Catch ex As Exception
'todo: code for exceptions!
Finally
GetCountryRegion = _ds.CountryRegion
If Not (cnn Is Nothing) Then
cnn.Close()
cnn.Dispose()
cnn = Nothing
End If
If Not (da Is Nothing) Then
da.Dispose()
da = Nothing
End If
If Not (cmd Is Nothing) Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Function
As you can see, I use the keyword "
Optional" for the parameters and I use "
%" as default value.
In T-Sql language "%" stands for "all". If there are no parameters passed, the code reads "all" CountryRegionCode records and "all" Name records.
Another interesting thing is the use of the keyword "
like" and the use of "%"
at the end of the parameters in the sqlStatement string ("like 'Name%'" and "like 'CountryRegionCode%'").
In this example I can catch all records with the Name that
starts with the parameter passed: all records with "Name%" starts with "I%" (Italy, Ireland, Iceland...) (ok I know, it's not easy for me to explain in eng... I'm sorry!)
sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"
When our object is ready and connected with a GridView through the "
DataSource" property, we have to use the "
Databind" property to associate the data with the GridView.
In the
Page_Load event, there are no parameters passed to the DataTable object (in fact the two TextBox values are empty ---> "")
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
GetGwCountryRegion()
End If
End Sub
But each time that we write something in the TextBox Name or CountryRegionCode and click on "
Filter", the DataTable recives parameters and use them for filtering.
That's all!
You can download this my little example
here.
You can download and install AdventureWorksDB.msi database
here.
Please remember to change in the web.config file, the two values "Data Source=
pc01;" with
your sqlserver instance name. (probably "
computername" with Sql2005/2008, but if you are using SqlExpress edition your instance name is "
computername\SQLExpress")
Enjoy!
