Adding Data Bound Controls

You have the same data binding capabilities on an added control on a document that you have for controls on a Windows Form. In Visual Studio, you can use the Data Sources window to add data-bound controls to your document. First you must create a new data source.

1. On the Data menu, click Add New Data Source. This opens the Data Sources Configuration Wizard.

2. In the Data Sources Configuration Wizard, select Database, and then click Next.

3. Navigate to the Northwind database that comes with Microsoft Access, and then click Next. Depending on where you installed Office, you should be able to find this database in Program Files\Microsoft Office\OFFICE11\Samples.

4. Leave the default settings for saving the connection string, and click Next.

5. Expand the Tables node, select the Products table and the Suppliers table, and then click Finish.

The Data Sources window becomes visible in Visual Studio and contains two tables: Products and Suppliers. When you expand one of these tables, such as the Products table, you will see all the fields available for it. By default, most of these fields appear as NamedRange controls, but if you click the drop-down box to the right of the field name, you can select from a number of other control types, as shown in Figure 8.22.

File Edit View Project Build Debug Data Tools Window Community Help

File Edit View Project Build Debug Data Tools Window Community Help

Data Sources » ï X

Sheetl.vb* >1

iKceiWorkbi

1dis*

ookl.Kls Start Page - X

1 Solu in Explorer * fl X |

□ lib ^ L*

: J U y G

a m a, j *> - % > • î. n

l

BIBlällRA

□ -G

orthwindDataSet H Products

Arial ^ 10 T B I =

I

3 ExcelWorkbookl. xls lJ] Sheet l.vb

ft

|g Prodi

^ 5upp fgCals

JCtID jlJ'.JI I_ lierlD

gorylD

1 ai T r~ I

A

B

c

D

E

F

G

H-

||g| NorthwindDataSet. xsd

^ app.config

1

2

ft j

;up

11

3

NamedRange ListObject

he

-2SÏR_1

1 c

g ®

ComboBox

Label

LinkLabel

ListBox

[None]

Properties ~ J? X

Sheet 1 Microsoft, Of fiee, Tools, Excel, V -

15

0

- ÎÂTira : 1

Figure 8.22. Selecting the type of control to be added to a document

Figure 8.22. Selecting the type of control to be added to a document

In the next example, you'll add data-bound controls to the worksheet and see how you can dynamically populate a ListObject and a Chart. First, you add data-bound controls to the worksheet.

1. Expand the Suppliers table in the Data Sources window, and change the CompanyName field to a combo box by clicking the drop-down box next to the CompanyName field and clicking ComboBox.

2. Drag the CompanyName field to cell A2 on Sheet1.

3. Click the drop-down next to the ContactName field, and select Label.

4. Drag the ContactName field to the worksheet below the CompanyName combo box.

5. At the bottom of the Suppliers field, you'll see the Products table. Select that table, and drag it to cell A7. A ListObject is created that contains a column for each field in the Products table.

6. Right-click the ProductID column, point to Delete, and click Column.

7. Using the method described in step 6, delete the following columns of the ListObject: SupplierID, CategoryID, and Discontinued.

You can resize each of the columns in the ListObject so that the headings are visible; to do so, drag the right column boundary to the right, or doubleclick the right column boundary for each column you want to resize. Note that the worksheet displays a component tray that contains components necessary for data binding. For example, there is a NorthwindDataSet, along with a table adapter and binding source, for each of the tables (Products and Suppliers). These components were added to the project for you when you dragged fields from the table to the worksheet. Your worksheet should resemble the one in Figure 8.23.

E NorthwindData5et BjJ Products j ProductID q ProductName q SupplierlD q CategorylD q QuantityPerUnit q UnitPrice 3 UnitsIn5tock 3 UnitsOnOrder q ReorderLevel Discontinued E _| Suppliers

SupplierlD T? CompanyName A ContactName

Sheet l.vb* ExcelWerkbeekl.xls*] 5tart Page

Arial >10 - B I U K ■ ■ î % ÎW- - » - ± - 3

B15 T fa

Arial >10 - B I U K ■ ■ î % ÎW- - » - ± - 3

B15 T fa

A

E!

C

D

F

G

H

1

2

Compsr^Name;

H

3

4

ContadName

5

6

7

ProductNai

QuantityP

UnitPrice

1111 itsl n Sr<

HiiifrC'nO

ReorderL

-•■I

8

3

10

11

Figure 8.23. Data-bound controls on a worksheet

Figure 8.23. Data-bound controls on a worksheet

Next, you can create a chart based on the data that will populate the worksheet when the solution is run. This chart will change dynamically as the ListObject data changes. The contents of the ListObject are bound to the Northwind database. Because we created a ListObject from the Products table, which was a child of the Suppliers table, a relationship exists between these two tables. This means that changes made in the CompanyName combo box will cause the data in the ListObject to display the related data for the selected company. Let's create the chart and run the solution to see how this works.

1. Select all the cells in the ListObject, including the header row.

2. On the Insert menu, click Chart. The Chart Wizard will appear.

3. In step 1 of the Chart Wizard, select Column in the Chart Type list box. Select the first chart under Chart Sub-type, and then click Next.

4. In step 2 of the Chart Wizard, select the Series tab. In the Series list box, select Product Name and then click Remove. Then remove QuantityPerUnit and UnitPrice. The Series list box should contain only UnitsInStock, UnitsOnOrder, and ReorderLevel, as shown in Figure 8.24.

Figure 8.24. Setting the series data for a chart

5. Type the following text in the Category (x) Axis Labels text box: =Sheetl!$A$8:$A$13

Instead of typing this text, you can select cells A8 through A13 in the ProductName column, as shown in Figure 8.24. You could include additional cells if you know that the data for that column will expand beyond row 13. In our case, each company in the Northwind database has fewer than eight products.

7. Click Finish to complete the wizard and create the chart.

8. Drag the chart to an area below the ListObject, and then resize the chart to the same width as the ListObject.

9. Press F5 to run the code.

10. Click the CompanyName combo box to select a company.

Notice that the data in the ListObject and Chart changes according to the company that is selected in the CompanyName combo box. Figure 8.25 shows the worksheet with controls bound to data in the Northwind database.

Figure 8.25. Selecting a company in a data-bound combo box

Being able to add controls directly to the worksheet or document gives you one more option for presenting a UI to the end user. However, it sometimes might not make sense to put a Windows Form control on a document. You have to assess the purpose of the control and the best way to display the UI. If you want to give users a way to select an item, as we did in our Excel example with the CompanyName combo box, you easily could add this control to the actions pane instead. However, another scenario for using a combo box might be to insert the selected item into the worksheet in place of the control. Choose the type of UI that best meets your needs.

If you print this worksheet, the Windows Forms controls also print. As you saw earlier in this chapter (see Figure 8.20), you can set the control's properties so that the control does not print. Of course, this means that the data in the control will not print. In Word, you cannot set inline controls to be hidden when you print because these controls are inline shapes. If you change the format of the control to make it a floating control, it essentially converts the Inline Shape object to a Shape object, which can then be hidden. But remember that the control might not reflow correctly in your document if the text changes and the control is not inline with the text.

One other way around this limitation is to add the control to the document programmatically to assist users in filling out information and then remove the control after the information is received. You'll see an example of this later in the chapter. But first, let's look at how you can add custom controls to a document.

0 0

Post a comment