Lesson 35: Sorting and Filtering Records

	Sorting and Filtering Records

Sorting the Records

Sorting the Records in the Table Window

The lists of records we get with a SELECT statement are presented in the order they have in the table. SQL allows you to arrange records in alphabetical order, in chronological order or in numeric incremental order. After selecting a series of columns, you may want to list the records following an alphabetical order from one specific field. To get an alphabetical or an incremental order of records, you must let the database know what field would be used as reference.

To specify the order, if you are using a table window in Microsoft SQL Server Management Studio or in Microsoft Visual Studio:

In the Diagram section, you can right-click a field and select either Sort Ascending or Sort Descending
In the Criteria section of the window, under the Sort Type column, click the corresponding box of the desired column. This would reveal that it is a combo box. Then click the arrow of that combo box and make your selection between Ascending and Descending:

Using the Table Window

If you select Ascending or Sort Ascending, the list of records would be re-arranged based on the type of the selected column:

If the column is text-based (char, varchar, and their variants), the records would be arranged in alphabetical order
If the column is date or time-based (datetime or smalldatetime), the records would be arranged in chronological order
If the column is number-based, the records would be arranged in incremental order
If the column is Boolean-based (bit), the FALSE records would appear first

If you select Descending or Sort Descending, the list of records would be re-arranged based on the type of the selected column:

If the column is text-based (char, varchar, and their variants), the records would be arranged in reverse alphabetical order
If the column is date or time-based (datetime or smalldatetime), the records would be arranged in reverse chronological order
If the column is number-based, the records would be arranged in decremental order
If the column is Boolean-based (bit), the TRUE records would appear first

After selecting the desired Sort Type, you can execute the SQL statement.

Practical Learning: Introducing Data Sorting

Start Microsoft Visual Basic and create a new Windows Application named AltairRealtors4
In the Solution Explorer, right-click Form1.vb and click Rename
Type AltairRealtors.vb and press Enter
Right-click the form and click View Code
Just above the Public Class line, import the System.Data.SqlClient namespace
In the Class Name combo box, select (AltairRealtors Events)

In the Method Name combo box, select Load and implement the event as follows:

Imports System.Data.SqlClient

