This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Chapter Overview 17.1 Introduction ...................................................................................................................710 17.2 Data Binding..................................................................................................................710 17.3 Binding Various Controls...............................................................................................717 17.4 Using DisplayMember and ValueMember Properties ...................................................717 17.5 Creating Data Forms using Wizard ...............................................................................719 17.6 Working with Datasets for Navigation and Data Entry ..................................................723 17.7 Working with Parameterized SQL queries ....................................................................729 17.8 Using Parent/Child Relationships..................................................................................732 17.9 Handling ADO .NET Objects in Code ...........................................................................737 17.10 Chapter Summary .....................................................................................................742 17.11 Exercises...................................................................................................................743 710 Chapter 17. Database Programming 17.1 Introduction In Chapter 15, we introduced the ADO .NET architecture and its components. We have seen
how to make a connection to a database using the Connection object, how to create Data
Adaptors to query to a connected database, and how to store the query results in Dataset
objects. ADO .NET thus provides a useful structure for working with databases and applications.
We then introduced Windows controls, the building blocks of application forms, in Chapter 16.
Some of these controls include the Text Box, Combo Box, List Box, and Data Grid controls. We
have seen how the properties of these features govern the appearance and behavior of
controls. When working with our sample database, we manipulated these properties in the
Properties window and also wrote VB .NET code that allowed us to dynamically manipulate
We cannot expect to work individually with datasets (which house query results) or form controls
(which display data to the user) in order to build a successful application. Instead, we need to
tell controls where to look for data to display. In this chapter, we will explore Data Binding, the
process of associating data from data sets (in general data source) to controls.
In this chapter, we first formally define and introduce data binding concepts, then move to
simple and complex binding, and finally discuss the importance of these operations. We will
also walk through the step-by-step process of data binding using examples from the University
database. Next, we present the list of properties used for data binding for main windows
controls. Data binding helps us to create different types of VB .NET forms like: data entry,
navigation, and parent/child relationships. We explore the Data Form wizard used to create data
entry and navigation forms. We then explore parameterized queries, an important topic often
used in application development. We also give an alternative to parameterized queries to
create parent/child relationships forms using XML schema of datasets.
It is important that developers should be familiar working with ADO .NET objects, Windows
controls, and data binding in code window. This allows us to build applications that need things
to be manipulated dynamically in the code. We talk about the code for navigating into the
dataset used for form navigation. We conclude the chapter with an example that illustrates on
the VB .NET code necessary to create ADO .NET objects, windows control, and data binding. 17.2 Data Binding
Data binding is a process that binds control objects to data sources by associating data
from the data source to a control object. For example, we frequently use data binding to
associate a data grid control object to a dataset. A control object can be a windows control that
appears in a windows application or a web control in a web application. Refer to Chapter 20 for
more on these concepts. A data source is any object that provides (source) the data. Dataset,
DataTable, DataColumn, and DataRow objects are a few examples of possible data sources.
Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 711 The process of data connection links database tables to data sources while data binding
associates data sources to control objects. Interestingly, we bind data by manipulating the
properties of control objects. Properties that assist in data binding are grouped under the data
binding heading in the property window. Examples of these properties are data source, data
member, display member, value member, and SelectedItem. Next, we must determine which
control object property should receive the data from the data source. For example, the text
property of the text box control can be set to receive data from the data source so that this data
can be displayed to the user. In another example, the ForeColor property of the label control
receives data (in this case, the value of the color) from a dataset. Data binding can, then,
dynamically set the properties of a control. As we will see, the various properties of control
objects can be set to receive data from different data sources, thereby increasing the flexibility
and overall power of data binding. See Figure 17.1 for an example of the display for a simple
data binding operation.
There are different scenarios where we can use data binding to facilitate application
development. Some of these include: entering values into a database, navigating through
datasets, data lookups, and Parent/Child relationship based applications. We will use HandsOn exercises to illustrate these techniques as we go through the chapter.
Data Entry: Controls like Text Boxes, Check Boxes, Radio Buttons, and List Boxes allow
users to specify input values. If data binding is used for these controls, their data sources can
be connected to a database to insert, delete, or update the appropriate database values. We
generally put these controls together on a form referred to as a data entry form. A data entry
form simplifies the task of data entry.
Navigation: The information from a database can be displayed on forms via data binding. If a
form displays one record at a time, we can navigate through individual data records. Data
binding makes this task much more efficient.
Data Lookup: We identify a record (or a tuple) in a database by its primary key. Displaying a
primary key to the user may not be good idea in some situations. For example, the faculty table
in the University database has social security number (SSN) as its primary key. However, we
may not want to display the SSN of all users who browse through faculty records. Sometimes
primary keys are long system generated numbers and may not make any sense to most users.
In such scenarios, we may prefer to display other attributes, such as name (Professor John
Doe), to users and still use the primary key attribute in our development code. Data binding
helps us in such a situation, allowing us to bind the control using its ValueMember (set to SSN
in our example) and DisplayMember (set to faculty name) properties.
Parent/Child Relationships: Database design normalizes data into different tables to
remove data redundancy. However, in application flow, we may need information from different
tables on a single form. For example, we want to display student personal information and
grades on a single form. Required data for this form is scattered in more than one table, Abhijit Pol & Ravindra Ahuja 712 Chapter 17. Database Programming tblStudent and tblTranscript. Data binding allows us to specify relationships between related
tables to access this information.
Data binding can be classified into two categories: simple data binding and complex data
binding. Let us now discuss the advantages and disadvantages of these techniques. Tips We bind the control with the data tables from the dataset and not with database
tables. Recall that the Dataset is a memory copy of the tables and is filled by
the corresponding data adapter. Simple Binding
Simple binding displays one data value from a data table on a control. We can use any
property of a control to perform a simple bind on a data value. For example, we can bind the
Text Box control to the Student table in the University Database and use its Text property to
display the name of the student from the data table. Consider another example. Imagine that the
student table includes a field called “Favorite Color.” We can bind the form control itself to this
field, allowing us to set the form background color property to each student’s favorite color.
Thus, whenever students log in to the system, they see their favorite colors. This idea is widely
used in web applications, including the web email interface of Yahoo.com. Let us now walk
through the following Hands-On section to see how we can use control’s Property Window and
VB .NET code to perform a simple binding operation. Hands On # 17.1 Simple Binding
In this section, we will try our hands at simple binding. We display the student name from the
student table in the Text box control on the form. We will only be able to see the name of the
first student in the student table because this is a simple binding operation.
1. Create a new Project, "SimpleBinding," and connect it to the University Database. Drag and
drop the student table from the Server explorer to the form to quickly create a data adapter.
Name the adaptor as ODAStudent.
2. Generate a dataset, DsStudent. Clear and fill the DsStudent in VB .NET code using our
classic 2 lines of code. Use Form's on load event for this code.
3. Add a Text box control, txtName, and go to its Property Window. Expand the DataBindings
properties tree. We see three options: Advance, Text, and Tag. Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 713 4. For the Text property under DataBinding, select the DsStudent | tblStudent | Name field.
This binds the data for the Name field to the Text property of txtName. Binding with single
values like this is referred to as Simple Binding. (Refer to Figure 17.1)
With simple binding, we can bind any of the properties of a control to a data value from the
database. Figure 17.1 Simple Data Binding First, click the build button that appears when we click the Advance option in the DataBinding
Property. The Advance Data Binding dialog box (shown in Figure 17.2) appears and lists the
properties that can be used with a control. For each property, we can select the field from the
dataset for simple binding.
It is a common practice to provide a navigation control with simple binding that allows the user
to easily navigate from one record to another. Later in this chapter, we will see how we can use
the Data Form Wizard and VB. NET code to add a navigation button to VB .NET forms.
Simple data binding can also be done at run time in VB .NET code. Refer to the code below for
details. Apart from the datasets, we can also bind a control to the values of another control. That
is, a control acts like a data source for another control. For example, we can bind the Text box's
Text property to another Text box’s Text property. Abhijit Pol & Ravindra Ahuja 714 Chapter 17. Database Programming Figure 17.2 Advance Option 'Bind txtName.Text to DsStudent | tblStudent | Name
1. txtName.DataBindings.Add ("Text", DsStudent, "tblStudent.Name")
'Bind txtTest.Text to txtName.txt
2. txtTest.DataBindings.Add ("Text", txtName, "Text") Explanation: Control.DataBindings.Add requires three parameters. The first parameter is
a string indicating which property of a control to bind. The second parameter specifies a data
source, the object that is the source of the data binding. The last parameter is again a string
that indicates the member of a data source who will provide data. For example, in line 1 we are
binding txtName using its Text property (first parameter) to the dataset DsStudent (second
parameter) and specifically to the Name field of the student table (third parameter). In line 2, we
bind the txtTest using its Text property to txtName and specifically to its Text property. In this
chapter, we are most interested in bindings with database tables and thus will only address this
subject. Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 715 Complex Binding
Complex binding displays more than one data value from a data table on a control. We
have already seen an example of complex binding in Chapter 15 when we displayed the entire
student table in a data grid. Controls like Data Grid, List Boxes, Combo Boxes, and
CheckedListBoxes have the ability to display multiple data values at the same time. It is
important to note that not all controls can support complex data binding. For example, controls
like label, text boxes, radio buttons, and command buttons can only display one data value at a
time, and hence can only support simple binding. We now discuss some of the control
properties that help us to perform complex binding.
Data Source: Specific source of data; typically it is the name of a Dataset
Data Member: A member of a data source that holds the data; typically it is the name of a
Data Table. Mainly used for Data Grid control.
Display Member: A field we want the control to display.
Value Member: It is the field we want the control to return as a value of a selection. For
controls like List Box, Combo Box, and Checked List box, we use the Value Member property
instead of Data member (the Value Member property often goes with the Display Member
We have seen how to use Data Source and Data Member properties for Data Grid control in
Chapter 15. We select a dataset for the Data Source and a field of a data table with its source
dataset as Data Member. The Display Member and Value Member properties are used in
slightly different ways. We illustrate the use of these properties in the following hands-on
section. Hands On # 17.2 Complex Binding In this section, we illustrate the complex binding necessary to display multiple data values on a
control. We use the CheckedListBox control and its DataSource, DisplayMember, and
ValueMember properties for data binding.
1. Add ComplexBinding.vb form to the current project. Drag and drop the tblFaculty table from
the server explorer to create the data adapter, ODAFaculty.
2. Generate a new dataset, DsFaculty, and write two lines of VB .NET code to clear and fill the
dataset on form load event.
3. Add a Checked List box control, clstFaculty, from the Windows Form toolbox, and go to its
Abhijit Pol & Ravindra Ahuja 716 Chapter 17. Database Programming 4. Expand the DataBindings properties tree to view DisplayMember and ValueMember
properties. Select DsFaculty for the DataSource property. Click on the DisplayMember
property and select the Faculty | Name from the available list.
5. Click on the ValueMember property and select Faculty | FacultyID. We discuss the
difference between DisplayMember and ValueMember in a separate section later in this
As in the case of simple data binding, we can also do complex data binding in VB .NET code.
The following code box depicts the equivalent VB .NET code for the above Hands-On section.
We now discuss data binding for other VB .NET controls that we have looked at previously. Figure 17.3 Complex Data Binding 'Set Dataset as Data Source
clstFaculty.DataSource = DsFaculty
'Set Faculty Name of tblFaculty as Display Member
clstFaculty.DisplayMember = "tblFaculty.Name"
'Set Faculty ID of tblFaculty as Value Member
clstFaculty.ValueMember = "tblFaculty.FacultyID"
'Example of use of DataSource with Data Member
DataGrid1.DataSource = Dataset21
DataGrid1.DataMember = "tblFaculty"
Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 717 17.3 Binding Various Controls
In this section, we summarize noteworthy properties of various controls for data binding. The
following table lists the control name, data binding type (simple or complex), and important
bound properties. We know that, by using the Property Window | Data Bindings | Advance
option, we can bind any of the control properties to a database field. However, we list only
those properties that we can see by expanding the DataBindings option in the property window.
Control Name Type Bound Properties • Command Buttons Simple Text – Text value associated with control • Radio Buttons • Text Boxes • Check Boxes Simple Checked – value indicating checked sate
CheckState – State of a three-state checkbox
Text – Text value associated with control • Combo Boxes • List Boxes DisplayMember – Value of this field is displayed • Checked List Boxes ValueMember – Value of this field is returned • Data Grid Complex DataSource – Source of Data, e.g. Dataset Complex DataSource – Source of Data, e.g. Dataset
DataMember – Member of a DataSource to display. Table 17.1 Data Binding – Bound Properties for various controls 17.4 Using DisplayMember and ValueMember Properties
We used the DisplayMember and ValueMember properties while discussing the complex
binding type. Recall that DisplayMember and ValueMember properties go hand-in-hand: the
DisplayMember property sets/gets the data table field actually displayed to the user, while the
ValueMember property sets/gets the data table field used for the value of the displayed item.
This distinction allows us to display data in a user-friendly fashion, hide unnecessary details,
and, at the same time, provide developers with an easy way to use member values to query
Abhijit Pol & Ravindra Ahuja 718 Chapter 17. Database Programming data.
In this section, we extend an example of complex binding to clarify this distinction
between DisplayMember and ValueMember.
Figure 17.4 shows the application we are about to develop. We add the Text Box control to
display the additional differences between DisplayMember and ValueMember. As before, we
use the faculty table to first display faculty names (DisplayMember) in the checked list box
control. Then, in the Text Box control, we display the corresponding facultyID (Value Member)
of the faculty names we select.
1. Open the DataBinding form and
add a Text Box control,
txtFacID, to the design window. 2. Use the SelectdValue property
of the checkbox list control to
give the facultyID, which is then
displayed in the text box on
selected index changed event of
the list control. Use the following
piece of code to complete the
Figure 17.4 and
ValueMember Private Sub DataBinding_Load (ByVal sender As System.Object...
txtFactID.Text = "First Select the Faculty" End Sub
Private Sub ChkFacName_SelectedIndexChanged (ByVal sender As...
1. txtFacID.Text = "Faculty ID is: " & ChkFacName.SelectedValue End Sub In the second subroutine, we will set the Text property of a Text box control to the Selected
Value of a checked list box and use the ‘&’ operator to append it to a string. Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 719 17.5 Creating Data Forms using Wizard
Data forms display the data records from the data tables and allow users to navigate through,
update, add new, or remove them from the graphical interface of forms. Data forms, then, form
the foundation of any application.
Visual basic .NET has a Data Form Wizard which guides us, step-by-step, on how to create
data forms based on one or more data tables. The
following Hands-On section illustrates the procedure for
the Students data table in the University database. The
student data form not only displays student personal
information in individual records (organized one-record-ata-time), but also allows us to navigate through all student
records, update or delete existing records, or add new
student records. This form allows users to perform all of
these tasks in a single form, and does not require them to
write a single line of VB .NET code.
Figure 17.5 Opening the Data Form Wizard Hands On # 17.3
1. Data Forms Using Wizard To invoke the Data Form Wizard, choose the Project | Add New item from the main menu,
then select the Data Form Wizard icon in the Template pane and click Ok. The Data form
wizard then adds a new form to the current project. We name this form in the Add New Item
dialog as StudentPortal.vb (See Figure 17.5)
2. Click ‘Next’ in the Welcome
window of the wizard. The
second step of wizard asks us
for the dataset. Select the
"Create a new dataset named"
option and name the new
dataset as DsStudentEntry.
Click “Next” to proceed. We use
the other option for an alreadyexisting dataset. (Figure 17.6)
Figure 17.6 Creating Dataset 3. In the next step we choose the database connection. Use the existing connection to the
University database. Alternately, we can use the Data Link Properties dialog box (opened by
the New Connection button) to create a new connection (Figure 17.7).
Abhijit Pol & Ravindra Ahuja 720 Chapter 17. Database Programming Figure 17.7
Database Connection Figure 17.8 Data Table Abhijit Pol & Ravindra Ahuja Selecting Chapter 17. Database Programming Figure 17.9 721 Selecting the Data Fields 4. Next, we choose data table(s) from the dataset to add to the data form. Select the student
table and click “Next.” Our data form can include more than one table; we use the
parent/child relation to negotiate between these tables. We discuss this topic later in this
chapter (Figure 17.8).
5. We then select the fields we would like to include in the form layout from the table shown in
Figure 17.9. Keep the default selections and click “Next” (see Figure 17.9).
In the final step, we choose the display style of the data form. We have an option of displaying a
single record-at-a-time or all-records-at-once. In the first option, the data form creates an
appropriate control for each of the selected fields and we can then navigate through individual
records. The second option simultaneously displays all of the recodes in different controls.
6. Select the “Single record in individual control” option and accept all other default settings.
Click “Finish” to create the form. The various check box controls in this window allow us to
customize the form for: Cancel All, Add, Delete, and Navigation buttons (Figure 17.10).
7. Save and run the application (Set this form as a start-up form, if required). Click the Load
button on the data form to load the student table. Test the application (Figure 17.11). Abhijit Pol & Ravindra Ahuja 722 Chapter 17. Database Programming Figure 17.10 Selecting the Display Style Figure 17.11 Running Data Form Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 723 17.6 Working with Datasets for Navigation and Data Entry
Data forms that display single records in individual controls (as in Figure 17.11) are often
provided with navigation buttons that allow the user to individually browse through data records.
For example, when a user clicks the > button, the next student record is displayed. If a user
clicks the << button, we navigate to the first record and so on. Besides navigation buttons, the
data form in Figure 17.11 features Update, Add, Delete, and Cancel buttons. All these buttons
are generated by the Data Form Wizard, as we mentioned previously, and we have not written
any code for these features. In this section, we examine the code behind these buttons,
allowing us to customize these controls according to our needs and build them without using the
Data Form Wizard. We begin by introducing the BindingContext class that has been used
to navigate Datasets.
BindingContext class is a collection of all bindings within form control. For example, the form in
Figure 17.11 features various text box controls. Each of these text boxes are bound to a
dataset, and all of these bindings are grouped under one class, BindingContext. We can use
this class to easily access and manipulate all instances of data binding that appear on this form.
Table 17.2 lists a few of the more important properties and methods of this class; we will
illustrate their usage in subsequent subsections.
Name Type Position Property Set/View the current position of cursor in Data Member Item Property Gets a particular Binding Count Property Gets the number of tuples Data Member has. Contains Method Gets where the BindingContext has the specific item AddNew Method Adds new record to a Data Member RemoveAt Method Table 17.2 Description Delete record from Data Member specified by its argument Properties, Methods of BindingContext Class We are now ready to explore the code behind the buttons in the data form. The topics discussed
are: displaying current position, navigating to next and previous records, navigating to first and
last records, adding, deleting, and updating records. The code presented below closely follows
the exact code generated by a data form wizard, but we have simplified some of the names. Abhijit Pol & Ravindra Ahuja 724 Chapter 17. Database Programming Displaying the Current Location
When we click the Load button on the data form, it clears and fills the student dataset through
the select command in the student adaptor (Lines 1-2). The interesting part here is the label
control at the bottom of the form that displays the location of the current record (1 of 500). The
position of the current record is displayed by a call to the subroutine in line 3. The subroutine,
ShowPosition () is used in almost each and every operation, indicating why we need this code in
each separate subroutine.
Private Sub cmdLoad_Click (ByVal sender As System.Object...
3. DsStudent.Clear ()
ODAStudent. Fill (DsStudent)
'Call to subroutine
End Sub Public Sub ShowPosition ()
4. lblPos.Text = _
(Me.BindingContext (DsStudent, "tblStudent").Position + 1).ToString _
& " of " & _
(Me.BindingContext (DsStudent, "tblStudent") .Count). ToString
End Sub Explanation: We explain the ShowPosition subroutine here. While this subroutine consists
primarily of one line, we have split it into four lines in order to ease presentation and
understanding. In line 1, we set the text property of the label control, lblPos, to a concatenated
string that we build in line 2-4. Line 2 gives the string for the position of the current record (say,
1) and line 4 gives the string for total records (say, 500) in the dataset, thus displaying “1 of
Line 2: We use the BindingContext class of the current form, Me.BindingContext, and through
this class, we access the student data table in the DsStudent dataset, that is, .BindingContext
(DsStudent, "tblStudent"). Any data table in a dataset can be accessed using the
BindingContext class by first specifying the dataset set name as a first argument followed by the
data table name as a second argument. Once we have accessed the desired data table through
BindingContext, we can use its position property to provide the location of the cursor in the
dataset. Since the cursor position starts from zero, we increment the current position value by
one and use the .ToString method to convert the output of position property into a string as
required. Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 725 Line 3: This line is very much like line 2 above, except we use the count property of the
BindingContext class to get the total number of records in the student data table. Moving to the Next Record
The desired effect for the next record button is to display the record at the next cursor position.
Recall that properties are used to get and set values. We can use the position property itself to
set the new incremented position of the dataset cursor. We have also handled the case when
the cursor is already at the last record in the data table and the user clicks on the next record
Private Sub btnNavNext_Click (ByVal sender As System.Object...
1. Dim pos As Integer = Me.BindingContext (DsStudent,"tblStudent").Position 2. If pos = Me.BindingContext (DsStudent, "tblStudent") .Count - 1 Then 3.
7. MessageBox.Show ("You are already at the last record")
Me.BindingContext (DsStudent, "tblStudent").Position = pos + 1
End If End Sub Explanation: In line 1, we save the current position value in pos integer variable. Line 2
makes sure the current position, pos, is not the last position in the data table. We use count -1
to determine the position of the last record in the data table. If it is the last position, we don’t
want to navigate; instead, we provide a message to the user that he/she has already reached
the last record (Line 3). In the case of Else, we navigate to the next records by simply setting
the position property to the incremented value, i.e., moving the dataset cursor by one position
(Line 5). We call the ShowPosition subroutine in line 6 to update the position label. It should
read next counter now. Moving to the Previous Record
This operation allows us to display the record prior to that marked by the cursor. We use the
same logic we used to move to the next record. These two operations differ, however, in that
we use a decremented value, and not an incremented value, to move to the previous record.
The code for the previous record button is provided below. We also handled the case when the
cursor is already at the first record in the data table and the user clicks on the previous record
button (Line 2) Abhijit Pol & Ravindra Ahuja 726 Chapter 17. Database Programming Private Sub btnNavPrev_Click (ByVal sender As System.Object...
1. Dim pos As Integer = Me.BindingContext (DsStudent,"tblStudent").Position 2. If pos = 0 Then 3.
7. MessageBox.Show ("You are already at the first record")
Me.BindingContext (DsStudent, "tblStudent").Position = pos - 1
End If End Sub Moving to the First Record
We have to go to the first record of the data table regardless of the current cursor position. We
do this by simply setting the position property to value 0.
Private Sub btnNavFirst_Click (ByVal sender As System.Object...
2. Me.BindingContext (DsStudent,"tblStudent").Position = 0
End Sub Moving to the Last Record
To go to the last record of the data table, we simply set the position property of the last record of
the data table to index, that is, count -1. Private Sub btnNavLast_Click (ByVal sender As System.Object...
3. Me.BindingContext (DsStudent,"tblStudent").Position = _
Me.BindingContext (DsStudent,"tblStudent").Count - 1
End Sub Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 727 Adding Record to a Dataset
The Add button on the student data form adds a new record to the dataset and makes the
cursor point at new records. Note that the Add button does not add a record to the table in the
database, but only to the data table in the dataset. We should use the update button to save
records to database tables. To add a new row in the data table, we use the AddNew method of
the BindingContext class. The method not only adds a new row, but also sets the position
property value to index of this new row and increments count by one. Private Sub btnAdd_Click (ByVal sender As System.Object...
'Saves the current edits
Me.BindingContext (DsStudent,"tblStudent").EndCurrentEdit ()
Me.BindingContext (DsStudent, "tblStudent").AddNew ()
MessageBox.Show (err1.Message) 6. End Try 7. ShowPosition()
End Sub Explanation: In line 2, we use the EndCurrentEdit method of the BindingContext class. This
method saves the current state of the data table and makes it ready for the next operation, in
this case adding a new row. We should always use this method before adding or updating data
tables. In line 3, we call the AddNew method of the BindingContext class to add a new row to
the data table. Note that this new row will be added to a data table given as second argument to
the BindingContext. The two lines of code are then enclosed in the Try-Catch-End Try block to
handle any kind of errors during the operation. Finally, we call ShowPosition routine in line 7 to
reflect changes in the position. Updating Records of a Dataset
The Update button on the student data form updates the database tables. This button takes the
most recent information from existing datasets (data tables) and overwrites changed records in
the database tables. We use the Update method of the BindingContext class to update the
tables. The method EndCurrentEdit saves the changes made to data tables, while update
method saves the data tables to the database tables. Abhijit Pol & Ravindra Ahuja 728 Chapter 17. Database Programming Private Sub btnUpdate_Click (ByVal sender As System.Object...
'Saves the current edits
Me.BindingContext (DsStudent,"tblStudent").EndCurrentEdit ()
Me.BindingContext (DsStudent, "tblStudent").Update ()
End Sub Explanation: We only make a change in line 3 to indicate that we are using the Update
method. Also note that we don’t need a call to ShowPosition in this case as we won’t be
changing cursor position. Deleting Records from a Dataset
The Delete button on the student data form uses the RemoveAt method of the BindingContext
class to remove records from the student table. The RemoveAt method takes an index as a
parameter and removes a record from that position. It also makes the cursor point at the next
record in the data table. The count is decremented by one. Note that this method does not
delete a record from the database table, but only deletes it from the data table in the dataset.
We should use the update button to delete it from the database tables. This button cannot
delete anything if the user clicks this button when there are no records in the data table.
Private Sub btnDelete_Click (ByVal sender As System.Object…
1. If (Me.BindingContext (DsStudent, "tblStudent").Count > 0) Then 2.
4. Me.BindingContext (DsStudent,"tblStudent").RemoveAt _
(Me.BindingContext (DsStudent, "tblStudent").Position)
ShowPosition () 5. End If
End Sub Explanation: In line 1, we check the count of the records in the data tables. If this number is
greater than one, then we delete a record in line 2. The input given to the RemoveAt method is
the current position value which we get in line 3. We call position subroutine in line 4 to reflect
new counts and positions. Note that we don’t have to use the Try-Catch-End Try block or call
Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 729 the EndCurrentEdit method here. Because the delete operation does not make changes in the
data table, we don’t have to save anything; instead, we simply remove a row. Error handling
features are optional in this operation. We have used an error catching feature in line 1 that
allows us to skip the Try-Catch-End Try block. Cancel Dataset Edit
When we edit the data in the text boxes of the student data form, we automatically start the edit
operation in the background. The EndCurrentEdit method makes these changes permanent in
the data tables, but not in the database. What if we make changes and then change our mind?
Can we revert changes made to the data form? The CancelCurrentEdit method of
BindingContext allows us to do this (Line 1). This method simply ignores the changes made to
the form controls and loads a fresh copy of the data from the data tables. Note that we cannot
backup from the addition of new records or deletions. This operation is applicable only for
Private Sub btnCancel_Click (ByVal sender As System.Object...
1. Me.BindingContext (DsStudent, "tblStudent").CancelCurrentEdit () End Sub 17.7 Working with Parameterized SQL queries
We can also bind controls with parameterized SQL queries. Recall from Chapter 8 (Access
parameter queries) that a parameter is a criterion, the value of which is specified at run time.
We can build parameter queries using VB .NET’s query builder, and then use the adaptor to
create datasets to store the result of a parameter query. We give a parameter its value in code
at run time.
The example we use for parameterized SQL queries relies on the faculty and department
tables. By now, we know how to display the faculty listing from tblFaculty in a data grid. We will
call this data display version 1. But suppose we want to create a second version that displays
faculty listings so that they are filtered by department? This organization pattern will allow users
to find faculty listings by first selecting from a list of department names. Parameter queries help
us to create this kind of display.
For the first version, we simply create a data adapter with the following SQL query:
SELECT * FROM tblFaculty However, in the second version we create a parameterized SQL query as: Abhijit Pol & Ravindra Ahuja 730 Chapter 17. Database Programming SELECT * FROM tblFaculty Where DeptID =? A parameter is indicated by a question mark (?), and we will assign its value in VB.NET code at
run time. For example, if we choose to view the faculty list for the ISE department, the
parameter query reads:
SELECT * FROM tblFaculty Where DeptID = "ISE" We can have more than one parameter in a SQL query and also more than one parameter in
one clause. The following query illustrates this point.
SELECT * FROM tblFaculty Where DeptID =? AND Salary BETWEEN ? AND ? The query has three parameters and the ‘Between’ clause has two parameters for it. We now go
through the following hands-on section to complete the application and show how to address
parameters in VB .NET and assign values to them. Figure 17.12 VB .NET form with parameterized SQL query Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming Hands On # 17.4 731 Parameterized SQL query In this hands-on section, we display the faculty information from tblFaculty in the data grid
control. Before it is displayed in the data grid, faculty information is filtered based on the
department selected in the combo box. We achieve this result through a parameter query on
1. Add a new form "SQL Parameters.vb" to the current project. Add a combo box control,
cmbDept, and a data grid control, FacultyGrid, as shown in Figure 17.12.
2. Drag and drop the tblDepartment from the server explorer on the form to quickly create a
data adapter. Name the adaptor as ODADeptList. We should have a live connection with the
University database for this.
3. Generate a dataset for this adapter and name it as DsDeptList.
4. Set the DataSource, DisplayMember, and ValueMember properties for the cmbDept in the
properties window, or code them as shown below (Lines 3-5). Also add the two lines of code
necessary to clear and fill the dataset (Lines 1-2).
Private Sub SQLParameters_Load (ByVal sender As System.Object...
5. 'Binding combo box & dataset in code
cmbDept.DataSource = DsDeptList
cmbDept.DisplayMember = "DsDeptList.Name"
cmbDept.ValueMember = "DsDeptList.DeptID" End Sub 5. Drag and drop the OleDbDataAdapter object from the data tab of the toolbox. This opens
the Data Adapter wizard. Build the parameterized query using the query builder as shown in
Figure 17.13. Note the ‘?’ in the query builder. We place a ‘?’ in the criteria column to
parameterize the DeptID field. We give its value at run time (Line 1 below). Name the
adaptor as ODAFaculty.
6. Generate a dataset for this adapter and name it as DsFaculty.
7. Assign the following code with the on index changed event for the cmbDept. Save, run, and
test the application. Abhijit Pol & Ravindra Ahuja 732 Chapter 17. Database Programming Private Sub cmbDept_SelectedIndexChanged (ByVal sender As System...
'Give parameter its value
1. ODAFaculty.SelectCommand.Parameters("DeptID").Value = cmbDept.SelectedValue 2.
5. 'Binding data grid & dataset in code
FacultyGird.DataSource = DsFaculty
FacultyGird.DataMember = "tblFaculty" End Sub Explanation: We only need to examine the first line of the previous code. We assign the
department identification parameter as the value for line 1. Note that we give this
parameter its value before we fill the dataset. When we fill the dataset, we execute the
select SQL statement in the adaptor. The adaptor should have the correct parameter value so
that it executes this function correctly. Now we return to the code shown in line 1. At first
glance, we might be daunted by this apparently complex formulation. In actuality, this code is
quite simple and logical. To paraphrase, the code basically says that we give a parameter
value for each select SQL statement of an adaptor. Thus, we use
AdaptorName.SelectCommand to gain access to the adaptor’s select command object (Recall
the architecture of the ADO .NET adaptor). We then use .parameter (“DeptID”) to access a
particular parameter of the Select Command object. Finally, we use .value property of the
specified parameter to assign it .SelectedValue of the combo box. 17.8 Using Parent/Child Relationships
In this section, we illustrate how we can take advantage of primary key/foreign key relationships
of database tables to display information on a single VB .NET form. This technique can be used
to build forms with Parent/Child relationships. For example, consider the form shown in Figure
17.14. The form has a combo box and a data grid control. The data grid (Child) displays the list
of faculty members for the department selected in the combo box (Parent). One option for
creating such forms requires that we merge data from these two related tables (tblFaculty and
tblDepartment) in one dataset, say DsParentChild, and then relate them using the VB .NET data
relation object. Once the dataset know that these tables are related to each other, we can bind
them to combo box and data grid, their respective controls, to make them behave like
parent/child relationships. Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 733 Figure 17.13 Query Builder We can always use parameter queries (discussed in the previous section) instead of the data
relation object to create parent/child relationships. We can create a data adaptor with a query
on the faculty table that uses DeptID as a parameter. We then assign parameter value as the
selected item from the combo box at run time. However, using the data relation object to relate
tables within a dataset remains a preferable method when we already have data adaptors on
target tables. The following Hands-on section walks us through the procedure needed to build
Parent/Child relationships through the data relation object. Abhijit Pol & Ravindra Ahuja 734 Chapter 17. Database Programming Figure 17.14 Example of
Relationships Hands On # 17.5 Parent / Child Relationship In this section, we build the parent/child relationships form as shown in Figure 17.14.
1. Add the new form "ParentChild.vb" to the existing project. Add a combo box and a Data grid
control to the form as shown in Figure 17.14.
2. Follow steps 2-5 from the
previous Hands-on section (#
17.2) to configure the combo
box so that it displays the
department listing. However,
make the following changes:
name the data adaptor as
ODADeptListPC and the dataset
3. Drag and drop the tblFaculty
from the server explorer on the
form to quickly create a second
Figure 17.15 Dataset for Parent/Child Form Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 735 4. Rename this adaptor as ODAFacultyPC and add the tblFaculty to the same dataset,
DSParentChild, we created in the previous step (Figure 17.15).
The DsParentChild is a data container that contains both tblDepartment and tblFaculty. Like any
other dataset, it is not aware of the relationship between these two tables. In fact, these two
tables are related to each other with a one-to-many relationship on a common field,
DepartmentID. DepartmentID is a primary key in tblDepartment (Parent Table) and a foreign key
for tblFaculty (Child Table). Now, we explicitly add a relation object that relates these two tables
within the XML schema of the dataset.
5. Find and locate the DsParentChild.xsd file, the XML schema for DsParentChild, in the
solution explorer. Double click this file name to open it in the design window (Refer to Figure
17.16). We now see two tables, tblDepartment and tblFaculty, in this schema and a XML
schema tab in the Toolbox. Figure 17.16 A Relation Data Object in XML Schema 6. Open the Edit relation dialog box by dragging the relation object from the XML tab of the
toolbox to the child table (tblFaculty). See Figure 17.16 for this relation data object in XML
7. The Edit relation dialog box gives default name, tblDepartmenttblFaculty, to the new
relation. Use the Edit relation dialog box (shown in Figure 17.17) to make sure that we have
assigned tblDepartment as the parent table and tblFaculty as the child table. Click Ok to see
the relation object between these two tables in the XML schema.
8. Assign the following code on click event of the command button, index change of the combo
box control, or page load event. Save, run, and test the application. Abhijit Pol & Ravindra Ahuja 736 Chapter 17. Database Programming Private Sub ParentChild_Load (ByVal sender As System.Object...
5. 'Binding data grid & dataset in code
FacultyGird.DataSource = DsParentChild
FacultyGird.DataMember = "tblDepartment" End Sub Figure 17.17 Edit Relation Dialog Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 737 17.9 Handling ADO .NET Objects in Code
In Chapter 15: Database Connectivity, we introduced some ADO .NET objects and showed how
to use them with visual programming. For example, we have seen connection object, data
adaptor, and data set objects and their usage in the design window. In this section, we work
with ADO .NET objects in the code window, and write code for the same processes we followed
for these objects in he design window. For instance, we have already used the Data Link
Properties dialog box to create a database connection in the design view. Now, we will use VB
.NET to create the same connection object. However, this time we will not use any visual aids
or dialog boxes and will write everything in a code window at run time. To give another
example, we can use VB .NET to write code that allows us to create a data adaptor without
running the adaptor wizard in the design view.
Base Class Name Type Description OleDbConn
ection ConnectionString Property Get/View the database connection string Database Property Get the name of the database to open DataSource Property Location and name of the file Provider Property Get the OLE DB provider Open Method Open the DB connection Close Method Closes the connection CreateCommand Method Creates an OleDbCommand object CommandText Property Get/Set the SQL statement CommadType Property Get/Set the type of the CommandText Connection Property Get/Set the connection to use ExecuteReader Method Runs SQL and create data reader ExecuteNonQuery Method Executes Update, Delete, Insert queries ExecuteScalar Method Executes scalar single value queries OleDbCom
mand Abhijit Pol & Ravindra Ahuja 738 Chapter 17. Database Programming Base Class Name Type Description OleDbAda
ct/Update Property Get/Set the SQL for appropriate action
records. Fill Method Add/refresh rows in a dataset to match source Tables Property Gets table in the dataset Clear Method Clears the dataset by removing all rows Copy Method Copy the dataset GetChanges Method Get all changes to dataset in new dataset Merge Method Merges this dataset with another dataset Columns Property Gets columns in the table Rows Property Gets rows in the table Dataset Property Get the dataset to which this table belongs PrimaryKey Property Get the primary key column for table AcceptChanges Method Commits the changes made to data table NewRow Method Create new row Dataset DataTable Table 17.3 ADO .NET Objects: Properties and Methods This kind of programming has many advantages when we are creating and using ADO .NET
objects at run time. In many situations, we will have to create objects based on the current
execution of the program. For example, this technique is frequently used when working with
crystal reports, which use datasets to plot graphs and produce printable reports. For
complicated plots, we often have to process datasets and copy to or create new datasets with a
desired layout. In these situations, we need to dynamically create and operate on a dataset so
that it provides the required plots and reports. We addressed another such situation when we
discussed the use of ASP .NET to handle stored procedures. In this example, we emphasized
the need to create a data adaptor and data set in the code that works with Access Crosstab
Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 739 In this section, we first list the various properties and methods of important ADO .NET objects.
The list provides a quick reference tool for these objects. We then work with all of the listed
objects so that we can create them in VB .NET code and familiarize ourselves with their
methods and properties.
We will attempt to display the student table through a VB .NET form. To do this, we will write all
of the code in the code window and use the properties and methods listed in the above table for
the ADO .NET objects required for this task. We follow our 5 step model [Which model? Implies
we have previously encountered this?] to display database information on a VB .NET form.
First, we create a connection to the University database. Once we have established this
connection, we can create a data adaptor for the SQL statement; this provides us with a dataset
that will hold the query results. In the next step, step 4, we create a data grid control to display
the student list. We must also create a data grid control (explained in Chapter 16) in VB .NET
code. Finally, in step 5 we bind the control to the dataset. Creating Database Connection (Step 1)
We create an OleDbConnection object to connect us to the Access database. The VB .NET
code for creating connection string and object itself is given below.
Private Sub cmdShowStudent_Click (ByVal sender As System.Object... 1.
3. 'Creating Connection Object (Step 1)
Dim ConString As String = "Provider= Microsoft.Jet.OLEDB.4.0"
ConString &= "Source=C:\University\database\University.mdb"
Dim UnivDbConn As OleDbConnection = New OleDbConnection (ConString) Explanation: In line 3, we create a new connection object, UnivDbConn, of the type
OleDbConnection. The object constructor demands a string parameter, commonly known as the
connection string. The connection string contains information on the database provider, the
location of the database, and other necessary information for a database connection. We use
the required information to create one such string, ConString, in lines 1 and 2. Then, we apply
this string to the connection object in line 3. Creating Data adaptor and Command object (Step 2)
We first discussed the architecture of ADO .NET in Chapter 15. Recall that a data adaptor
object consists of one or more command objects. For example, typical adaptors object features
Abhijit Pol & Ravindra Ahuja 740 Chapter 17. Database Programming select, insert, update, and delete commands. Likewise, a command object actually stores the
related SQL statement. After we create a data adaptor in the design window, we can run the
wizard, which has created all of the command objects as well as the encapsulating data adaptor
object. While working in the code window, we must first manually create the required command
object. After that, we create a data adaptor and associate the command object to this newly
created adaptor. 4.
8. 'Creating Command Object
Dim studentCmd As OleDbCommand
studentCmd = New OleDbCommand ("SELECT * FROM tblStudent")
studentCmd.CommandType = CommandType.Text
studentCmd.Connection = UnivDbConn 'Creating Data Adaptor Object (Step 2)
9. Dim OleDbStudent As OleDbDataAdapter = New OleDbDataAdapter()
10. OleDbStudent.SelectCommand = studentCmd
11. OleDbStudent.TableMappings.Add ("Table","tblStudent") Explanation: In line 4, we declare the new command object, studentCmd. The object is
instantiated in line 5 with the desired SQL string. We then set the CommandType property of the
command object in line 6. For SQL queries, set the type to CommandType.Text. We then open
the database connection object, UnivDbConn, using its Open method (Line 7). We must
complete this step before we associate the connection object with the command object (Line 8).
After we complete these actions, the command object, studentCmd, uses the associated
connection to process data.
We are now ready to create the data adaptor object. A new adaptor is created in line 9 of the
code. In line 10, we set the SelectCommand property of the adaptor to the command object
name. This step associates the command object with the adaptor object. Finally, in line 11 we
add table tblStudent to the table mapping of the adapter. For this step, we use the Add method,
as it enables us to keep track of the adaptor tables. Creating Dataset object (Step 3)
Once we have established the data adaptor, we need an object (or dataset) to hold the query
results. Creating a dataset in code performs the same actions as right-clicking on the adaptor
and then selecting the Generate Dataset item from the short-cut menu. Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 741 Private Sub cmdShowStudent_Click (ByVal sender As System.Object... 1.
3. 'Creating Connection Object (Step 1)
Dim ConString As String = "Provider= Microsoft.Jet.OLEDB.4.0"
ConString &= "Source=C:\University\database\University.mdb"
Dim UnivDbConn As OleDbConnection = New OleDbConnection (ConString) 'Creating Command Object
4. Dim studentCmd As OleDbCommand
12. studentCmd = New OleDbCommand ("SELECT * FROM tblStudent")
13. studentCmd.CommandType = CommandType.Text
15. studentCmd.Connection = UnivDbConn
'Creating Data Adaptor Object (Step 2)
16. Dim OleDbStudent As OleDbDataAdapter = New OleDbDataAdapter()
17. OleDbStudent.SelectCommand = studentCmd
18. OleDbStudent.TableMappings.Add ("Table","tblStudent")
'Creating Dataset Object (Step 3)
19. Dim DsStudent As New Dataset ()
20. DsStudent = New Dataset ("tblStudent") 21.
24. 'Creating Data Grid and Binding Dataset (Step 4 & 5)
Dim studentGrid As New DataGrid
studentGrid.SetDataBinding (DsStudent, "tblStudent") End Sub Explanation: In line 19, we create a new dataset and instantiated it in line 20 with the student
table name. Line 20 performs an action equivalent to checking the student table in the Generate
Dataset dialog box. Creating Data Grid Control and Data Binding (Step 4 & 5)
We create a data grid control, studentGrid, in line 21. The control is then used to display the
student records from DsStudent dataset. Lines 22 and 23 perform our usual code clearing and
fill the dataset object through a data adaptor object. In line 24, we bind the grid control to the
dataset using the SetDataBinding method. This method uses the name of the dataset and the Abhijit Pol & Ravindra Ahuja 742 Chapter 17. Database Programming mapped table to automatically set the data source and data member properties of the grid.
Remember that we can always replace line 24 with the following two lines of code.
Data rows, data tables, data columns, looping over the dataset, and the crosstab query stored
procedure are all covered in the ASP section.
studentGrid.DataSource = DsStudent
studentGrid.DataMember = "tblStudent" Final Words
There are few interesting topics related to ADO .NET and Data Binding that we are skipping in
this chapter. We have not talked about handling Data Tables, Data Rows, and Data Column
object’s of ADO .NET. We also skipped important ability of looping over/ cell wise access of
datasets. We cover these topics in ASP .NET section. These techniques are very
straightforward and written in similar manner in both, VB .NET and ASP .NET. Finally, we will
also deal with the Data Command object of Data Adaptor in ASP .NET while working with
Stored Procedure. 17.10 Chapter Summary
• Data binding is a process of binding control objects to data sources by associating data
from data source to a control object. • Connecting database tables to data sources is data connection associating data source
to control object is data binding. • There are different scenarios where we can use the data binding to facilitate application
development; entering values into database, navigating through datasets, data lookups, and
Parent/Child relationship based applications. • We bind the control with the data tables from dataset and not with database tables. Recall
that the Dataset are the memory copy of the tables and is filled by corresponding data
adapter. • Simple binding is displaying one data value from a data table on a control. We can use
any property of a control to do simple bind to a data value. • Complex binding is displaying more than one data values from a data table on a
control. • Complex binding is done by manipulating one or more of these properties: Data Source:
Specifics source of a data. Data Member: A member of a data source which holds the Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 743 data. Display Member: It is a field we want the control to display. Value Member: It is a
field we want the control to return as a value of a selection. Value Member property often
goes with Display Member property
• Data forms display the data records from the data tables and allow user to navigate through,
update, add new, or remove them from graphical interface of forms. • BindingContext class is collection of all bindings with in form control. The Position, Count,
properties and AddNew, RemoveAt, and Update methods are very useful to operate on
datasets. • A parameter is a criterion, value of which is specified at run time. We can build parameter
queries using VB .NET’s query builder (use =?), and then use the adaptor to create datasets
to store the result of parameter query. We give parameter its value in code at run time. • We can create ADO .NET object like Data Connection, Data Adaptor, and Dataset in VB
.NET dynamically at run time. 17.11 Exercises
1. Why do we use data-binding?
disadvantages? What advantages does it offer? Can you think of any 2. What is the difference between "simple" and "complex" data-binding?
support each type of binding? Which controls 3. Some controls have a DisplayMember and a ValueMember. What is the difference between
these two? Give two examples of when one might use them together.
4. What are the most commonly data-bound properties of the following controls: Text Boxes,
Check Boxes, Combo Boxes, List Boxes, and Data Grids?
5. When using the wizard, what is the difference between autoforms created for showing "all
records" and "single record"?
6. What common actions can be performed by using a form's BindingContext?
7. What is the purpose of using a Parent/Child relationship in a form? (Hint: Think about a very
similar feature you used in Access forms.) What kind of intertable relationships are allowed? Abhijit Pol & Ravindra Ahuja 744 Chapter 17. Database Programming 8. There were five common ADO objects discussed in this chapter: Connection, Command,
Data Adapter, Data Set, and Data Table. What is the function of each of these objects?
Draw a diagram to show how they are related (containment, flow, etc.)
9. Why is it important to issue the "Clear()" command to a Data Set before filling it? What
could happen if it wasn't cleared?
10. What additional functionality does a parameterized query provide a program that a nonparameterized query does not?
11. What is the notation is used to tell VB.NET that a criterion will be filled later when creating a
12. When using the Query Wizard to build complex queries, what three warnings are often
encountered? (That is, what actions can the Wizard not figure out how to do?)
13. When creating a Command object in code, why do we set the command type to
"CommandType.Text"? (Hint: What is the universal way to store queries?)
14. What is the importance of the "EndCurrentEdit" command? What would happen if the user
changed the DataSet but didn't issue this command?
15. Reading from and writing to a database are slow operations (why?), so an update command
is often wrapped in a Try/Catch block. What might happen if an update failed and the error
wasn't handled properly? Hands-On Exercises
1. Display Forms: Using the Data Form Wizard, create a form to view information about
patients using the Hospital database.
2. Display Forms: Build a form similar to the one in the previous question, without using the
Wizard. Do not allow users to edit any of the information. (Hint: Use the read-only
attribute.) Don't forget the appropriate navigational buttons.
3. Display Forms: Using the Data Form Wizard, create a form to view information about
customers using the MegaBucks database.
4. Build a form similar to the one in the previous question, without using the Wizard. Do not
allow users to edit any of the information. Abhijit Pol & Ravindra Ahuja Chapter 17. Database Programming 745 Data-Entry Forms
5. Using the Data Form Wizard, create a form which will allow a user to edit existing books and
add books to and delete books from the Books database.
6. Build a form similar to the one in the previous question without using the Wizard. Pay
special attention to the commands for updating, adding, and deleting entries.
7. Using the Data Form Wizard, create a form which will allow a user to edit existing customers
and add customers to and delete customers from the Hotel database.
8. Build a form similar to the one in the previous question without using the Wizard.
9. Using the Airline database, create a form which allows the user to select a start city from a
combo box. Use this selection to populate a second combo box from which the user can
select a destination city. Finally, use both of these to display a list of available flights
traveling between those two cities.
10. Using the Post Office database, create a form which allows the user to enter a ZIP code into
a text box and then displays the information for that post office. Use this information to
display a list showing all of the sending addresses for packages sent from that Post Office.
Finally, allow the user to select an address and show all package information in a grid for
packages sent from that address. (Hint: The list of ZIP codes serviced by a particular
branch is stored as a String.)
11. Using the Movies database, create a form which allows a user to select a state from a
combo box. Use this information to populate a second combo box which will allow the user
to select a city from those available in that state. Display a list of movie theaters in that city
and allow the user to select one. Finally, display the appropriate movie data for that theater.
12. Using the Hotel database, create a form which will allow the user to select a room type from
a combo box. The name should be displayed, but the numerical type should be the value.
Add two Date/Time Pickers to allow the user to select a beginning and ending date and
display all the customers who have that room type reserved between those two dates.
13. Using the Hospital database, create a parent/child form which allows the user to select a
doctor's name from a combo box and then displays all of the patients assigned to that
14. Using the Library database, create a parent/child form which allows the user to select a
member ID and displays all of the books checked out by that member.
Abhijit Pol & Ravindra Ahuja 746 Chapter 17. Database Programming 15. Using the Furniture database, create a parent/child form which allows the user to select a
product from a combo box (with the name displayed and the number as the value) and
displays a grid showing every shipment which has contained that product. Abhijit Pol & Ravindra Ahuja ...
View Full Document
This note was uploaded on 10/20/2008 for the course IST 331 taught by Professor Sikder during the Spring '08 term at Cleveland State.
- Spring '08