Filter and Sort a Table in VB 2005 Express

Orginal post by Konker at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2401261&SiteID=1

The Question :

I am using Visual Basic 2005 Express and wish to create a subset from a SQL database table, and then sort on a column of the subset. I can get the table into visual Basic but cannot then figure out how to create and then sort a subset.

The accepted answer : (by Riquel Dong)

Hi Knoker,



Based on your post, my understanding of your question is that you fill the data into a datatable from the database and need to filter and data. You can use filter property and sort property of the BindingSource class to handle this problem.



The BindingSource Filter property is used to filter the in-memory bound data source so that only rows that meet the criteria in the Filter string are made "visible" to the binding mechanism. The BindingSource Sort property is implemented like the ADO.NET DataView Sort property. This means you can name any column and indicate "ASC" (the default) for an ascending sort or "DESC" for a descending sort. For more information about how to use the bindingsource class, visit: http://www.databaseportal.org/Visual-Studio-and-SQL-Server/Using-the-Binding-Source-Class.html. Here is the simple code snippet to use this class. I use the Access database here.


Code BlockImports System.Data.OleDb

Public Class Form1

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb")

Dim sql As OleDbCommand = New OleDbCommand("SELECT * FROM Shippers", con)

Dim ds As DataSet = New DataSet()

Dim DataAdapter1 As OleDbDataAdapter = New OleDbDataAdapter()

Dim bs As New BindingSource

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

bs.Filter = "ShipperID>'15'"

bs.Sort = "CompanyName DESC"

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

con.Open()

DataAdapter1.SelectCommand = sql

DataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey

DataAdapter1.Fill(ds, "Shippers")

bs.DataSource = ds.Tables("shippers")

DataGridView1.DataSource = bs

con.Close()

End Sub

End Class






Best regards,

Riquel