Public Class AltairRealtors

    Private Sub AltairRealtors_Load(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles Me.Load
        Using Connect As SqlConnection = _
  		New SqlConnection("Data Source=(local);" & _
  				  "Integrated Security='SSPI';")

            Dim strDatabase As String = "IF EXISTS (" & _
             		"SELECT * " & _
             		" FROM sys.databases " & _
             		" WHERE name = N'AltairRealtors1' " & _
             		")" & _
             		"DROP DATABASE AltairRealtors1;" & _
             		"CREATE DATABASE AltairRealtors1;"

            Dim cmdAltairRealtors As SqlCommand = New SqlCommand(strDatabase, _
            Connect)

            Connect.Open()
            cmdAltairRealtors.ExecuteNonQuery()

            MsgBox("The AltairRealtors1 database has been created")
        End Using

        Using Connect As SqlConnection = _
     		New SqlConnection("Data Source=(local);" & _
     				  "Database='AltairRealtors1';" & _
     				  "Integrated Security='SSPI';")

            Dim strAltairRealtors As String = _
             		"CREATE TABLE Properties" & _
             		"(" & _
             		"PropertyID int identity(1,1) NOT NULL," & _
            		"PropertyNumber char(6)," & _
             		"Address varchar(100)," & _
             		"City varchar(50)," & _
             		"State char(2)," & _
             		"ZIPCode varchar(12)," & _
             		"PropertyType varchar(40)," & _
             		"Condition varchar(32)," & _
             		"Bedrooms smallint," & _
             		"Bathrooms float," & _
             		"FinishedBasement bit," & _
             		"IndoorGarage bit," & _
             		"Stories smallint," & _
             		"YearBuilt smallint," & _
             		"MarketValue money" & _
                           ");"

            Dim cmdAltairRealtors As SqlCommand = _
      		 New SqlCommand(strAltairRealtors, _
      				Connect)

            Connect.Open()
            cmdAltairRealtors.ExecuteNonQuery()

            MsgBox("The Properties table has been created")
        End Using

        Using Connect As SqlConnection = _
	      New SqlConnection("Data Source=(local);" & _
      				"Database='AltairRealtors1';" & _
      				"Integrated Security='SSPI';")

            Dim strAltairRealtors As String = _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('524880', '1640 Lombardo Ave', 'Silver Spring', 'MD'," & _
          "'20904', 'Single Family', 'Good', 4, 2.5, 3, 1, 3, 1995, 495880.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('688364', '10315 North Hacht Rd', 'College Park', 'MD'," & _
            "'20747', 'Single Family', 'Excellent', 4, 3.5, 3," & _
            "1, 2, 2000, 620724.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, FinishedBasement," & _
            "Stories, MarketValue)" & _
            "VALUES('611464', '6366 Lolita Drive', 'Laurel', 'MD'," & _
            "'20707', 'Single Family', 'Good', 1, 2, 422625.00);" & _
                      "" & _
            "INSERT INTO Properties(Address, City, PropertyType," & _
            "Bedrooms, MarketValue)" & _
            "VALUES('9002 Palasko Hwy', 'Tysons Corner'," & _
            "'Condominium', 2, 422895.00);" & _
                      "" & _
            "INSERT INTO Properties(PropertyNumber, State," & _
            "ZIPCode, Bedrooms, YearBuilt, MarketValue)" & _
            "VALUES('420115', 'DC'," & _
            "'20011', 2, 1982, 312555);" & _
                      "" & _
            "INSERT INTO Properties(PropertyNumber, City, ZIPCode," & _
            "PropertyType, Bedrooms, YearBuilt, MarketValue)" & _
            "VALUES('917203', 'Alexandria', '22024'," & _
            "'Single Family', 3, 1965, 345660.00);" & _
                      "" & _
            "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "PropertyType, Condition, Bedrooms, Bathrooms, MarketValue)" & _
            "VALUES('200417', '4140 Holisto Crt', 'Germantown', 'MD'," & _
            "'Condominium', 'Excellent', 2, 1, 215495.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('927474', '9522 Lockwood Rd', 'Chevy Chase', 'MD'," & _
            "'20852', 'Townhouse', 'Bad Shape', 3, 2.5, 3, 0, 3," & _
            "1992, 415665.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
            "VALUES('207850', '14250 Parkdoll Rd', 'Rockville', 'MD'," & _
            "'20854', 'Townhouse', 'Good', 3, 2.5, 2, 1, 2," & _
            "1988, 325995.00);" & _
                      "" & _
            "INSERT INTO Properties(City, PropertyType, Bedrooms," & _
            "YearBuilt, MarketValue)" & _
            "VALUES('Washington', 'Townhouse', 4, 1975, 366775.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
            "YearBuilt, MarketValue)" & _
          "VALUES('288540', '10340 Helmes Street #408', 'Silver Spring', 'MD'," & _
            "'20906', 'Condominium', 'Good', 1, 1, 2000, 242775.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('247472', '1008 Coppen Street', 'Silver Spring', 'MD'," & _
            "'20906', 'Single Family', 'Excellent'," & _
            "3, 3, 3, 1, 3, 1996, 625450.00);" & _
                      "" & _
            "INSERT INTO Properties(City, ZIPCode, PropertyType, " & _
            "Stories, YearBuilt, MarketValue)" & _
            "VALUES('Chevy Chase', '20956', 'Single Family', " & _
            "3, 2001, 525450.00);" & _
                      "" & _
            "INSERT INTO Properties(Address, City, State," & _
            "PropertyType, Condition, Bedrooms, MarketValue)" & _
            "VALUES('686 Herod Ave #D04', 'Takoma Park', 'MD'," & _
            "'Condominium', 'Excellent', 2, 360885.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
            "VALUES('297446', '14005 Sniders Blvd', 'Laurel', 'MD'," & _
            "'20707', 'Townhouse', 'Needs Repair'," & _
            "4, 1.5, 3, 1, 2, 2002, 412885.00);" & _
                      "" & _
           "INSERT INTO Properties(City, ZIPCode, Condition, Bedrooms," & _
            "Stories, YearBuilt)" & _
            "VALUES('Silver Spring', '20905', 'Good'," & _
            "4, 2, 1965);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
            "VALUES('924792', '680 Prushia Rd', 'Washington', 'DC'," & _
            "'20008', 'Single Family', 'Good'," & _
            "5, 3.5, 3, 0, 3, 2000, 555885.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('294796', '14688 Parrison Street', 'College Park', 'MD'," & _
            "'20742', 'Single Family', 'Excellent'," & _
            "5, 2.5, 2, 1, 2, 1995, 485995.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
            "YearBuilt, MarketValue)" & _
           "VALUES('811155', '10340 Helmes Street #1012', 'Silver Spring'," & _
            "'MD', '20906', 'Condominium', 'Good'," & _
            "1, 1, 2000, 252775.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
           "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('447597', '4201 Vilamar Ave', 'Hyattsville', 'MD'," & _
            "'20782', 'Townhouse', 'Excellent'," & _
            "3, 2, 2, 1, 3, 1992, 365880.00);" & _
                      "" & _
            "INSERT INTO Properties(Address, ZIPCode, Bathrooms)" & _
            "VALUES('1622 Rombard Str', 20904, 2.5);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
                "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('297415', '980 Phorwick Street', 'Washington', 'DC'," & _
            "'20004', 'Single Family', 'Good'," & _
            "4, 3.5, 3, 3, 1, 2004, 735475.00);" & _
                      "" & _
           "INSERT INTO Properties(PropertyNumber, Address, City, State," & _
            "ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms," & _
                "FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)" & _
           "VALUES('475974', '9015 Marvin Crow Ave', 'Gaithersburg', 'MD'," & _
            "'20872', 'Single Family', 'Needs Repair'," & _
            "4, 2.5, 3, 1, 1, 1965, 615775.00);"

            Dim cmdAltairRealtors As SqlCommand = _
             New SqlCommand(strAltairRealtors, _
            Connect)

            Connect.Open()
            cmdAltairRealtors.ExecuteNonQuery()

            MsgBox("Some records have been created in the Properties table.")
        End Using
    End Sub
End Class

Execute the application
Close the form and return to your programming environment
Return to the form (or display the form)
From the Menu & Toolbars section of the Toolbox, click ContextMenuStrip and click the form
Name it cmnProperties

Create the menu items as follows:

Text	(Name)	Image
Filter by &Selection	mnuFilterBySelection	filtsel.ico
Filter &Excluding Selection	mnuFilterExclSel	filtexcl.ico
Separator
Sort &Ascending	mnuSortAscending	Ascending.ico
Sort &Descending	mnuSortDescending	Descending.ico
Separator
&Remove Filter/Sort	mnuRemoveFilterSort	rmvfiltsrt.ico

Design the form as follows:

Control	Text	Name	Other Properties
DataGridView		dgvProperties	Anchor: Top, Bottom, Left, Right ContextMenuStrip: cmnProperties
GroupBox	Fields to Show	grpFieldsToShow	Anchor: Bottom, Left, Right
RadioButton	Show all Fields	rdoShowAllFields
RadioButton	Show Only	rdoShowSomeFields
CheckedListBox		clbColumns	CheckOnClick: True MultiColumn: True Anchor: Bottom, Left, Right
Button	Execute	btnExecute	Anchor: Bottom, Right
Button	Close	btnClose	Anchor: Bottom, Right

Right-click the form and click View Code

Change the Load event as follows:

Private Sub AltairRealtors_Load(ByVal sender As Object, _
                                ByVal e As System.EventArgs) _
                                Handles Me.Load
    Using Connect As SqlConnection = _
	  New SqlConnection("Data Source=(local);" & _
      			    "Database='AltairRealtors1';" & _
      			    "Integrated Security='SSPI';")

        Dim strSelect As String = "SELECT * FROM Properties;"

        Dim cmdProperties As SqlCommand = _
                    New SqlCommand(strSelect, _
        		           Connect)
        Dim sdaProperties As SqlDataAdapter = _
             New SqlDataAdapter(cmdProperties)
        Dim bsProperties As BindingSource = New BindingSource

        Dim dsProperties As DataSet = New DataSet("PropertiesSet")
        sdaProperties.Fill(dsProperties)

        Connect.Open()
        bsProperties.DataSource = dsProperties.Tables(0)

        dgvProperties.DataSource = bsProperties
        For Each col As DataColumn In dsProperties.Tables(0).Columns
            clbColumns.Items.Add(col.ColumnName)
        Next
    End Using

    rdoShowAllFields.Checked = True
End Sub

In the Class Name combo box, select btnExecute

In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnExecute_Click(ByVal sender As Object, _
                             ByVal e As System.EventArgs) _
                             Handles btnExecute.Click
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='AltairRealtors1';" & _
     			   "Integrated Security='SSPI';")

        Dim strColumns As String = ""

        For Each str As String In clbColumns.CheckedItems
            strColumns = strColumns & ", " & str
        Next

        Dim strResult As String

        If rdoShowAllFields.Checked = True Then
            strResult = "SELECT * FROM Properties"
        Else
            strResult = "SELECT " & _
                        strColumns.Substring(1) & _
                        " FROM Properties"
        End If

        Dim cmdProperties As SqlCommand = _
            New SqlCommand(strResult, Connect)
        Dim sdaProperties As SqlDataAdapter = _
             New SqlDataAdapter(cmdProperties)
        Dim bsProperties As BindingSource = New BindingSource

        Dim dsProperties As DataSet = New DataSet("PropertiesSet")
        sdaProperties.Fill(dsProperties)

        Connect.Open()
        bsProperties.DataSource = dsProperties.Tables(0)

        dgvProperties.DataSource = bsProperties
    End Using
