Beginning ASP.NET 2

Reading Data

In this Try It Out you modify the previous Try It Out. Instead of presenting a DetailsView control with all of the games stacked up, you will present just one game. That one game will be determined by a game identifier (FixtureID) in the querystring.

1.Make a copy of Fixtures-Stacked as follows. In Solution Explorer select FixturesStacked.aspx. Right-click and select Copy. Right-click the root of the site and select Paste.

Look in the list of files for Copy of Fixtures-Stacked.aspx. Rename the copy to FixturesOne.aspx.

2.Select the DetailsView control and dis-enable paging.

3.Select the SqlDataSource control, open its smart task panel, and click Configure Data Source.

4.Click Next to keep the current connection, and go to configuration of the Select Statement. There is no need to change the table source or fields. Click the WHERE button to detour to the Add WHERE Clause box (see Figure 7-37).

Figure 7-37

5.Select FixtureID from the drop-down list of columns.

6.Accept an operator of equals and set the source to querystring. In the parameters properties panel set the QueryString field to FixID (type in the name) and a default of 1. Click Add and then click OK. Click Next and test. Notice that before the test runs, VWD gives you a chance to input by hand the value that will be in the querystring. Try the default of 1, and then also test various numbers between. (See Figure 7-38.) Click Finish and read on.

249

Chapter 7

Figure 7-38

7.VWD registers that there was a change to the data source feeding the DetailsView control. It will give you a chance to rebuild the data-bound control to be synchronous with the new settings of the data source control. Agree to the update. You can also perform such a synchronization at any time by clicking the Refresh Schema choice in the smart task panel. Your file source code will end up as follows.

<%@ Page Language=”VB” MasterPageFile=”~/site.master” AutoEventWireup=”false” CodeFile=”Fixtures-One.aspx.vb” Inherits=”Fixtures_One” title=”Untitled Page” %> <asp:Content ID=”Content1” ContentPlaceHolderID=”mainContent” Runat=”Server”>

<asp:DetailsView ID=”DetailsView1” runat=”server” AutoGenerateRows=”False” DataSourceID=”SqlDataSource2”

Height=”50px” Width=”447px”> <PagerSettings Mode=”NumericFirstLast”

FirstPageText=”First Game of the Season” LastPageText=”Last Game of the Season”/>

</asp:DetailsView>

<asp:SqlDataSource ID=”SqlDataSource2” runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString2 %>” SelectCommand=”SELECT [FixtureDate], [FixtureType], [GoalsFor],

[GoalsAgainst], [Notes], [Opponents] FROM [Fixtures] WHERE ([FixtureID] =

@FixtureID)”>

<SelectParameters>

250

Reading Data

<asp:QueryStringParameter DefaultValue=”1” Name=”FixtureID” QueryStringField=”FixID”

Type=”Int32” /> </SelectParameters>

</asp:SqlDataSource>

</asp:Content>

8.Now you can test the page by running it. By default game number 1 will be displayed. In your browser’s address bar try changing the URL from ... /WroxUnited/Fixtures-One.aspx by adding a querystring to get ... /WroxUnited/Fixtures-One.aspx?FixID=3 and click the GO button of your browser (as shown in Figure 7-39).

Figure 7-39

How It Works

You did a rather elaborate procedure to make the copy. You might be tempted to just open the original and Save As with the new name. But with a Save As you do not get a copy of the code-behind page nor do you get a change in the .aspx page’s reference to the code-behind file. Copying, pasting, and changing the name in Solution Explorer instructs VWD to take care of the details.

First, you disabled paging because with only one record in the stack there is no need for navigation. Then you revised your SelectCommand. Even though you already have your SqlDataSource control configured, you can restart the wizard and modify the control. This eliminates the possibility of introducing typos by editing in Source View.

In prior exercises you did not use the WHERE button and so VWD built for you a SelectCommand that returned all of the records. In the Add WHERE Clause step you selected a column (which means field) that was going to be scanned for matches. You wanted your matches to be exact, so you selected the equals operator. Then you selected a source of QueryString. This means that the value of which fixture to display (the parameter) can be found in the querystring. Under parameters properties you set the name of the value in the querystring to be FixID. Don’t forget to click the Add button in the Add Where Clause window — it is easy to overlook.

251

Chapter 7

VWD keeps an eye on the data source control for each data-bound control. When you finish your revision to the data source control VWD warns you that you better synch up any data-bound controls using that source. You can also do that synch explicitly with a click on the Refresh Schema button of the smart task panel.

