Using the Data Form Wizard

The Data Form Wizard is a graphical tool for binding form controls to data sources. You can create data-bound controls for Windows and Web applications with the Data Form Wizard. This section examines the use of the wizard with Windows forms. If you are an Access developer learning Visual Basic .NET and ADO.NET, one of the great advantages of using the Data Form Wizard is that you can build form solutions for your Access databases without writing any code. However, the code behind the automatically generated forms is all standard code. Therefore, you can view the code as a learning aid or as a starting point for more customized solutions.

Menu for the Section

You will frequently build switchboard forms toward the end of an application development project. This is because you need to have the forms to which a switchboard directs traffic available for the code behind the switchboard form to compile properly. In spite of this application development requirement, switchboard forms are useful in tutorial presentations at the beginning of a section, such as this presentation. A switchboard form can succinctly summarize the topics a section covers.

Figure 6-11 shows the switchboard form for this section. The form exists as Form1 in the DataFormWizSamples project. The code behind this form works identically to the code behind the switchboard form in the preceding section, except that this form s code references four samples instead of five. The most significant point of the samples referenced from this switchboard is that the Data Form Wizard generated all their forms. I did not have to code any of them. However, these forms do typical database chores in a database form. The first sample enables data access and manipulation via text boxes. The second sample enables data manipulation via a DataGrid control. The third sample presents two DataGrid controls in a main/sub form configuration. The fourth sample takes this main/sub form to a higher level by showing aggregated data in the sub form.

Figure 6-11: The switchboard form for the discussion of the Data Form Wizard

Data Access and Manipulation with TextBox Controls

The first Data Form Wizard demonstration creates a Windows form with three text boxes bound to the Freight, OrderDate, and OrderID columns in the Orders table of the Northwind database. By now, you should understand that a dataset contains a local cache of data that gets filled by a data adapter. As mentioned (but not demonstrated yet), data adapters are two-way pipes between an Access database file and a local dataset. This means that a user can update the local cache of data in the DataSet object and then invoke a method for the data adapter that copies changes from the local data cache to the database file. Chapters 7 through 9, which cover ADO.NET, explore conceptual, architectural, and programming aspects of this and related tasks . However, for the purpose of this sample (and this chapter), let s see how simple the Data Form Wizard can make this task.

To invoke the Data Form Wizard, you will need to start from a type of project that supports the wizard. One of these types is the Windows application project that we have repeatedly explored in this chapter and earlier ones. Starting the Data Form Wizard from a Windows application project creates a Windows form. Successive forms created with the Data Form Wizard have the default names DataForm1 , DataForm2 , and so on. This sample is located in the DataFormWizSamples project. It has a Form1 , formatted to look like Figure 6-11, and four data forms that were created with the Data Form Wizard. If you do not use a switchboard menu in your project, you need another mechanism for transferring control to the form created by the Data Form Wizard. (For example, you could make the form created by the wizard the project s startup object.)

Creating DataForm1

You invoke the Data Form Wizard by choosing Project, Add New Item, and selecting Data Form Wizard from the Templates. The wizard suggests a name for the form file, such as DataForm1.vb, which you can override. Click Open to launch the wizard. Because you are working with a wizard, screens will appear successively, offering a range of options for performing a task, which in this case is creating a data-bound form. These are the steps that were used to create the forms in this sample:

1. Click Next on the wizard s Welcome screen.

2. Specify the creation of a new dataset by entering DsOrders in the text box for naming a new dataset, and then click Next.

3. 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.

4. Highlight Orders from the Tables collection of Available Items, and click the > button so that the table name migrates to the Selected Items box. Then click Next.

5. Designate the three items to show on your form by clearing the checked status of all check boxes, except for Freight, OrderDate, and OrderID. Then click Next.

6. Specify a form with text boxes by selecting the radio button with the label Single Record In Individual Controls, and then click Finish.

The Design View of DataForm1