End Sub

In the Class Name combo box, select clbColumns

In the Method Name combo box, select SelectedIndexChanged and implement the event as follows:

Private Sub clbColumns_SelectedIndexChanged(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                              Handles clbColumns.SelectedIndexChanged
    If clbColumns.CheckedItems.Count < 1 Then
        rdoShowAllFields.Checked = True
    Else
        rdoShowSomeFields.Checked = True
    End If
End Sub

In the Class Name combo box, select btnClose

In the Method Name combo box, select Click and implement the event as follows:

Private Sub btnClose_Click(ByVal sender As Object, _
                           ByVal e As System.EventArgs) _
                           Handles btnClose.Click
    End
End Sub

Execute the application to see the result
Click a few check boxed in the bottom control
Click the Execute button
Close the form and return to your programming environment

Sorting the Records in SQL

In SQL, to specify the sorting order, use the ORDER BY expression. The formula to follow is:

SELECT What FROM WhatObject ORDER BY WhatField;

The column used as the basis must be recognized as part of the selected columns. For example, to get a list of students in alphabetical order based on the LastName column, you can use the following statement:

SELECT FirstName, 
       LastName, 
       DateOfBirth, 
       Sex
FROM Students
ORDER BY LastName;

This would produce:

Using the SQL

In the same way, you can get the list of girls followed by the list of boys by ordering the list in alphabetical order based on the Sex column. The statement to get this result can be written as follows:

SELECT FirstName, LastName, Gender, EmailAddress
FROM Students
ORDER BY Gender

As another example, to list all students arranged in alphabetical order by their last name, you can change the statement as follows:

SELECT * FROM Students
ORDER BY LastName

By default, records are ordered in Ascending order. Nevertheless, the Ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in Ascending order including the first and last names, you would use a statement as follows:

SELECT * FROM Students
ORDER BY LastName ASC

On the other hand, if you want to sort records in reverse order, you can use the DESC keyword instead. It produces the opposite result to the ASC effect. Here is an example:

SELECT FirstName,
       LastName,
       Gender,
       ParentsNames,
       SPHome
FROM Students
ORDER BY LastName DESC;

This would produce:

Sorting Records

Practical Learning: Sorting the Records

Change the design of the form as follows:

Control	Text	Name	Other Properties
Label	Arrange
ComboBox		cbxColumns	DropDownStyle: DropDownList
Label	in
ComboBox		cbxAscendingDescending	DropDownStyle: DropDownList Items: Ascending Order Descending Order

Right-click the form and click View Code

Change the Load event as follows:

Private Sub AltairRealtors_Load(ByVal sender As Object, _
                                ByVal e As System.EventArgs) _
                                Handles Me.Load
    ' Open a connection to the database
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='AltairRealtors1';" & _
     			   "Integrated Security='SSPI';")

        ' This statement creates a list of all properties
        Dim strSelect As String = "SELECT * FROM Properties;"
        ' This data set will hold the tables of the database
        Dim dsProperties As DataSet = New DataSet("PropertiesSet")

        ' Create a command to perform on the connection
        Dim cmdProperties As SqlCommand = _
        New SqlCommand(strSelect, _
                       Connect)

        ' Create a data adapter that will populate the data set
        Dim sdaProperties As SqlDataAdapter = _
    	         New SqlDataAdapter(cmdProperties)

        ' Create a binding source
        Dim bsProperties As BindingSource = New BindingSource

        ' Fill the data set with the values
        sdaProperties.Fill(dsProperties)

        ' Open the connection
        Connect.Open()

        ' Get the table from the data set
        Dim tblProperties As DataTable = dsProperties.Tables(0)
        ' Assign that table to the binding source
        bsProperties.DataSource = tblProperties

        ' Apply that binding source to the data grid view
        dgvProperties.DataSource = bsProperties

        ' Get a list of the columns of the table and
        ' put them the names of those columns in the checked list box
        For Each col As DataColumn In dsProperties.Tables(0).Columns
            clbColumns.Items.Add(col.ColumnName)
            cbxColumns.Items.Add(col.ColumnName)
        Next

        rdoShowAllFields.Checked = True
        cbxColumns.SelectedIndex = 0
        cbxAscendingDescending.SelectedIndex = 0
    End Using
