Criteria For Data Analysis

 Introduction

Besides selecting and sorting records, data analysis also consists of restricting the list of records to examine at one time. To do this, you would include a clause in your SQL statement to create a particular. The interpreter would select only records that abide by the rule of your choice. The rule used to restrict the list of available records is called a criterion. The plural is criteria but it can also be used in singular.

 WHERE is Data

To set a criterion in your SQL statement, you use the WHERE keyword in the following formula:

`SELECT What FROM TableName WHERE Condition`

The What and the TableName factors follow the same rules we have applied so far.

The Condition factor is used to specify the rule applied to restrict the list of records. A condition is formulated as a logical expression that produces a Boolean result. To create an expression, you use comparison operators supported by the SQL. You are already familiar with some of the operators.

 Comparison Operators

 Introduction

A comparison operator is used to compare one value to another. Normally, from your knowledge of Visual Basic .NET, you should already be familiar with all the routine comparisons performed on values and variables. Most operators used in SQL for comparison are the same as those used in Visual Basic .NET. Based on this, you can perform your comparisons on column names and constant values.

If you are using .NET Framework classes of the System.Data.SqlTypes namespace in your code, each one of its classes has an overloaded method that corresponds to each comparison operator we will review.

 Equal =

The equality operator is used to compare one value to another. For a WHERE condition, this operator can be used to compare the values of a column to a particular value for a match. The condition would have the following formula:

`ColumnName = 'Value'`

In this case, you can specify a name for the column that holds the type of value you want to use as a criterion. Once you have identified the column, you can assign it the particular value of its records that would excluding non-abiding columns. For example, imagine you have a list of business customers in different countries, to get a list of only the customers who live in Mexico, you would set the criterion as Country = 'Mexico'. Here is an example of such a statement:

If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an Equals() method overloaded with two versions. The first corresponds to the routine Equals() method that all .NET Framework class inherit from the Object class. The second version allows you to compare, for equality, two columns created with the same data type.

 Comparison Operators: Not Equal <>
 To find out if two fields hold different values, you can use the inequality operator which is represented by <>. Its syntax is: `Value1 <> Value2` This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a TRUE value. If they hold the same value, the comparison produces FALSE. This shows that the equality (=) and the inequality (<>) operators are opposite each other. Here is an example: ```SELECT FirstName, LastName, Gender FROM Students WHERE Gender <> 'Female';``` If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an NotEquals() method used to compare, for inequality, two columns created with the same data type.

 Comparison Operators: Less Than <
 The "Less Than" operator uses the following formula: `Value1 < Value2` If Value1 holds a value that is lower than that of Value2, the comparison produces TRUE. If Value1 holds a value that is greater than or similar to that of Value2, the comparison renders FALSE. You can formulate the condition as follows: ```SELECT FirstName, LastName, Gender, DateOfBirth FROM Students WHERE DateOfBirth < '1988-06-01'``` Here is an example:
 If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an LessThan() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is lower than the second, this method returns true. Otherwise, it returns false.
 Comparison Operators: Less Than or Equal <=
 When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with the "Less Than Or Equal To" operator. It is represented by <= and its formula is: `Value1 <= Value2` If both operands (Value1 and Value2) hold the same value, then the comparison produces a TRUE result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a TRUE result. By contrast, if the value of Value1 is higher than that of Value2, the comparison renders a FALSE result. Notice that the > and the <= operators are opposite each other. Here is an example: ```SELECT FirstName, LastName, DateOfBirth, EmailAddress FROM Students WHERE DateOfBirth <= '1990-01-01';``` If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an LessThanOrEqual() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is lower than the second or both are equal, this method returns true. If the value of the first argument is strictly less than that of the second, this method returns false.

 Comparison Operators: Greater Than >
 The > operator is used to find out whether one value is "Greater Than" another. Its syntax is: `Value1 > Value2` The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces TRUE. Otherwise, the comparison produces FALSE. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces FALSE. If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an GreaterThan() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is higher than the second, this method returns true. Otherwise, it returns false.

 Comparison Operators: Greater Than or Equal >=
 If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is: `Value1 >= Value2` If both Value1 and Value2 hold the same value, then the comparison renders a TRUE result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces TRUE. If the value of Value1 is less than the value of Value2, the comparison produces a FALSE result. Therefore, < and >= are opposite. If you are using a class from the System.Data.SqlTypes namespace, each class is equipped with an LessThanOrEqual() method used to compare two columns created with the same data type. The value of the first argument to this method is compared with the value of the second argument. If the first value is higher than the second or both are equal, this method returns true. If the value of the first argument is strictly higher than that of the second, this method returns false.
 Practical Learning: Filtering Records
