Chapter 17 - Chapter Overview 17.1 Introduction

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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 these controls. 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 property). 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 Property Window. 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 chapter. 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 form) Figure 17.4 and properties DisplayMember ValueMember Private Sub DataBinding_Load (ByVal sender As System.Object... 1. 2. 3. DsFaculty.Clear() ODAFaculty.Fill(DsFaculty) 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 Creating 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... 1. 2. 3. DsStudent.Clear () ODAStudent. Fill (DsStudent) ShowPosition() 'Call to subroutine End Sub Public Sub ShowPosition () 1. 2. 3. 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 500.” 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 button. 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. 4. 5. 6. 7. MessageBox.Show ("You are already at the last record") Else Me.BindingContext (DsStudent, "tblStudent").Position = pos + 1 ShowPosition() 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. 4. 5. 6. 7. MessageBox.Show ("You are already at the first record") Else Me.BindingContext (DsStudent, "tblStudent").Position = pos - 1 ShowPosition() 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... 1. 2. Me.BindingContext (DsStudent,"tblStudent").Position = 0 ShowPosition() 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... 1. 2. 3. Me.BindingContext (DsStudent,"tblStudent").Position = _ Me.BindingContext (DsStudent,"tblStudent").Count - 1 ShowPosition() 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... 1. 2. 3. 4. 5. Try 'Saves the current edits Me.BindingContext (DsStudent,"tblStudent").EndCurrentEdit () Me.BindingContext (DsStudent, "tblStudent").AddNew () Catch err1 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... 1. 2. 3. 4. 5. 6. Try 'Saves the current edits Me.BindingContext (DsStudent,"tblStudent").EndCurrentEdit () Me.BindingContext (DsStudent, "tblStudent").Update () Catch err1 MessageBox.Show (err1.Message) End Try 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. 3. 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 updates. 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 tblFaculty. 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... 1. 2. DsDeptList.Clear() ODADeptList.Fill(DsDeptList) 3. 4. 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. 3. DsFaculty.Clear() ODAFaculty.Fill(DsFaculty) 4. 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 Parent/Child 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 as DsParentChild. 3. Drag and drop the tblFaculty from the server explorer on the form to quickly create a second data adapter. 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 schema. 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... 1. 2. 3. DsParentChild.Clear() ODADeptListPC.Fill(DsParentChild) ODAFacultyPC.Fill(DsParentChild) 4. 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 pter Delete/Insert/Sele 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 queries. 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. 2. 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. 5. 6. 7. 8. 'Creating Command Object Dim studentCmd As OleDbCommand studentCmd = New OleDbCommand ("SELECT * FROM tblStudent") studentCmd.CommandType = CommandType.Text UnivDbConn.Open() 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. 2. 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 14. UnivDbConn.Open() 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. 22. 23. 24. 'Creating Data Grid and Binding Dataset (Step 4 & 5) Dim studentGrid As New DataGrid DsStudent.Clear() OleDbStudent.Fill(DsStudent) 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 Review 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 parameterized query? 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 Display Forms 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. Parameterized Queries 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. Parent/Child Forms 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 doctor. 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.

Ask a homework question - tutors are online