You can select rows from an existing DataSet by using a filter . In Chapter 3, you used the Sort method of a BindingSource; here you will learn to use the BindingSource’s Filter method.
' Filter an existing DataSet based on a combo box selection. SelectionString = JobComboBox.SelectedValue.ToString
With BindingSource1
.DataMember = "employee"
.Filter = "job_id = " & SelectionString .Sort = "Name"
End With
Y ou can undock both toolbars and
align them to be side-by-side. ■
Writing a Filter
The rules for creating a filter are the same as for a WHERE clause of a SQL state- ment. Specify the field, a comparison operator (usually the equal sign), and the value to match.
"LastName = Jones" "SalesAmount = 1000" "Quantity > 0"
The tricky part comes when you want to filter on a string that may contain spaces. The value to match must be enclosed in single quotes.
"Title = 'A Great Book'"
If you are matching a value stored in a variable, you must concatenate the elements to create a filter string in the correct format. For string data, you must concatenate the single quotes around the data values:
"Title = '" & TitleString & "'"
"Title = '" & TitleComboBox.SelectedValue.ToString & "'"
In the second statement, assume that TitleComboBox.SelectedValue = “Great Expectations”. After the concatenation, the entire string would be “ Title =
' Great Expectations '”, which is exactly what is needed for the filter. For numeric values, you create a filter string without the quotes:
"SalesAmount = " & AmountDecimal.ToString "Quantity > " & QuantityTextBox.Text
Here is a list of the most useful operators. You can find a complete listing of operators on the “Comparison Operators” page in MSDN.
Operator Meaning Examples
= equal to "Subject = 'Business'"
"Subject = '" & SubjectTextBox.Text & "'"
> greater than "Sales > 1000"
"Sales > " & SalesTextBox.Text
< less than "Sales < 1000"
"Sales < " & SalesTextBox.Text
Like pattern match "Subject Like ('B%' ) " (For SQL Server databases) "Subject Like 'B*'" (For Access databases)
Binding a List at Run Time
When you display filtered data in a list, usually you want the list to appear empty until a selection is made. In that case, you must bind the control in code, rather than at design time.
' Bind the list box with the filtered data. With EmployeeListBox
.DataSource = BindingSource1 .DisplayMember = "Name" End With
When you set the DataSource, the list fills with the data from the Binding- Source.
Filtering a DataSet—Step-by-Step
In this step-by-step exercise, you will again allow the user to select a job and display the employees that match. However, this time, the job description will appear in a combo box for selection, and you will display the employees by full name in a list box. Figure 4.13 shows the completed application.
This exercise uses a different technique to create data-bound controls: You will first add the controls from the toolbox to the form and then set up the data binding.
F i g u r e 4 . 1 3
The completed filter exercise. The user selects a job descrip- tion from the combo box. The corresponding job_id is used to filter the data, which is dis- played in the list box.
Begin a New Project
STEP 1: Begin a new Windows Application project called Ch04RowFilter. STEP 2: Rename the form to EmployeesForm and set its Text property to
Employees By Job.
Create the Data Source
STEP 1: Add a new data source connected to the Pubs database. Select both
the employee and jobs tables.
STEP 2: Drag a BindingSource object from the Data section of the toolbox to the form.
STEP 3: In the Properties window, set the DataSource of BindingSource1 to
PubsDataSet and the DataMember to employee. Notice that a Pubs- DataSet component and an EmployeeTableAdapter component are added to the component tray.
STEP 4: In the DataSet Designer, add a column to the employee table. Name
the column Name and set the Expression property to concatenate the first name and last name fields. You can preview the data to test the new column.
STEP 5: Configure both TableAdapters to not generate Insert , Update , and
Delete statements.
Set Up the Form
STEP 1: Add a combo box and a list box from the toolbox to the form. (Refer to Figure 4.13 .) Name the list box EmployeeListBox and the combo box JobComboBox .
STEP 2: Drag the jobs table from the Data Sources window and drop it on the combo box. Use the smart tag to confirm that the control is now bound to the JobsBindingSource and the display member is set to the job description. Also notice that the ValueMember is set to job_id, the primary key of the jobs table.
Reviewing Combo Box Properties
Combo boxes and list boxes have several properties that you should know how to use. As you know, the DisplayMember determines the items that appear to the user. The ValueMember can hold a key that identifies the item that dis- plays. For the JobComboBox, the DisplayMember holds the job description and the ValueMember holds the job_id ( Figure 4.14 ). When the user makes a selection from a combo box, the control’s SelectedIndexChanged and Selec- tionChangeCommitted events fire and the SelectedValue property is set to the ValueMember of the selected item. You will use the SelectedValue property to create the filter to find the matching employees.
Write the Code
STEP 1: Select the combo box and click on the Events button in the Properties window. Locate SelectionChangeCommitted and double-click to open
New Hire - Job not specified Chief Executive Officer Business Operations Manager Chief Financial Officer Publisher
Managing Editor Marketing Manager Public Relations Manager Acquisitions Manager Productions Manager Operations Manager Editor Sales Representative Designer 1 2 3 4 5 6 7 8 9 10 11 12 13 14 DisplayMember job_desc job_id ValueMember F i g u r e 4 . 1 4
The user selects from the DisplayMember. Use the ValueMember to find matching values.
that event handler in the Code Editor window. The SelectionChange- Committed event is preferable to the SelectedIndexChanged event, which occurs several times during loading the form and binding the controls.
STEP 2: Write the code:
Private Sub JobComboBox_SelectionChangeCommitted(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles JobComboBox.SelectionChangeCommitted ' Filter the matching employees in the DataSet.
Dim SelectionString As String
SelectionString = JobComboBox.SelectedValue.ToString With BindingSource1
.DataMember = "employee"
.Filter = "job_id = '" & SelectionString & "'" .Sort = "Name" End With With EmployeeListBox .DataSource = BindingSource1 .DisplayMember = "Name" End With End Sub
Run the Program
STEP 1: Run the program. Select a job title from the combo box and the em-
ployees that match appear in the list.