End Sub

Change the code of the Click event of the Execute button as follows:

Private Sub btnExecute_Click(ByVal sender As Object, _
                             ByVal e As System.EventArgs) _
                             Handles btnExecute.Click
        
    ' Open a connection to the database
    Using Connect As SqlConnection = _
	      New SqlConnection("Data Source=(local);" & _
        	  	        "Database='AltairRealtors1';" & _
         			"Integrated Security='SSPI';")

        ' Get the list of columns checked in the list box
        ' and put it in the Arrange combo box
        Dim strColumns As String = ""

        For Each str As String In clbColumns.CheckedItems
            strColumns = strColumns + ", " & str
        Next

        ' Find out what radio button is selected and use it
        ' to know what column(s) will be used
        Dim strResult As String = ""

        If rdoShowAllFields.Checked = True Then
            strResult = "SELECT * FROM Properties"
        Else
            strResult = "SELECT " & _
	                strColumns.Substring(1) & _
            	        " FROM Properties"
        End If

        ' Find out what sort order is selected and apply it
        If cbxAscendingDescending.Text = "Ascending Order" Then
            strResult = strResult + " ORDER BY " & cbxColumns.Text & " ASC;"
        Else
            strResult = strResult + " ORDER BY " & cbxColumns.Text & " DESC;"
        End If

        ' Create a command to execute on the database
        Dim cmdProperties As SqlCommand = _
     		New SqlCommand(strResult, Connect)
        ' Create a data adapter that will populate the data set
        Dim sdaProperties As SqlDataAdapter = _
 	           New SqlDataAdapter(cmdProperties)
        ' Create a binding source
        Dim bsProperties As BindingSource = New BindingSource
        ' Create a data set
        Dim dsProperties As DataSet = New DataSet("PropertiesSet")

        ' Fill the data set with the records in the data adapter
        sdaProperties.Fill(dsProperties)

        ' Open the connection
        Connect.Open()
        ' Assign the Properties table to the binding source
        bsProperties.DataSource = dsProperties.Tables(0)

        ' Assign the binding source to the data grid view
        dgvProperties.DataSource = bsProperties
    End Using