All of these changes have left the page ready to accept an ID of a game in the querystring. The value must have the name of FixID. Your data source will take that value (or 1 if there is no FixID) and use that in the SelectCommand to find the records with a matching FixtureID. Only that game will be shown. When you test the page you only have to remember the syntax to attach a value to a URL. After the page name type a question mark to begin parameters. Then type the parameter name, an equals sign, and the parameter’s value.

Multiple Data Controls Working Together

The previous section described a crude way to use parameters by typing them into the URL. The enlightened way is to use one data-bound control to create the parameters for another data-bound control. These are termed ControlParameters. This section demonstrates two very common cases. In the first case, a DropDownList control determines which record or records to display in a GridView control. In the second case, a selection in a GridView control determines which record for which the page should show more fields in a DetailsView control. These are called master-child scenarios. A selection in a master data-bound control establishes the value for a WHERE clause in the data source control of the child data-bound control. In almost all cases there must be separate data source controls for the master and child data-bound controls.

In the master control you only need one special property. If the master control is a ListBox or DropDownList control, the AutoPostBack property must be set to True so that when a selection is made from the master, ASP.NET 2.0 will invoke a refresh of the page and the resulting change in the record will be shown in the child control. Keep in mind that ListBox and DropDownList controls have two field parameters. The first is the field to display (DataTextField property), and the second is the field that is used in page code (DataValueField property). This allows a last name, for example, to be displayed to the user in the list box but the more useful value of, for example, PlayerID to be used in SQL statements. If the master control is a GridView you can open its smart task panel and Enable

Selection to get VWD to build a new column with select buttons for each record. When clicked, ASP.NET will react to the selection of that record, as you will see.

In the child control you must set a parameter into the WHERE clause of the child control’s data source control. In the previous section you set the parameter to a field of the querystring using the Add WHERE Clause dialog of the Configure Data Source Control wizard. You can use the wizard again, but this time when you get to the Add WHERE Clause panel you set your column equal to a value from a control (not a querystring). The wizard presents a list of controls from which you can select. Within the selecting control you determine which value to use, normally the SelectionValue.

In the following Try It Out you practice two combinations of controls. In the first you use a GridView selection to show additional fields in a DetailsView control. In the second you use a DropDownList control to show only certain records in a GridView.

252

Reading Data

1.Now you will build your final version of Fixtures.aspx. Move your existing Fixtures.aspx to a folder that you create and name OldPages. Because the steps are so quick and easy (and it is a good review) you will start from scratch. Add a new item to the site named Fixtures.aspx using the Master page with VB code in a separate file. Switch to Design View.

2.Open Database Explorer, navigate to WroxUnited.mdf Tables Fixtures. Select only the fields for FixtureID, FixtureDate, and Opponents (using Ctrl+Click), and drag them to the page. VWD will crank for a few seconds and then give you a GridView with its smart task panel open. Enable Selection as shown in Figure 7-40. Note that VWD has already added the column of Select buttons.

Figure 7-40

3.Go to the bottom of the content holder and drag a DetailsView control from the Toolbox. Choose a data source of New and configure the database with an ID of SqlDataSource2 and continue using the connection string named WroxUnited that you use in all of this chapter. Specify to get data from the Fixtures table and select all the fields.

4.Click the WHERE button and select the column of FixtureID. Set the operator to equals and select Control from the Source drop-down (see Figure 7-41).

5.Now in the parameter properties, set the ControlID to GridView1 and the default value to 1. Don’t forget to click the Add button (not done yet in Figure 7-41). Click OK, Next, and then the Test Query button (a value of 1 is fine), and finally, click the Finish button.

6.Drag the DetailsView control to be wider. Save and view it in your browser (see Figure 7-42). Select various matches from the GridView and see their notes below the GridView in the

DetailsView control.

7.Now you will improve the Gallery page by only showing pages from one match. In Solution Explorer, make a copy of Gallery-All.aspx to the root. Change the copy’s name to

Gallery.aspx.

253

Chapter 7

Figure 7-41

Figure 7-42

254

Reading Data

8.Open Gallery.aspx and add a few line breaks and then a DropDownList box to the top of the page. Configure its data source to a new source of type Database and name it SqlDataSource3. Use the WroxUnited and from the Fixtures table select the FixtureID, FixtureDate, and

Opponents.