Following the preceding steps can add an object named DataForm1 to your project. As mentioned, this object is really a Windows form that the wizard created for you based on your inputs to the wizard screens. Figure 6-12 shows the Design view for DataForm1 .

Figure 6-12: Design view of a form created with the Data Form Wizard that contains text boxes for browsing and manipulating values

This section provides an overview of the design features of DataForm1 , and it describes specific features associated with each control. The basic purpose of DataForm1 is to offer a Windows form that controls data access and manipulation to the Orders table in the Northwind database. Clicking the Load button populates the DsOrders dataset, which the objDsOrders variable points at. After downloading a snapshot of the Orders table from the Northwind database to the Orders table in the DsOrders dataset, users can browse through the rows of the local Orders table with the <<, <, >, and >> buttons. The label between the < and > buttons displays the ordinal position of the current row among all rows in the data source for the form (namely, the Orders table in the DsOrders dataset).

In addition to browsing through rows, users can perform updates, inserts , and deletes. Making an update is as simple as editing a column value appearing in a text box and navigating off a row. Clicking the Cancel button below the > and >> buttons restores the current row in the local dataset to the value it had before any changes were made to its column values. Clicking the Delete button marks the current row showing in the text boxes for deletion. Clicking the Add button creates a form with all fields blank except for OrderID, which shows the next unused autonumber value from the Access data source to the data adapter for the local dataset.

All the changes to the local dataset persist for the current session. However, if you close the form without first clicking the Update button, any changes you made to the local dataset do not propagate to the database serving as the source for the local dataset. Therefore, the next time the form opens, it will be as though no updates, inserts, or deletes ever happened . Furthermore, clicking the Cancel All button below the Update button restores the Orders table in the DsOrders dataset to the state it was in immediately after you clicked the Load button. The only way to persist the changes to the local dataset to the database is to click the Update button. After updating an Access database file from the form, you can capture any changes entered by other users since your last load by clicking the Load button. This repopulates the local dataset showing in the form with the most recent version of the Access database object ”namely, a table or a query ”underlying the local dataset.

Note  

Rows marked for deletion in the local dataset are not physically removed from the local dataset until after a user clicks the Update button. However, rows marked for deletion do not display when a user browses the local dataset.

DataForm1 in Action

The original Freight column values for rows with OrderID values of 10248, 10249, and 10250 are 32.38, 11.61, and 65.83, respectively. You need to understand that a disconnected dataset maintains a local data cache that is separate and distinct from the database version of the same data. Therefore, you can have independent updates in both versions. When performing any kind of data manipulation with a Form object created by the Data Form Wizard, you should always include the primary key column(s) in the local dataset. Thus, when you attempt to propagate a change to a local dataset back to its database source, your application can identify the specific rows to edit or delete.

Figure 6-13 shows a progression of screens from the Access Datasheet view for the Orders table and the Windows form managed by the DataForm1 object. I moved the Freight column values so that they appear to the immediate right of the OrderID column values in the Datasheet view. The top window shows the original OrderID and Freight values in the Datasheet view. The center window shows DataForm1 after an edit of the Freight column value for OrderID 10248 from 32.38 to 32.3. After clicking the Update button on DataForm1 , the Datasheet view in Access looks like the bottom window; you might have to refresh or reopen the Orders table Datasheet view to actually see the change.

Figure 6-13: Making a change in a DataForm object can update the corresponding record in a table in an Access database after you click the form s Update button.

Changes can also go the other way ”namely, from the Datasheet view of a table in Access to the DataForm . However, clicking the Update button does not transfer data from Access to the DataForm . A user needs to click the Load button to see updates to the database in DataForm1 . This repopulates the dataset behind the text boxes on the form. For a data source the size of the Orders table, which has 830 rows, this repopulation process occurs very quickly. Figure 6-14 tracks DataForm1 before and after the modification of the Freight value for OrderID 10250 from 65.83 to 65.80. The top left window shows DataForm1 before the change in the Datasheet view. The top right window shows the Freight value (65.80) after the change. The bottom window displays DataForm1 after a click to the Load button. Because this button refreshes and moves the current row to the first row in the Orders table for the local dataset, I need to move the row pointer to 3 of 830 with the > navigation button.