End Sub

Change the code of the SelectedIndexChanged event of the checked list box as follows:

Private Sub clbColumns_SelectedIndexChanged(ByVal sender As Object, _
                                            ByVal e As System.EventArgs) _
                                   Handles clbColumns.SelectedIndexChanged
    ' This list will hold the names of checked columns in the list box
    Dim lstColumns As List(Of String) = New List(Of String)

    ' Assist the user with checking the radio buttons
    ' based on the contents of the list box
    If clbColumns.CheckedItems.Count < 1 Then
        rdoShowAllFields.Checked = True
    Else
        rdoShowSomeFields.Checked = True
    End If

    ' If the second radio button is selected, get the columns 
    ' checked in the list box and add them to the Arrange combo box
    If rdoShowSomeFields.Checked = True Then
        cbxColumns.Items.Clear()

        For Each objItem As Object In clbColumns.CheckedItems
            cbxColumns.Items.Add(objItem)
        Next
        cbxColumns.SelectedIndex = 0
    End If
End Sub

Execute application and test the form
Close the form and return to your programming environment

Sorting the Records in the Data Grid View

If you use a data grid view in your application, you can sort records without writing a single line of code. To sort the records based on a particular column, click the column header. After clicking for the first time, the column is sorted alphabetically, incrementally, or chronologically and an up-pointing arrow button would appear on the column header. Here is an example on the City column:

Sorting Records Using the Data Grid View

To sort records in reverse order based on a particular column, you can click the column again. Or, you must first click the column header to sort in order, then click the same column header again to reverse. When the records are sorted in reverse, a down-pointing arrow button would appear on the column header. Here is an example on the ZIPCode column:

Sorting Records Using the Data Grid View

Practical Learning: Sorting the Records Using the Data Grid View

In the top section of the file, declare a DataGridViewColumn variable named colSelected:

Imports System.Data.SqlClient

Public Class AltairRealtors
    Private colSelected As DataGridViewColumn

    . . . No Change

In the Load event, initialize the new variable using the default constructor of its class:

