Home

Filtering Records

 

Filtering Records

 

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 LearningPractical Learning: Introducing Data Filtering

  1. Start a new Windows Application named AltairRealtors5
  2. In the Solution Explorer, right-click Form1.vb and click Rename
  3. Type AltairRealtors.vb and press Enter
  4. From the Menu & Toolbars section of the Toolbox, click ContextMenuStrip and click the form
  5. Name it cmnProperties
  6. 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 
  7. On the main menu, click Data -> Add New Data Source...
  8. On the first page of the wizard, make sure Database is selected and click Next
  9. In the second page of the wizard, click New Connection...
  10. In the Server Name combo box, select the server or type (local)
  11. In the Select or Enter a Database Name combo box, select AltairRealtors1
  12. Click Test Connection and click OK twice
  13. On the Data Source Configuration Wizard, make sure the new connection is selected
    Click the + button of Connection String
  14. Click Next
  15. Change the connection string to cstAltairRealtors and click Next
  16. Change the name of the data set to dsAltairRealtors
  17. Expand the Tables node and expand the Properties node
  18. Click the check boxes of: PropertyNumber, City, State, ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, Stories, YearBuilt, and MarketValue
     
     Data Source Configuration Wizard
  19. Click Finish
  20. From the Data Source window, drag the Properties object and drop it on the form
  21. 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
  22. On the form, click the data grid view control
  23. In the Properties window, change its name to dgvProperties
  24. Set its ContextMenuStrip to cmnProperties
  25. 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
  26. Complete the design of the form as follows:

    Altair Realtors - Real Estate Properties Listing
  27. Right-click the form and click View Code
  28. Just above the Public Class line, import the System.Windows.Forms namespace
  29. Just under the Public Class line, declare a private DataGridViewColumn variable named colSelected
  30. 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
  31. In the Class Name combo box, select dgvProperties
  32. 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
  33. In the Class Name combo box, select mnuSortAscending
  34. 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
  35. In the Class Name combo box, select mnuSortDescending
  36. 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
  37. Execute the application
  38. Right-click a value in a column and sort the records
  39. 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 LearningPractical Learning: Filtering Data

  1. In the Class Name combo box, select mnuFilterBySelection
  2. 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
  3. Execute the application to test the form
     
    Altair Realtors
    Altair Realtors
    Altair Realtors
  4. 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 LearningPractical Learning: Filtering Data by Exclusion

  1. In the Class Name combo box, select mnuFilterExclSel
  2. 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
  3. In the Class Name combo box, select mnuRemoveFilterSort
  4. 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
  5. Execute the application to test the form:
     
    Altair Realtors
    Altair Realtors 
  6. Close the form and return to your programming environment
 

Home Copyright © 2008-2012 FunctionX