Figure 6-14: Making a change in an Access Datasheet view propagates to a DataForm object after a user clicks the Load button.

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-15), 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-15 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-15: 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 Action

Making an edit to a DataGrid cell is straightforward. Double-click a cell, and then edit the value. Figure 6-16 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.

Figure 6-16: Committing an edit from the DataGrid control is as easy as clicking the Update button.

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.

Moving to the last row in a DataGrid control enables you to add a new row to a local dataset. Figure 6-17 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-17, 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-17: 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-18 before clicking the > button to save your specification for the relationship between the two data sources. Then click Next.

   
   Figure 6-18: 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.

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, and click Finish.

The Design View of DataForm3

Figure 6-19 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-19: 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 at how the wizard makes settings can give you a starting point when you begin creating your own custom solutions without the wizard.

DataForm3 in Action

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-20 portray these two views of data that characterize the operation of a main/sub form.

Figure 6-20: 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.

Main Table with Aggregated Sub Form

The discussion of the preceding sample s output revealed that you cannot perform updates to column values on a sub form when it contains aggregate values. Figure 6-20 shows another outcome that Access database developers might find interesting: the ProductID column values displayed as a number. Often, Access automatically looks up and displays the matching name for a column value, such as ProductName from the Products table, instead of presenting the less meaningful column value as a ProductID number. Visual Basic .NET follows the tradition of SQL Server development: it is common for the developer to program the lookup. After all, this merely involves linking a table with columns for both the numeric codes (such as ProductID) and their names (such as ProductName) as column values.

The final Data Form Wizard sample application generates a main/sub form with a table, Categories, as the source for the main form and an aggregated query as a data source for the sub form. The aggregated query, ExtendedPricesSummedbyProduct, computes extended price by product within CategoryID. This custom query returns three columns: CategoryID, ProductName, and the sum of extended prices. The custom query is an aggregation of the Order Details Extended built-in query for the Northwind database, which computes extended price for individual line items within Orders. The SQL statement for the ExtendedPricesSummedbyProduct query joins the aggregation with the Products table by ProductID to add CategoryID column values to the result set from the query. The following SQL expression defines the custom query:

 SELECT Products.CategoryID, Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS SumOfExtendedPrice FROM Products INNER JOIN [Order Details Extended] ON Products.ProductID = [Order Details Extended].ProductID GROUP BY Products.CategoryID, Products.ProductName 

The CategoryID column values in the result set from the query enable joining the aggregated query with the Categories table. Through a relationship based on this join, the main/sub form can show category names in the rows of its grid on its main form. The sub form shows the names of products in its grid along with the summed extended prices for each product within a category. By changing the selected category in the main form, you cause the sub form to display different sets of products. Figure 6-21 shows the main/sub form in action ”immediately after opening and then selecting the last row in the main form.

Figure 6-21: You can customize the look of a DataForm by dropping and resizing controls.

Some custom editing went into the form in Figure 6-21, but the steps for creating the main/sub form in the figure generally mirror those of the preceding main/sub form sample. Of course, you need to select the Categories table and the ExtendedPricesSummedbyProduct query as the data sources for the main and sub forms. The DataFormWizSamples project available for this chapter named the dataset with both data sources as DsCategoriesExtendedPricesbyProduct . On the last Data Form Wizard screen, clear the Cancel All check box to remove the button by this name from the form. In Design view, you can remove the Update button because the form is strictly for reporting editing, not enabling it. The form instances in Figure 6-21 show additional editing to resize the form and grids for easier viewing. You can accomplish this resizing by selecting the control or form and dragging the selected item to a new size.