Private Sub AltairRealtors_Load(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles Me.Load
        colSelected = New DataGridViewColumn

    . . . No Change

In the Class Name combo box, select dgvProperties

In the Method Name combo box, select MouseDown and implement the event as follows:

Private Sub dgvProperties_MouseDown(ByVal sender As Object, _
                       ByVal e As System.Windows.Forms.MouseEventArgs) _
                                    Handles dgvProperties.MouseDown
    ' Identity the point where the mouse landed
    Dim hti As DataGridView.HitTestInfo = dgvProperties.HitTest(e.X, e.Y)
    ' Create a cell reference based on the coordinates of the mouse
    Dim celSelected As DataGridViewCell = _
	    dgvProperties.Rows(hti.RowIndex).Cells(hti.ColumnIndex)

    ' Just in case the user right-clicked, select that cell
    dgvProperties.CurrentCell = celSelected

    ' Identify the selected column and initialize our variable with it
    colSelected = dgvProperties.Columns(hti.ColumnIndex)
End Sub

In the Class Name combo box, select mnuSortAscending

In the Method Name combo box, select Click and implement the event as follows:

Private Sub mnuSortAscending_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles mnuSortAscending.Click
    ' Since we know what column is going to be used to sort the record, 
    ' synchronize it with the Arrange combo box
    cbxColumns.Text = colSelected.Name
    ' Since the user clicked Ascending, synchronize with the in combo box
    cbxAscendingDescending.SelectedIndex = 0
    ' Sort the records using the data grid view
    dgvProperties.Sort(colSelected, _
                       System.ComponentModel.ListSortDirection.Ascending)
End Sub

In the Class Name combo box, select mnuSortDescending

In the Method Name combo box, select Click and implement the event as follows:

Private Sub mnuSortDescending_Click(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles mnuSortDescending.Click
    ' Since we know what column is going to be used to sort the record, 
    ' synchronize it with the Arrange combo box
    cbxColumns.Text = colSelected.Name
    ' Since the user clicked Ascending, synchronize with the in combo box
    cbxAscendingDescending.SelectedIndex = 1
    ' Sort the records using the data grid view
    dgvProperties.Sort(colSelected, _
		       System.ComponentModel.ListSortDirection.Descending)
End Sub

Execute the application
Right-click a value in a column and sort the records
Close the form and return to your programming environment

Filtering Data

Introduction to Operators

In the previous lesson, we learned that we could analyze data using the Table window. We also learned how to analyze data by creating and executing a SQL statement in a query window. Instead of selecting all data as we have done so far using the SELECT keyword, you can present a condition that the database would follow to isolate specific records.

When analyzing data or if you are creating a query using the Table window, you can type an expression that uses one or more logical operators we reviewed in Lesson 21. Here is an example of an expression

> '12/31/1993'

This means that the dates that occur after 1993 would be selected.

Practical Learning: Introducing Data Filtering

Start a new Windows Application named AltairRealtors5
In the Solution Explorer, right-click Form1.vb and click Rename
Type AltairRealtors.vb and press Enter
From the Menu & Toolbars section of the Toolbox, click ContextMenuStrip and click the form
Name it cmnProperties

Create the menu items as follows:

Text	(Name)	Image
Filter by &Selection	mnuFilterBySelection	filtsel.ico
Filter &Excluding Selection	mnuFilterExclSel	filtexcl.ico
Separator
Sort &Ascending	mnuSortAscending	Ascending.ico
Sort &Descending	mnuSortDescending	Descending.ico
Separator
&Remove Filter/Sort	mnuRemoveFilterSort	rmvfiltsrt.ico

On the main menu, click Data -> Add New Data Source...
On the first page of the wizard, make sure Database is selected and click Next
In the second page of the wizard, click New Connection...
In the Server Name combo box, select the server or type (local)
In the Select or Enter a Database Name combo box, select AltairRealtors1
Click Test Connection and click OK twice
On the Data Source Configuration Wizard, make sure the new connection is selected
Click the + button of Connection String
Click Next
Change the connection string to cstAltairRealtors and click Next
Change the name of the data set to dsAltairRealtors
Expand the Tables node and expand the Properties node
Click the check boxes of: PropertyNumber, City, State, ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, Stories, YearBuilt, and MarketValue
Click Finish
From the Data Source window, drag the Properties object and drop it on the form
Under the form, click the controls and use the Properties window to change their names as follows:

Object New Name

propertiesBindingSource bsProperties

propertiesTableAdapter tadProperties

propertiesBindingNavigator bnProperties
On the form, click the data grid view control
In the Properties window, change its name to dgvProperties
Set its ContextMenuStrip to cmnProperties

Under the Properties window, click Edit Columns and configure the columns as follows:

Column	HeaderText	Width
PropertyNumber	Prop #	50
City		90
State		40
ZIPCode	ZIP Code	60
PropertyType	Property Type	90
Condition		80
Bedrooms	Beds	40
Bathrooms	Baths	40
Stories		45
YearBuilt	Year	40
MarketValue	Market Value	80

Complete the design of the form as follows:
Right-click the form and click View Code
Just above the Public Class line, import the System.Windows.Forms namespace
Just under the Public Class line, declare a private DataGridViewColumn variable named colSelected

Initialize the colSelected variable in the Load event using its default constructor:

Imports System.Data.SqlClient

Public Class AltairRealtors
    Private colSelected As DataGridViewColumn

    Private Sub PropertiesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, _
                                                         ByVal e As System.EventArgs) _
                                         Handles PropertiesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.bsProperties.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DsAltairRealtors)

    End Sub

    Private Sub AltairRealtors_Load(ByVal sender As System.Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles MyBase.Load
        'TODO: This line of code loads data into
        ' the() 'DsAltairRealtors.Properties' table.
        '  You can move, or remove it, as needed.
        Me.tadProperties.Fill(Me.DsAltairRealtors.Properties)

        colSelected = New DataGridViewColumn
    End Sub
End Class

In the Class Name combo box, select dgvProperties

In the Method Name combo box, select MouseDown and implement the event as follows:

Private Sub dgvProperties_MouseDown(ByVal sender As Object, _
                        ByVal e As System.Windows.Forms.MouseEventArgs) _
                                    Handles dgvProperties.MouseDown
    ' Identity the point where the mouse landed
    Dim hti As DataGridView.HitTestInfo = dgvProperties.HitTest(e.X, e.Y)
    ' Create a cell reference based on the coordinates of the mouse
    Dim celSelected As DataGridViewCell = _
	     dgvProperties.Rows(hti.RowIndex).Cells(hti.ColumnIndex)

    ' Just in case the user right-clicked, select that cell
    dgvProperties.CurrentCell = celSelected

    ' Identify the selected column and initialize our variable with it
    colSelected = dgvProperties.Columns(hti.ColumnIndex)
End Sub

In the Class Name combo box, select mnuSortAscending

In the Method Name combo box, select Click and implement the event as follows:

Private Sub mnuSortAscending_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles mnuSortAscending.Click
    ' Sort the records using the data grid view
    dgvProperties.Sort(colSelected, _
                       System.ComponentModel.ListSortDirection.Ascending)
End Sub

In the Class Name combo box, select mnuSortDescending

In the Method Name combo box, select Click and implement the event as follows:

Private Sub mnuSortDescending_Click(ByVal sender As Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles mnuSortDescending.Click
    ' Sort the records using the data grid view
    dgvProperties.Sort(colSelected, _
                       System.ComponentModel.ListSortDirection.Descending)
End Sub

Execute the application
Right-click a value in a column and sort the records
Close the form and return to your programming environment

WHERE is the Condition

If you are writing your SELECT statement, to formulate a condition, you use the WHERE keyword with a basic formula as follows:

SELECT What FROM WhatObject WHERE Expression;

The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor is called a criterion(the plural is criteria). The expression is written using the formula:

ColumnName=Value

The ColumnName factor must be an existing column of a table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. If it is a number, you can type its numeric value.

To apply a WHERE condition, if you are working from a table in the Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in the Criteria section and under the Filter column, click the box that corresponds to the field on which the condition will be applied, and type the value of the expression (only the value). Here is an example:

WHERE

If you are writing writing the SQL statement to specify the condition, after FROM table, enter WHERE followed by the whole Expression. Here is an example:

SELECT DateOfBirth, LastName, FirstName, 
       Sex, ParentsNames
FROM Students
WHERE Sex='Female';

If you are working in a table window and specify the expression in the Criteria section, the WHERE condition would be created in the SQL section and, to make it easier to read, it would be included in parentheses:

Using WHERE

When creating a condition, you can sort it if you want. If you are working in a table window, in the Criteria section, under the Sort Type column, click the box corresponding to the field that will be used as the basis. In the Filter column, click the box that corresponds to the column that will hold the condition and enter the expression. Here is an example:

WHERE

If you are writing the SQL statement, after the WHERE condition, enter the ORDER BY expression. Here is an example:

SELECT DateOfBirth, LastName, FirstName, 
       Sex, State, ParentsNames
FROM Students
WHERE State='MD'
ORDER BY LastName;

This would produce:

Using WHERE

Practical Learning: Filtering Data

In the Class Name combo box, select mnuFilterBySelection

In the Method Name combo box, select Click and implement the event as follows:

Private Sub mnuFilterBySelection_Click(ByVal sender As Object, _
                                       ByVal e As System.EventArgs) _
                                       Handles mnuFilterBySelection.Click
    Dim strResult As String = dgvProperties.CurrentCell.Value

    If strResult = "" Then
        If colSelected.ValueType.Equals(Type.GetType("System.Int16")) Or _
           colSelected.ValueType.Equals(Type.GetType("System.Double")) Then
            bsProperties.Filter = "IsNull(" & _
               dgvProperties.Columns(colSelected.Index).DataPropertyName & _
                                     ", '0') = 0"
        Else
            bsProperties.Filter = "IsNull(" & _
                dgvProperties.Columns(colSelected.Index).DataPropertyName & _
                                      ", 'Null Column') = 'Null Column'"
        End If
    Else
        bsProperties.Filter = _
  		dgvProperties.Columns(colSelected.Index).DataPropertyName & _
  				      " = '" & strResult & "'"
    End If
End Sub

Execute the application to test the form
Close the form and return to your programming environment

Hiding a Column

In our SELECT statements so far, we were selecting the columns we needed to display. When formulating such a statement, you can apply a condition to a column without including that column in the result. For example, imagine you create a statement that produces a list of female students. Since we know that the result would show only the girls, it becomes redundant to include the Sex column in the statement. In this case, you can hide that column in the result.

To hide a column from a SELECT statement, if you are working from the table in Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in the Diagram or in the Criteria sections, select the column. Then, in the Criteria section, under the Output column, uncheck the box corresponding to the field you want to hide.

If you are writing a SQL statement, omit the column in the SELECT statement but involve it in the WHERE condition. Here is an example:

SELECT DateOfBirth, LastName,
       FirstName, State, ParentsNames
FROM Students
WHERE Sex='Female';
GO

This would produce:

WHERE is the field?

Notice that the SELECT statement does not have the Gender column and the resulting query does not show the Gender column.

Negating Some Records

In Lesson 21, we saw that you could use the NOT operator to negate the validity of a Boolean expression. Consider the following statement:

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM Students
WHERE Sex = 'Female';

When this statement is executed, a list of female students would display. Instead of girls, to get a list of male students, you can negate the WHERE condition. To do this, type NOT before the expression. Here is an example:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT Gender = 'Female';
GO

To make this condition easier to read, you should include the positive expression in parentheses. This would be done as follows:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM Students
WHERE NOT (Sex = 'Female');

This clearly indicates that it is the expression in the parentheses that is being negated. In the same way, you can use the IS NOT NULL to find the records that are not null. For example, you can create a list of only records that do not have a null value on a certain column. Here is an example:

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM   Students
WHERE  State IS NOT NULL;

When this statement is executed, the table would display only the records that include a state for each student.

Practical Learning: Filtering Data by Exclusion

In the Class Name combo box, select mnuFilterExclSel

In the Method Name combo box, select Click and implement the event as follows:

Private Sub mnuFilterExclSel_Click(ByVal sender As Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles mnuFilterExclSel.Click
    Dim strResult As String = dgvProperties.CurrentCell.Value

    If strResult = "" Then
        If colSelected.ValueType.Equals(Type.GetType("System.Int16")) Or _
           colSelected.ValueType.Equals(Type.GetType("System.Double")) Then
            bsProperties.Filter = "IsNull(" & _
                 dgvProperties.Columns(colSelected.Index).DataPropertyName & _
                                       ", '0') <> 0"
        Else
            bsProperties.Filter = "IsNull(" & _
                 dgvProperties.Columns(colSelected.Index).DataPropertyName & _
                                       ", 'Null Column') <> 'Null Column'"
        End If
    Else
        bsProperties.Filter = _
               dgvProperties.Columns(colSelected.Index).DataPropertyName & _
                                     " <> '" & strResult & "'"
    End If
End Sub

In the Class Name combo box, select mnuRemoveFilterSort

In the Method Name combo box, select Click and implement the event as follows:

Private Sub mnuRemoveFilterSort_Click(ByVal sender As Object, _
                                      ByVal e As System.EventArgs) _
                                      Handles mnuRemoveFilterSort.Click
    bsProperties.Filter = Nothing
    bsProperties.Sort = Nothing
End Sub

Execute the application to test the form:
Close the form and return to your programming environment


Previous	Copyright � 2008, Yevol	Next