Two Data Form Wizard Examples
from Chapter 6 of
Programming Microsoft Visual Basic .NET
for Microsoft Access Databases
Add an Updateable Data Source to a DataGrid Control
Some applications work better with a DataGrid control, which lets users see multiple rows at once and navigate rapidly with a vertical scroll bar. For this reason, the Data Form Wizard enables the creation of forms with DataGrid controls. In fact, the Data Form Wizard creates forms only with a DataGrid design when you use it to develop a Web solution, but developers creating solutions within a Windows application project can choose between DataGrid and text box designs. Both designs can enable the same range of functions, but the two designs enable the manipulation of those functions via different techniques.
Creating DataForm2
The steps presented next show how to create a Form object containing a DataGrid control with the Data Form Wizard. To make this sample application comparable to the preceding one, it uses the same local dataset, DsOrders. Because the second application reuses the dataset created in the first application, the steps for creating the DataGrid sample illustrate how to reuse a dataset. Open the Data Form Wizard by choosing Project, Add New Item. Then, highlight Data Form Wizard in the Templates collection, and click Open to start the wizard’s process for creating a Form object named DataForm2. Click Next on the Welcome screen to advance to the first wizard screen, from which you can make a choice. Then, follow these steps:
|
1. Instead of attempting to create a new dataset, indicate that you want to reuse an existing dataset by accepting the selection to use the DataFormWizSamples.DsOrders dataset. Then click Next. 2. Choose FillDataSet from DataFormWizSamples.DataForm1 in the top drop-down box as the method to fill the dataset, click the Include An Update check box, and choose UpdateDataSource from DataFormWizSamples.DataForm1 as the method to update the dataset. Then click Next. 3. Designate the three items to show on your form by clearing the checked status of all check boxes, except those for Freight, OrderDate, and OrderID. Then click Next. 4. Accept the default selection to display all records in a grid, and click Finish. |
The Design View of DataForm2
DataForm2 contains just four controls (see Figure 6-14), but it supports the same range of data access and manipulation functions offered by DataForm1, which contained many more controls. A Load button populates (or repopulates) the DataGrid control that appears below it. When a user opens DataForm2 to browse and manipulate data, the Orders table from the DsOrders dataset appears with a vertical scroll bar for moving forward and backward through the data. This feature enables users to quickly browse through the rows in the Orders table. The objDsOrders variable shown in the tray below the form in Figure 6-14 points at the DsOrders dataset. You can confirm this for yourself by selecting the object in the tray and viewing its DatasetName property (which is DsOrders).
Figure 6-14. Design view of a form created with the Data Form Wizard that contains a DataGrid control for browsing and manipulating values
Users can edit the value in any DataGrid cell. These edits automatically persist in the local dataset for the current session, but they do not automatically propagate to the Access database file that you use as a data source for the local dataset. To pass changes from the local dataset to an Access database (or any other kind of database), click the Update button. The Cancel All button in this application works like the button with the same label in the preceding sample application. Clicking this button before clicking Update undoes any changes made since the last time the Update button was clicked.
You can also click the Update button to remove rows from a database based on deleted rows in a local dataset or to add rows to a database based on rows inserted into a local dataset. This might seem confusing at first because no Add and Delete buttons exist on DataForm2. You can add a new row to a table in a local dataset by scrolling to the last row, which has an asterisk (*) in its row selector. Then, enter new values in the row, and move off the row. To mark a row for deletion, select the row. Then, press the Delete key on the keyboard.
DataForm2 in Operation
Making an edit to a DataGrid cell is straightforward. Double-click a cell, and then edit the value. Figure 6-15 shows DataForm2 after double-clicking the DataGrid control’s top left cell. An edit is in process for the first row, as evidenced by the symbol in the selector for that row; the edit is to change the default value of 32.38 to 32.3. Clicking Update will commit the edit in the local dataset and propagate the revised Freight column value to the Orders table of the Northwind database. Clicking the Cancel All button instead of the Update button clears the modification and restores the original value, 32.38, in place of the edited value, 32.3. Just as with the preceding form sample application containing text boxes, clicking the Load button after users make their edits updates the local dataset to reflect those changes. If you want to persist any uncommitted changes to the database, you must click Update before clicking Load.
Note: Just as with a form containing text boxes, clicking the Update button on a form containing a DataGrid control commits all changes not yet propagated to the database source from a local dataset—not just a pending change for a single row.
Figure 6-15. Committing an edit from the DataGrid control is as easy as clicking the Update button.
Moving to the last row in a DataGrid control enables you to add a new row to a local dataset. Figure 6-16 shows the Freight column value open for the addition of a new row. Just type the new values into the columns for the row. If you are using an autonumber column value, as with the OrderID column in Figure 6-16, the form shows the next autonumber as 1 plus the last value for the autonumber column in the DataGrid. Because Access can discard autonumbers for uncommitted rows or deleted rows, it is possible for the appropriate autonumber to be different than the DataGrid control shows. When you click Update, Access automatically corrects an invalid autonumber and replaces it with a valid one. The next time a user repopulates the DataGrid control by clicking the Load button, the control reflects the valid autonumber set by the Jet database engine.
Figure 6-16. When adding a new row through a DataGrid control, the .NET Framework assigns an autonumber for the row based on the last autonumber column value in the DataGrid control.
Main/sub Forms
You can use the Data Form Wizard to create classic main/sub forms in Visual Basic .NET similar to those that Access makes easy to create. A main/sub form is a single form that contains two nested forms—a main form and a sub form. Each form has its own data source. The sub form is synchronized with the main form to show the subset of rows from its data source that match the currently selected row in the main form. For the process to work, your application needs to know about the relationship between the data sources for the main and sub forms.
To implement a main/sub form with the Data Form Wizard, you need to specify at least two data sources for the form. One data source is for the main form, and the second is for the sub form. You can choose any table or view (row-returning query without parameters) in an Access database as the data source for a form. In addition, you must specify in the Data Form Wizard screens the relationship between the two data sources.
Creating DataForm3
The sample demonstrating the process for creating main/sub forms uses the Orders and Order Details tables from the Northwind database as the sources for the main form and sub form, respectively. The two tables serving as data sources relate to one another via their OrderID columns values. Start to create this main/sub form by invoking the Project, Add New Item command. If you have been following along, the default DataForm filename will be DataForm3.vb. Accept this name by clicking Open. Then, click Next to advance to the first Data Form Wizard screen from which you can make a selection. From there, follow these instructions:
|
1. Select the radio button for creating a new dataset, enter the name DsOrdersOrderDetails in the text box below the button, and then click Next. 2. Use the drop-down box to select a previously created connection for the Northwind database, or click the New Connection button to create a new connection to the Northwind database. Then click Next. 3. From the Available Items box, click the Orders table followed by the > button before clicking the Order Details table followed by the > button so that the Orders and Order Details tables appear in the Selected Item(s) box. Then click Next. 4. On the Create A Relationship Between Tables dialog box, specify a relationship name, parent and child table names, and keys for linking the parent and child tables as shown in Figure 6-17 before clicking the > button to save your specification for the relationship between the two data sources. Then click Next. 5. Designate three items to show on your master form by clearing the checked status of all check boxes, except for Freight, OrderDate, and OrderID, and clear the OrderID check box for the detail table to show all other columns from its data source. Then click Next. 6. Accept the default selection to display all rows in a grid for both the main and sub forms, and click Finish. |
Figure 6-17. When you designate two separate data sources in the Data Form Wizard, you can designate a relationship between them to create a main/sub form.
The Design View of DataForm3
Figure 6-18 shows the main/sub form in Design view created by following the instructions given a moment ago. You can see all of the DataGrid control for the main form with column values of Freight, OrderDate, and OrderID. Below the main form, you can see the top of the DataGrid control for the sub form. Notice that the sub form does not show a column for OrderID.
Figure 6-18. Design view of a main/sub form created with the Data Form Wizard
The display columns in the grids do not tell the whole story about what’s available as a data source behind these two forms. Both forms have all the columns available from their respective data sources. This facilitates the coding for data manipulation tasks. Happily, all these details are managed by the Data Form Wizard. For those who want to dig deeper, you can examine the Data Adapter Configuration Wizard screens for the OleDbDataAdapter1 and OleDbDataAdapter2 object variables in the tray below the form. You can start the wizard by right-clicking either object and choosing Configure Data Adapter. Do not edit any settings; looking how the wizard makes settings can give you a starting point when you begin creating your own custom solutions without the wizard.
DataForm3 in Operation
After opening DataForm3, you can populate the DataGrid controls for the main and sub forms by clicking Load. Initially, the main form selects the first row in the Orders table with an OrderID column value of 10248. The sub form shows the three rows in the Order Details table that match the selected row in the Orders table from the main form. After selecting the second row in the main form (for OrderID 10249), the sub form changes to reflect rows for OrderID 10249. The left and right windows in Figure 6-19 portray these two views of data that characterize the operation of a main/sub form.
Figure 6-19. A main/sub form when it initially opens (on left) and after the selection of a row in the main form (on right)
Because the data sources for the main and sub forms are both tables, you can perform data manipulation tasks such as inserts, updates, and deletes in the same way that you would for a form with a single DataGrid control connected to a single table. You can also perform data manipulation for a query serving as a data source as long as the Jet engine permits updates to the query. For example, you cannot edit rows in a query that aggregates rows because the column values appearing in a grid do not physically exist; the column values in an aggregate query are computed just for the query.