How to filter data with the ADO.NET DataView in VB.NET

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()