1. In the bottom section of the form, add the following controls:

 Control Name Text Items Other Properties Label Filter For: Anchor: Bottom, Left ComboBox cboFilterFor Anchor: Bottom, Left DropDownStyle: DropDownList ComboBox cboOperator = Equals <> Not Equal < Less Than <= Less Than or Equal To > Greater Than >= Greater Than or Equal To Anchor: Bottom, Left DropDownStyle: DropDownList ComboBox cboRecValue Anchor: Bottom, Left DropDownStyle: DropDownList
2. Double-click an unoccupied area of the form and change the Load event as follows:

 ```Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load Dim tblStudents As DataTable = DsROSH1.Tables("Students") Dim colStudents As DataColumnCollection = tblStudents.Columns Dim i As Integer For i = 0 To colStudents.Count - 1 Step 1 cboColumns.Items.Add(colStudents(i).ColumnName) Next cboColumns.SelectedIndex = 0 cboSortOrder.SelectedIndex = 0 SqlDataAdapter1.Fill(DsROSH1) colSelected = New DataColumn For i = 0 To colStudents.Count - 1 Step 1 cboFilterFor.Items.Add(colStudents(i).ColumnName) Next End Sub```
3. In the Class Name combo box, select mnuFiltBySel
4. In the Method Name combo box, select Click
5. Implement the event as follows:

 ```Private Sub mnuFiltBySel_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles mnuFiltBySel.Click Dim curCell As DataGridCell = dataGrid1.CurrentCell dvwStudents.RowFilter = colSelected.ColumnName & _ " = '" & CStr(dataGrid1(curCell)) & "'" End Sub```
6. In the Class Name combo box, select mnuFiltExclSel
7. In the Method Name combo box, select Click
8. Implement the event as follows:

 ```Private Sub mnuFiltExclSel_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles mnuFiltExclSel.Click Dim curCell As DataGridCell = dataGrid1.CurrentCell Text = CStr(dataGrid1(curCell)) dvwStudents.RowFilter = colSelected.ColumnName & _ " <> '" & CStr(dataGrid1(curCell)) & "'" End Sub```
9. In the Class Name combo box, select cboFilterFor
10. In the Method Name combo box, select SelectedIndexChanged
11. Implement the event as follows:

 ```Private Sub cboFilterFor_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles cboFilterFor.SelectedIndexChanged Dim strColSelected As String = cboFilterFor.Text Dim colRows As DataRowCollection = DsROSH1.Tables("Students").Rows Dim i As Integer cboRecValue.Items.Clear() cboRecValue.Items.Add("NULL") For i = 0 To colRows.Count - 1 Step 1 Dim strRecValue As String = colRows(i).Item(strColSelected).ToString() cboRecValue.Items.Add(strRecValue) Next cboOperator.SelectedIndex = 0 End Sub```
12. In the Class Name combo box, select cboRecValue
13. In the Method Name combo box, select SelectedIndexChanged
14. Implement the event as follows:

 ```Private Sub cboRecValue_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles cboRecValue.SelectedIndexChanged Dim strColSelected As String = cboFilterFor.Text Dim strOperSelected As String = cboOperator.Text Dim strRecValue As String = cboRecValue.Text If strColSelected = "" Then Exit Sub End If Dim strOperator As String = "=" If strOperSelected = "<> Not Equal" Then strOperator = "<>" ElseIf strOperSelected = "< Less Than" Then strOperator = "<" ElseIf strOperSelected = "<= Less Than or Equal To" Then strOperator = "<=" ElseIf strOperSelected = "> Greater Than" Then strOperator = ">" ElseIf strOperSelected = ">= Greater Than or Equal To" Then strOperator = ">=" End If If strRecValue = "NULL" Then dvwStudents.RowFilter = "IsNull(" & strColSelected & " & 'Null Column') = 'Null Column'" Else dvwStudents.RowFilter = strColSelected & " " & strOperator & " '" & strRecValue & "'" Text = "ROSH - Student Analysis: " & CStr(dvwStudents.Count) & " Records Found" End If End Sub```
15. In the Class Name combo box, select cboOperator
16. In the Method Name combo box, select SelectedIndexChanged
17. Implement the event as follows:

 ```Private Sub cboOperator_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles cboOperator.SelectedIndexChanged ' This event should work only if the user has already selected ' a column in the most right combo box If cboRecValue.Text = "" Then Exit Sub End If cboRecValue_SelectedIndexChanged(sender, e) End Sub```
18. Execute the application