Filtering Records

Introduction to Operators

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
     
     
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:
    
    
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

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:

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:

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

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

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

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

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

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