Click ORDER BY and sort on FixtureDate ascending. Click OK, Next, and then test the query and finish. This brings you back to the Choose a Data Source window, and you will display (for now) the Opponents and set the value to FixtureID. Click OK. While the DropDownList control is still selected, turn on Enable AutoPostback from the smart task panel.

9.Open the smart task panel for the DataSourceControl1 (used by the DataList) and start its Configure Data Source wizard. Step through to the WHERE clause. Set the Column of FixtureID to equal a control with the ControlID of DropDownList1 and a default value of 1. Don’t forget to click the Add button and click OK. Finish the procedure with clicking Next, and then

test query (a parameter value of 1 is fine) and finish. Refresh the data-bound control when prompted. You will see the result shown in Figure 7-43 in your browser (albeit all the photos to date have been from game 1) and code in Source View.

Figure 7-43

<%@ Page Language=”VB” MasterPageFile=”~/site.master” AutoEventWireup=”false” CodeFile=”FixFinal.aspx.vb” Inherits=”FixFinal” title=”Untitled Page” %>

<asp:Content ID=”Content1” ContentPlaceHolderID=”mainContent” Runat=”Server”> <asp:DropDownList ID=”DropDownList1” runat=”server”

AutoPostBack=”True”

DataSourceID=”SqlDataSource3”

DataTextField=”Opponents”

DataValueField=”FixtureID”>

</asp:DropDownList>

255

Chapter 7

<asp:SqlDataSource ID=”SqlDataSource3” runat=”server” ConnectionString=”<%$ ConnectionStrings:WroxUnited %>”

SelectCommand=”SELECT [FixtureID], [FixtureDate], [Opponents] FROM [Fixtures] ORDER BY [FixtureDate]”>

</asp:SqlDataSource>

<asp:GridView ID=”GridView1” runat=”server” AutoGenerateColumns=”False” DataSourceID=”SqlDataSource1”

EmptyDataText=”There are no data records to display.”> </asp:GridView>

<asp:SqlDataSource ID=”SqlDataSource1” runat=”server” ConnectionString=”<%$ ConnectionStrings:WroxUnitedConnectionString1 %>” DeleteCommand=”DELETE FROM [Fixtures] WHERE [FixtureID] = @FixtureID” InsertCommand=”INSERT INTO [Fixtures] ([FixtureDate], [Opponents]) VALUES (@FixtureDate, @Opponents)”

ProviderName=”<%$ ConnectionStrings:WroxUnited.ProviderName %>” SelectCommand=”SELECT [FixtureID], [FixtureDate], [Opponents] FROM

[Fixtures] WHERE ([FixtureID] = @FixtureID)”

UpdateCommand=”UPDATE [Fixtures] SET [FixtureDate] = @FixtureDate, [Opponents] = @Opponents WHERE [FixtureID] = @FixtureID”>

<DeleteParameters>

<asp:Parameter Name=”FixtureID” Type=”Int32” /> </DeleteParameters>

<UpdateParameters>

<asp:Parameter Name=”FixtureDate” Type=”DateTime” /> <asp:Parameter Name=”Opponents” Type=”String” /> <asp:Parameter Name=”FixtureID” Type=”Int32” />

</UpdateParameters>

<SelectParameters>

<asp:ControlParameter ControlID=”DropDownList1” DefaultValue=”1” Name=”FixtureID”

PropertyName=”SelectedValue” Type=”Int32” /> </SelectParameters>

<InsertParameters>

<asp:Parameter Name=”FixtureDate” Type=”DateTime” /> <asp:Parameter Name=”Opponents” Type=”String” />

</InsertParameters>

</asp:SqlDataSource>

<asp:DetailsView ID=”DetailsView1” runat=”server” AutoGenerateRows=”False”

DataKeyNames=”FixtureID”

DataSourceID=”SqlDataSource2” Height=”50px” Width=”492px”>

<Fields>

<asp:BoundField DataField=”FixtureID” HeaderText=”FixtureID” InsertVisible=”False”

ReadOnly=”True” SortExpression=”FixtureID” /> <asp:BoundField DataField=”FixtureDate” HeaderText=”FixtureDate”

SortExpression=”FixtureDate” />

<asp:BoundField DataField=”FixtureType” HeaderText=”FixtureType” SortExpression=”FixtureType” />

<asp:BoundField DataField=”GoalsFor” HeaderText=”GoalsFor” SortExpression=”GoalsFor” />

256

Reading Data

<asp:BoundField DataField=”GoalsAgainst” HeaderText=”GoalsAgainst” SortExpression=”GoalsAgainst” />

