IS437, In Class Project 6: Separate Forms for Data Entry and Data Display. Displaying Data from Multiple Joined Tables:

Showing one “Parent” tied to the “Child” Value

Showing multiple “Children” tied to the “Parent” Value

  1. Modify the user entry/message display form to alert the user before final submission of the information to the database by asking them to explicitly confirm that the values that are entered are correct. We will use messageBox.Show method with a YesNo Button option)
  1. Add three more buttons to the entry form. One for browsing by users, one for browsing by age ranges and one for browsing by clothing items.
  1. Create a new form for browsing by users. It will have the following elements (controls):
  2. One combo box (bound to the last name column): lastNameComboBox
  3. Six labels, bound to the following values:
  4. First name
  5. Last name
  6. Gender
  7. Status
  8. AgeRange
  9. Clothing Item
  10. Exit button to close this form (and return to the main menu)
  1. For the new form we will actually have to create a new connection object and new adapter(s). In this form we will display information from three different tables: Items in labels (i) through (iv) are from the Users table, item (v) is from the Age table, and item (vi) is from the Clothing Items table. Thereare several different ways to do this. We will implement the way in which only ONE adapter and ONE data set is used (this method is NOT recommended by the official .NET developer web site, but it is much simpler than the standard method and will be sufficient for this class). Again, we can drag all the tables onto the form and automatically one connection object and three data adapters will be created. You can erase two extra adapters and rename the remaining one as browseUsersDataAdapter. Also, rename your connection object as browseUsersConnection.
  1. We will configure the browseUsersDataAdapter by right clicking on it and clicking on Configure Data Adapter option that will start the configuration Wizard. Keep clicking “Next” button (three times) until you get to the screen “Generate SQL Statements”. Here we will generate the SQl statement that will return all six needed columns whose values we need to display in this form. There are several different ways to do this, and I will demonstrate some of them that do not require the knowledge of SQL.
  1. After the browseUsersDataAdapter is configured we will use it to generate the new dataset that we will call browseUsersDataSet. This is done by right clicking on the browseUsersDataAdapter and using the generate data set option. In this method our data set can only be tied to one table even though it contains values from multiple tables.
  1. Once the browseUsersDataSet is created it can be used for data binding to our controls in the same way as done in previous lab. Verify that the dataset will be filled properly by using the fill data set command on the browseUsersDataAdapter.
  1. Make sure that data set is explicitly filled with necessary data every time the form loads, by adding the line in the code (the only line of code in this form) under the form Load event

Dataadaptername.fill(datasetname)

  1. We will then repeat this whole exercise for the form that will be used to show relevant data (all users first and last names, for example) for each selected age range. This time the challenge is in the fact that there may be more than one user associated with any selected age range, whereby in previous case, only one age range and clothing item was associated with any user.
  1. This form will have only three controls (elements)
  2. Combo box for the list of age ranges: ageRangeComboBox
  3. List box (selectedUsersListBox) that will display in first name and last names of each user form that age range in separate rows
  4. AgeRange
  5. Clothing Item
  6. Exit button to close this form (and return to the main menu)
  1. This time we will need (when using this particular approach) two data adapters, one tied to a new data set and another tied to the original ageDataSet form the display Message form. I will demonstrate the reasons for this in the class.
  1. Also, this time several lines of code will be written for the combo box event that will explicitly bind data in two controls that are tied to two different data sets.