If in your project you want to include only certain rows in the display you can use a DataView to apply custom filtering. To accomplish this, you should use the RowFilter property which acts like a WHERE clause in a SQL query. You can limit the final results by using it and by applying logical operators, described in the next table:
|
Operator |
Description |
|
<, >, <=, and >= |
Performs comparison of more than one value. These comparisons can be numeric (with number data types) or alphabetic dictionary comparisons (with string data types). |
|
<> and = |
Performs equality testing. |
|
NOT |
Reverses an expression. Can be used in conjunction with any other clause. |
|
BETWEEN |
Specifies an inclusive range. For example, “Units BETWEEN 5 AND 15” selects rows that have a value in the Units column from 5 to 15. |
|
IS NULL |
Tests the column for a null value. |
|
IN(a,b,c) |
A short form for using an OR clause with the same field. Tests for equality between a column and the specified values (a, b, and c). |
|
LIKE |
Performs pattern matching with string data types. |
|
+ |
Adds two numeric values or concatenates a string. |
|
– |
Subtracts one numeric value from another. |
|
* |
Multiplies two numeric values. |
|
/ |
Divides one numeric value by another. |
|
% |
Finds the modulus (the remainder after one number is divided by another). |
|
AND |
Combines more than one clause. Records must match all criteria to be displayed. |
|
OR |
Combines more than one clause. Records must match at least one of the filter expressions to be displayed. |
The next example page includes three GridView controls. Each one is bound to the same DataTable but with different filter settings:
‘ Step 1. Retrieve the data into a DataSet
‘ Create the Connection, DataAdapter, and DataSet.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings(“SomeCompany”).ConnectionString
Dim con As New SqlConnection(connectionString)
Dim sql As String = “SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder, Discontinued FROM Products”
Dim da As New SqlDataAdapter(sql, con)
Dim ds As New DataSet()
da.Fill(ds,”Products)
‘ Step 2. Filter each DataView for different criterion
‘ Filter for the Computer product
Dim view1 As New DataView(ds.Tables(“Products”))
view1.RowFilter = “ProductName = ‘Computer'”
grid1.DataSource = view1
‘ Filter for products that aren’t on order or in stock.
Dim view2 As New DataView(ds.Tables(“Products”))
view2.RowFilter = “UnitsInStock = 0 AND UnitsOnOrder = 0”
grid2.DataSource = view2
‘ Filter for products starting with the letter K.
Dim view3 As New DataView(ds.Tables(“Products”))
view3.RowFilter = “ProductName LIKE ‘K%'”
grid3.DataSource = view3
‘Step 3. Copy the values form the DataTable into control separately or for entire page by calling Page.DataBind().
Page.DataBind()