1.
Select New File . . . from the File menu, or right-click on the root entry in the Solution Explorer window and select Add New Item . . . to open the Add New Item dialog. Select Web Form, and change the filename to TestMenu3.aspx. Be sure to tick the “Place code in separate file” check- box near the bottom of the dialog, and then click Add (see Figure 5-3).Figure 5-3: Clicking the checkbox, and then clicking Add
2.
VWD creates two new files, and adds them to the project. If you look in the Solution Explorer window (see Figure 5-4), you will see these two files. TestMenu3.aspxcontains the interface code, and automatically opens in the main editing window. TestMenu3.aspx.vbcontains the executable code for the page, which you will examine later in this example.3.
Switch to Design view, and drag a SqlDataSourcecontrol from the Toolbox onto the page(TestMenu3.aspx) that is open in the Editing window. Select Configure Data Source in
the SqlDataSource Tasks pane to start the Configure Data Source Wizard. Select the entry
PPQ_DataConnectionString1for the connection string in the first page of the wizard,
and click Next. In the Configure the Select Statement page, select “Specify columns from a table or view,” and select the MenuItemstable. In the Columns: list, select the MenuItemID,
MenuItemType, and ItemNamecolumns (see Figure 5-5).
Figure 5-5: Selecting columns in the Columns: list
4.
Click Next. Test the query if you wish, and click Finish to close the Configure Data Source Wizard. Then drag a GridViewcontrol from the Toolbox onto the page. In the GridView Tasks pane, selectSqlDataSource1in the Choose Data Source: list, and apply an Auto Format of your choice. Now,
5.
This opens the Add Field dialog. Choose a TemplateFieldin the list at the top of the dialog, and enter Size and Price as the Header Text (see Figure 5-7).Figure 5-7: Add Field dialog
6.
This adds a new TemplateFieldas the last column of the GridViewcontrol. Click OK to close the Add Field dialog, and click the Edit Templates link at the bottom of the GridView Tasks pane to switch this pane into Template Editing Mode. You will see the new column named Sizeand Priceand the templates that are available for it. Select the ItemTemplate, as shown in
Figure 5-8.
Figure 5-8: Template Editing Mode pane
7.
Whatever you place in this template will appear in this column of every row in the GridView control at run time. Therefore, you need to add controls into this template that will fetch the rows from the SizeAndPricetable that match the MenuItemIDvalue in the current row of theGridView. Drag a SqlDataSourcefrom the Toolbox into the template-editing area, and select
Configure Data Source in the SqlDataSource Tasks pane to start the Configure Data Source Wizard. Select the entry PPQ_DataConnectionString1for the connection string in the first page of the wizard, and click Next. In the Configure the Select Statement page, select “Specify columns from a table or view,” but this time select the SizeAndPricetable. In the Columns: list, select the ItemSizeand ItemPricecolumns, as shown in Figure 5-9. Then click the WHERE . . . button to open the Add WHERE Clause dialog.
Figure 5-9: Selecting columns from the Columns: list
You must add a WHEREclause (a criteria) to the SQL statement so that only the rows matching the current menu item are returned. The data source controls allow you to add parameters to the SQL statement, and these can even be populated with the appropriate value dynamically at run time. For example, you can use a ControlParameterautomatically populated with the value from, say, a
DropDownListso that the rows returned by the query match the currently selected value in this list.
However, things are not that simple with a GridViewcontrol, as you will see in the next step.
8.
The Add WHERE Clause dialog (shown in Figure 5-10) allows you to select a Column in the source data that the query generates, an Operator (such as equals), and a Source for the value to compare to the specified column contents. The Source specifies the type of parameter to create and there are several types available. You can take the value from another control on the page,a value in the query string, a posted form value, a value from a cookie, or a value from the user’s session or profile. In the example you are building now, it would be tempting to add a parameter that takes its value from the current row in the GridViewcontrol, as shown in Figure 5-10.
Figure 5-10: Add WHERE Clause dialog
9.
Unfortunately, this will not produce the required result. If you look at the Value: that the wizard proposes, you can see that it will come from the SelectedValueproperty of the GridView control. The problem is that, when simply displaying data, the rows in the GridViewcontrol are not actually “selected.” Only one row can be in “selected mode” in a GridView, as you may recall from the example in Chapter 1 that demonstrates selection and editing in a GridView control. Instead, you must add a “normal” parameter (one that is not automatically popu- lated) to the SQL statement, and then set the value of this parameter using code at run time. Confusingly, to create a parameter of this type, you select None in the Source: list of the Add WHERE Clause dialog, as shown in Figure 5-11. You should also leave the Value: (in the “Parameter properties” section of the dialog) empty. After setting these values in the Add WHERE Clause dialog, click the Add button. You will see them appear in the SQL Expression and Value section at the bottom of the list (you can add multiple criteria in this dialog). Then click OK to close the dialog and return to the Configure Data Source Wizard.Figure 5-11: Selecting None in the Source: list
10.
Back in the Configure Data Source Wizard, you can now specify the sorting of the rows by adding an ORDER BYclause to the SQL statement. Click the ORDER BY . . . button to open the Add ORDER BY Clause dialog. Select the ItemPricecolumn, and check that the order is set toAscending. You can see the final SQL statement at the bottom of this dialog, with the WHERE
Notice that the WHEREclause specifies that the fkMenuItemIDcolumn (in the SizeAndPricetable)
must be equal to a parameter named @fkMenuItemID. This is the default naming convention. When using SQL Server, parameter names should always start with the @character.
11.
Click OK to close the Add ORDER BY Clause dialog, and click Next in the main Configure Data Source Wizard window. Now you can test your query. Click the Test Query button, and — because there is a parameter in the SQL statement — the Parameter Values Editor dialog opens showing the name and type of each parameter that is required by the query. Enter a value between 1and 7, and click OK (see Figure 5-13).Figure 5-13: Parameter Values Editor dialog
12.
You will see that the result contains only the rows that have the value you entered in theirfkMenuItemIDcolumn (see Figure 5-14). After viewing the results, click Finish to complete the
Figure 5-14: SELECT statement results
13.
Back in the main VWD editing window, you can now add the list control that you will bind tothe SqlDataSourcecontrol you just configured. Only a line break is required between each
item in the list, and so the obvious choice is the lightweight and simple Repeatercontrol. Drag
a Repeatercontrol from the Toolbox and drop it into the ItemTemplate editing area. In the
Repeater Tasks pane, select the new SqlDataSource(named SqlDataSource2) in the Choose Data Source list (see Figure 5-15).
14.
The Repeatercontrol depends on templates to provide all of its content and yet is one of the few controls that does not provide a design-time interface for editing its templates. As you can see in Figure 5-15, you must switch to Source view and manually enter the templates and content. Find the <asp:Repeater>control declaration, and insert the following ItemTemplate declaration (as shown highlighted in Figure 5-16):<ItemTemplate>
<%#Eval(“ItemSize”)%>: <%#Eval(“ItemPrice”, “$ {0:F2}”)%><br /> </ItemTemplate>
The Repeatercontrol generates a copy of the ItemTemplate contents at run time for each row
in the source data rowset. The content you just added consists of two Evalstatements, which display the values from the specified columns of the current row, and some literal text (the “:” and a space) plus an HTML line break. Notice that the second Evalstatement also specifies a format string (like that you saw in previous examples), so the value will appear with a dollar sign and two decimal places.
Looking at the source of the page (in Figure 5-16), you can see the declaration of the second
SqlDataSourcecontrol (with ID=”SqlDataSource2”) within the ItemTemplateof the GridView
control. Nested inside the SqlDataSourceis a SelectParameterssection that contains the parameter you added to the SQL statement in the Configure Data Source Wizard.
15.
If you run the page now, you will see nothing in the Size and Pricecolumn. This is becausethe SqlDataSourcein the ItemTemplate of the GridViewcontrol cannot match any rows in the
SizeAndPricetable — the value of the @fkMenuItemIDparameter is empty. To set the value,
you need to write some code that will execute as each row of the GridViewis bound to its data source (a row from the MenuItemstable). Recalling how you learned in the previous chapter that ASP.NET controls raise events, you will not be surprised to discover an event that provides just the opportunity you need. The GridViewcontrol raises a RowDataBoundevent after it has collected the data from the data source for each row, and is ready to create the output. Go to the Solution Explorer window and open the code-behind page named Testmenu3.aspx.vb. In the two drop-down lists at the top of the main VWD editing window, select the GridViewcontrol
(GridView1), and the RowDataBoundevent, as shown in Figure 5-17.
Figure 5-17: Selecting the RowDataBoundeven
16.
An outline of the event handler routine appears in the page (see Figure 5-17), to which you can then add your own code (see Figure 5-18). The code you require is:If e.Row.RowType = DataControlRowType.DataRow Then
Figure 5-18: Outline of event handler routine
The code used here looks complicated, but has a simple task to accomplish. It first checks what type of row this is by looking at the RowTypeproperty of the row — referenced through the arguments passed to the event handler (it might be a data row, a header row, or a footer row). If it is a data row, it will contain the SqlDataSourceand Repeatercontrols. The code uses a method of the Rowobject named
FindControlto get a reference to the SqlDataSourcecontrol in this row. Then it extracts the value
of the MenuItemIDcolumn for the current row from the DataKeyscollection (VWD automatically sets the DataKeyNamesproperty of the GridViewcontrol to the primary key column(s) in the source rowset, which populates the DataKeyscollection). The value of the parameter named fkMenuItemID
in the SelectParameterscollection of the SqlDataSourcecontrol in this row is then set to the
MenuItemIDvalue for this row, causing it to extract the matching rows from the SizeAndPricetable.
17.
Click Save to save the two files, and then run your page to see the results. As you can see in Figure 5-19, the Repeatercontrol now generates a list of values from the SizesAndPrices table for each row in the MenuItemstable.Figure 5-19: Results of Repeatercontrol generating a list of values