DataView DataTable sorting and filtering in VB and .NET

This tutorial will show how we can use the GridView control to display data from a database, and also how we can filter the data as well as sort it. VB version.

Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!

Looking for the C#.NET 2005 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!

For this tutorial, we will be using a sample database to display the data in three different GridViews. The First will be the original data, then we will use DataTables and DataViews to filter and sort the data into two more GridViews. These will be displayed on button clicks. First we need the Connection String:

</appSettings>

We then implement our GridView controls, as well as the buttons and a TextBox for the user to choose what to filter by:

<strong>DataView and DataTable<br />
</strong >
<br />
<strong ><em>Original Data<br /></em >
<asp:GridView ID ="gvResults" runat ="server" AutoGenerateColumns ="False" BackColor ="White" BorderColor ="#CCCCCC" BorderStyle ="None" BorderWidth ="1px" CellPadding ="3">

<asp:BoundField DataField ="stor_id" HeaderText ="Store ID" />
<asp:BoundField DataField ="stor_address" HeaderText ="Address" />
<asp:BoundField DataField ="city" HeaderText ="City" />
<asp:BoundField DataField ="state" HeaderText ="State" />

</Columns>

<RowStyle ForeColor ="#000066" />
<SelectedRowStyle BackColor ="#669999" Font-Bold ="True" ForeColor ="White" />
<PagerStyle BackColor ="White" ForeColor ="#000066" HorizontalAlign ="Left" />
<HeaderStyle BackColor ="#006699" Font-Bold ="True" ForeColor ="White" />

</asp:GridView>

<br />Filtering Data Using DataView<br />
Enter State to Filter:
<asp:TextBox ID="txtFilter" runat="server" Columns="3" MaxLength="3"></asp:TextBox><br />

<asp:Button ID ="btnFiltering" runat ="server" OnClick ="btnFiltering_Click" Text ="Filtering" Width ="103px" /><br />
<asp:GridView ID ="gvFilter" runat ="server" AutoGenerateColumns ="False" BackColor ="White" BorderColor ="#999999" BorderStyle ="Solid" BorderWidth ="1px" CellPadding ="3" ForeColor ="Black" GridLines ="Vertical">

</Columns>

<SelectedRowStyle BackColor ="#000099" Font-Bold ="True" ForeColor ="White" />
<PagerStyle BackColor ="#999999" ForeColor ="Black" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Black" Font-Bold ="True" ForeColor ="White" />
<AlternatingRowStyle BackColor ="#CCCCCC" />

</asp:GridView>

<br />Sorting Data Using DataView<br />
Sort by City:<br />
<asp:Button ID ="btnSorting" runat ="server" OnClick ="btnSorting_Click" Text ="Sorting" /><br />
<asp:GridView ID ="gvSort" runat ="server" AutoGenerateColumns ="False" BackColor ="White" BorderColor ="#336666" BorderStyle ="Double" BorderWidth ="3px" CellPadding ="4" GridLines ="Horizontal">

</Columns>

<RowStyle BackColor ="White" ForeColor ="#333333" />
<SelectedRowStyle BackColor ="#339966" Font-Bold ="True" ForeColor ="White" />
<PagerStyle BackColor ="#336666" ForeColor ="White" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="#336666" Font-Bold ="True" ForeColor ="White" />

</asp:GridView>
</strong>

</div>
</form>

The code-behind will look something like this:

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If (Not IsPostBack) Then

Dim sqlQuery As String = "SELECT * from Table1"
Dim conn As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
Dim sda As New SqlDataAdapter(sqlQuery, conn)
Dim dtData As New DataTable()
sda.Fill(dtData)

gvResults.DataSource = dtData.DefaultView
gvResults.DataBind()

End If

End Sub

Protected Sub btnFiltering_Click(ByVal sender As Object, ByVal e As EventArgs)

Dim sqlQuery As String = "SELECT * from Table1"
Dim conn As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
Dim sda As New SqlDataAdapter(sqlQuery, conn)
Dim dtData As New DataTable()
sda.Fill(dtData)

Dim dvData As New DataView(dtData)
dvData.RowFilter = "state = '" & txtFilter.Text.ToUpper() & "'"

gvFilter.DataSource = dvData
gvFilter.DataBind()

End Sub

Protected Sub btnSorting_Click(ByVal sender As Object, ByVal e As EventArgs)

Dim sqlQuery As String = "SELECT * from Table1"
Dim conn As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
Dim sda As New SqlDataAdapter(sqlQuery, conn)
Dim dtData As New DataTable()
sda.Fill(dtData)

Dim dvData As New DataView(dtData)
dvData.Sort = "city"

gvSort.DataSource = dvData
gvSort.DataBind()

End Sub

End Class

Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!

Looking for the C#.NET 2005 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!