<asp:BoundField DataField=”Notes” HeaderText=”Notes” SortExpression=”Notes” />

<asp:BoundField DataField=”Opponents” HeaderText=”Opponents” SortExpression=”Opponents” />

</Fields>

</asp:DetailsView>

<asp:SqlDataSource ID=”SqlDataSource2” runat=”server” ConnectionString=”<%$ ConnectionStrings:WroxUnited %>”

SelectCommand=”SELECT * FROM [Fixtures] WHERE ([FixtureID] = @FixtureID)”> <SelectParameters>

<asp:ControlParameter ControlID=”GridView1” DefaultValue=”1” Name=”FixtureID” PropertyName=”SelectedValue”

Type=”Int32” /> </SelectParameters>

</asp:SqlDataSource>

</asp:Content>

10.You’ll finish up with a trick to resolve the problem of the user not knowing which match to select in the DropDownList when the same opponent is listed several times. Change the

SelectCommand of the DropDownList data source control (SqlDataSource2) and the DataTextField of the DropDownList as follows:

<asp:DropDownList ID=”DropDownList1” runat=”server” DataSourceID=”SqlDataSource2” DataTextField=”FixtureCombo” DataValueField=”FixtureID”

AutoPostBack=true > </asp:DropDownList>

<asp:SqlDataSource ID=”SqlDataSource2” runat=”server” ConnectionString=”<%$ ConnectionStrings:WroxUnited %>”

SelectCommand=”SELECT [FixtureID], (Opponents + ‘ - ‘ + CONVERT(varchar,

FixtureDate, 6)) As FixtureCombo FROM [Fixtures] ORDER BY [FixtureDate]”>

How It Works

Both of these pages are logical extensions of what you did in earlier exercises. As before, you changed the SelectCommand (using VWD’s Configure Data Source wizard) so that there is a WHERE clause that gets its value from a parameter. In the previous Try It Out the parameter came in with the querystring, but on these two pages the parameter comes from another control.

In the first case the GridView filled the parameter with the ID of the record for which the Select button was clicked. That value was then used by the data source control in its WHERE clause. An important (and frequently overlooked) step is to set the DropDownList control to AutoPostBack.

In the final step you wanted to display more than one field in the DropDownList control. You can achieve that goal by using a SQL statement that creates a new field by concatenating values from existing fields. The following code brings together the opponent’s name, the literal text of spaces, a hyphen, and a converted format of the game date to create a field called FixtureCombo:

SelectCommand=”SELECT [FixtureID], (Opponents + ‘ - ‘ + CONVERT(varchar,

FixtureDate, 6)) As FixtureCombo FROM [Fixtures] ORDER BY [FixtureDate]”>

257

Chapter 7

Note how a plus sign (+) is used between each section of the string of text. The opponent value comes from the database in a form ready to use. The FixtureDate also comes from the database. But it comes in a date format that is not what you want. So you convert it to a type called varchar, which is a string of characters. You then put together the two parts with a dividing section of text that consists of a space, a hyphen, and another space.

Working with XML Data

XML has solidified as the standard format for information on the Web. XML files (or streams of data) are self-describing (each value has a label) and hierarchical in that every item of data has a parent except the topmost tag. Typically an XML file will have its highest-level tags that merely state the kind of information held, like Players. Then there would be a set of tags called “player” for each member of the team. Within there would be further, lower-level tags describing that player. Each item is called a node, for example, one player. Additionally, XML is case-sensitive. XML files can be created, read, and revised using ASP.NET 2.0. In the following Try It Out, you create an XML file using VWD.

The league has issued a few changes to Wrox United’s 2007 schedule:

1.Right-click the root of your site and add a new item of the type XML file and name it Schedule2007. Normally you would store an XML file in App_Data, but for this exercise that creates some rights problems when viewing in your browser.

2.At the beginning of the file VWD will automatically add the tag for the XML version. Go to the end of the file and type in <Games>. Notice that VWD automatically adds the closing tag for <Games>. These tags are the highest level and describe the entire contents of the XML file.

3.Now you will create a node for the first game. Inside the <Games> tags, create a <game> tag (note that this is singular). Now add a Number=”1” inside the opening <Game> tag as per the code listing after the next step.

4.Inside the <Games> tag, add three pairs of child tags with values for the team names and the date as follows:

<Games>

<Game Number=”1”> <Date>2007/10/21</Date> <Home>WroxUnited</Home> <Visitor>Clatterham</Visitor>

</Game>

</Games>

258