IS 460
SqlDataSource and FormView Lab
Ekedahl

This lab requires that you use the database named Gaming.mdf. A copy can be found on the COBA K: drive. In lab, you will perform the following exercises:

  • Create a SqlDataSource control instance and configure it to select values. Create a second SqlDataSource control instance and configure it to select, insert, update, and delete values
  • Bind a ListBox to a SqlDataSource control instance
  • Create the SelectedIndexChanged event handler for a ListBox
  • Read the bound ListBoxvalues
  • Create a FormView control and configure it to display a single record
  • Configure the FormView control to perform inserts
  • Configure the FormView control to perform deletes
  • Configure the FormView control to perform updates
  • Handle selected FormView events

Exercise 1 – Creating and Configuring a SqlDataSource

In this exercise, you will create a SqlDataSource control and configure it to select all of the rows from the table named tblProperty in the database named Gaming.mdf.

  1. Create a new Empty Web site from the template as you have been doing before.
  2. In that Empty Web site, create a folder named App_Data and copy the database from K:\IS460 to this folder. To create the folder, right click on the project (IS460FormViewLab). In the context menu, click Add, New Folder. Rename the folder to App_Data. These are the same tasks that you performed in the last class. The structure should appear as follows:

  3. Add a new blank Web form to the page. Web Site / Add New Item / Web Form. Name the form Default.aspx.

  4. Using the Toolbox, create a SqlDataSource control instance on the form. You can create the control instance with either the Design or Source view activated. The SqlDataSource control appears on the Data tab of the Toolbox.
  5. Next, you will configure the control instance using the supplied Wizard. Again, you could also configure the data source by hand. Make sure that the SqlDataSource is selected in Design view. The following steps will not work from Source view. Click the arrow as shown in the following figure:

  6. The following dialog box appears allowing you to create a new connection or configure an existing one.

    Depending on exactly how you set up the database in the previous steps, you might see the connection to the database named Gaming.mdf, in the above list box. If so, select it. If not, click the New Connection button to establish the connection to the database. Click the Browse button to locate the database. It is in the project / folder you specified previously.

  7. In the next dialog box, you will be asked to save the connection string to the application’s configuration file (web.config). Name the connection string gamingConnectionString. In a moment, you will look at the web.config file to see the connection string that was configured by the Wizard.

  8. Click Next to display the following dialog box:

  9. Make sure to check all of the fields as shown in the above figure. Since this connection string will be used to select all of the records from the table named tblProperty, you will not need a WHERE clause. Since the property will only be used for selection, you need not use the Advanced tab to define the INSERT, UPDATE, and DELETE statements.
  10. In the above dialog box, click the ORDER BY… button to display the following dialog:

  11. The above dialog box allows you to create up to three ORDER BY clauses. As you can see, rows can be sorted in ascending or descending order. Select fldPropertyName in the first Sort by box as shown in the above figure:
  12. Click OK to close the above dialog box, Click Next.
  13. In the Configure Data Source dialog box, click Next to display the following dialog box:

  14. Test the query if you like. You should see a couple of data rows.
  15. Click Finish. The Wizard created the SqlDataSource and configures it as shown in the following code segment.

asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:gamingConnectionString%>"

SelectCommand="SELECT [fldProperty], [fldPropertyName], [fldAddress], [fldCity],

[fldState], [fldZipCode]

FROM [tblProperty] ORDER BY fldPropertyName">

</asp:SqlDataSource

  • The SqlDataSource is named SqldataSource1.
  • The runat=”server” attribute appears, as always.
  • The ConnectionString attribute contains the binding expression that will load the connection string from the Web.config resource. The <%$ & syntax binds to the connectionstring named gamingConnectionString, appearing in the connectionStrings section of the Web.config file.
  • The SelectCommand property contains the SELECT statement that will be executed when the data source is bound.
  • Because you did not configure the SqlDataSource to perform updates, there is no UpdateCommand, DeleteCommand, or InsertCommand, property.

Exercise 2 – Binding a SqlDataSource to a ListBox

In this exercise, you will bind a ListBox control to the SqlDataSource that you just created. In this exercise, you will create a bound list box. When the user selects an item in this bound list box, the code you write will ultimately display the current record in a FormView control.

Binding a ListBox to a SqlDataSource involves setting up to three properties.

  • The DataSourceID property should contain the name of the SqlDataSource to which the list box will be bound. Most bound controls operate this way. Note also, that you can write C# code to bind the list box programmatically.
  • The DataTextField property contains the name of the field from the data source that will be rendered to the control’s visible area.
  • The DataValueField contains the name of a field from the data source. This field typically contains a primary key and is used in lookup operations. You will use the DataValueField later in this lab.
  1. Create a list box on the form (default.aspx). I suggest naming the list box lstProperty. The control can be configured in a couple of different ways.
    You can use the Wizard to specify the data source and the applicable properties.
    You can use the Properties window to configure the properties.
    The properties can be configured programmatically.

  2. Click the arrow shown in the above figure to begin setting the properties. In the popup that appears, click the Enable AutoPostBack check box. This causes the control to post back to the server, when an item is selected.
  3. Next, click Choose Data Source. The following dialog box will appear:

  4. In the above dialog box, select the data source named SqlDataSource1. Click the Refresh Schemalink on in the lower-left corner of the form. Otherwise, the fields will not be selectable in the following check boxes. Set the properties as shown in the above figure: the field named fldPropertyName will be displayed. The field named fldProperty will be set accordingly based on the selected value.
  5. Click OK to configure the list box. When you do, the following code is generated:

    As you can see, the fields discussed previously are set so that the control instance will be bound to the SqlDataSource control instance named SqlDataSource1.
  6. Run the program. The rows from the table should appear in the list box sorted by property name.

Exercise 3 – Getting a Value from a bound list box

In this exercise, you will write the code that will handle the SelectedIndexChanged event for the list box that you just created, and get the selected value. Later in the exercise, you will use this value to display a single record in a FormView control instance.

In this step, you will need to create the SelectIndexChanged event handler for the list box. There are three ways to do this.

  • Make sure the form editor is in Design view. In the Properties window, select the list box, click the Events icon (the little lightning bolt), and double-click in the column SelectIndexChanged. The system will create the event handler code in the C# code behind file. It will also create the onSelectedIndexChangeddeclaration event in the .aspx file.

  • While the Editor is in Source mode, begin to type onSelectedIndexChanged. Intellisense should allow you to select the event. Click the <Create new Event> button in the popup. The system will again create the event handler.
  • Finally, you can just write all of the code by hand.

OnSelectedIndexChanged="lstProperty_SelectedIndexChanged"

protectedvoidlstProperty_SelectedIndexChanged(object sender, EventArgs e)

{

}

Again, remember that the name of the event handler appearing in the .aspx file must match the name of the event handler appearing in the corresponding code behind file.

  1. Make sure that the form is in Design view and the ListBox control instance is selected.
  2. Press F4 to activate the Properties window. Click the Events button to display the events applicable to the ListBox control.
  3. In the Value column (right column), double-click the SelectedIndexChanged event. The system will update the .aspx file and .cs file to include the new event handler.

Be careful creating the event handlers. If the syntax if the .aspx file is not correct the program will not compile and the control cannot be rendered correctly as shown in the following figure.

Exercise 4- Reading bound list box properties

This exercise is bit nonsensical but it does demonstrate the use of the various bound properties. Remember, the following properties are set when the user selects an item from a list box.

  • SelectIndex contains the ordinal 0-based index of the selected item. If no item is selected, then the property’s value is -1.
  • SelectedItem contains the text selected by the user.
  • SelectedValue contains the corresponding index value.
  1. Create three labels on the form immediately after the list box that you just created.

  2. Enter the following code in the SelectedIndexChanged event handler that you just created.

  3. Run the program. Select an item from the list box. Select Caesars Palace. The form posts back to the server. The SelectedIndexChanged event fires, and the following results are displayed.

Exercise 5–Creating a SqlDataSource to select a single record

In the next set of steps you will create a second SqlDataSource. However, this SqlDataSource will support insertion, updating, and deletion. In addition, this SqlDataSource will be configured to select a single record instead of all records from a table.

You will then create a FormView control and bind it to this second SqlDataSource.

  1. Create a second SqlDataSource control on the form. I suggest that you use the Toolbox to do this.
  2. Again, use the menu to begin configuring the data source. Select the connection string named gamingConnectionString. As you can see, the same connection string can be used throughout the application.
  3. On the next dialog box, select all of the individual fields as you did before. Click the WHERE… button to build a WHERE clause. Complete the information as shown in the following dialog box:

    In the above dialog box, you are saying that the field named fldProperty must be equal to the parameter of the same name. These are the same parameters that you worked with programmatically. Here, the parameter name is @fldProperty. Click the Addbutton, the parameter appears in the WHERE clause as follows:

  4. Click OK to close the dialog box. Note that you could add additional parameters here. If you do, they will be connected together via an AND clause.
    It is here where the Wizard has limitations. It cannot build more complex WHERE clauses such as those involving sub selects, or OR phrases.
  5. Click the Advanced… button. It is in the following dialog box that you tell that system that you want to generate INSERT, UPDATE, and DELETE statements. For this to work, a primary key must have been defined for the table. Click Generate INSERT, UPDATE, and DELETE statements.

  6. Click OK to close the dialog box. Accept the defaults to finish creating the query.
    The following code is generated in the .aspx file:
  • First, note that all of the parameters match up. That is, the SelectCommand has one parameter named @fldProperty. Thus, there is one property of the same name in the SelectParameters element. Also, note the fldProperty is the name of the table’s primary key.
  • The InsertParameters also match the INSERT statement. That is, there are five (5) parameters. Note that fldProperty is not a parameter because the field is configured as an IDENTITY field. The database engine automatically increments the value for IDENTITY fields, and creates them on insert.
  • In the UpdateParameters section, fldProperty is a parameter. It is used in the WHERE clause to restrict the number of rows selected to one. (the primary key).

Exercise 6 – Creating a FormView to display a single record

Next, you will create a FormView control instance that will display the currently selected record from the list box.

Note that I have experienced unpredicted results using some of the FormView configuration tools. In some cases, it will create all of the default templates. In some cases, not. In this lab, you will create each of the templates by hand so as to better understand the process and avoid the idiosyncrasies of the FormView configuration wizard.

Before you create the FormView control, note the following about its behavior. There are two attributes essential to using the FormView:

  • The DataKeyNames property should contain the field name of the primary key.
  • The DataSourceID property contains the name of a SqlDataSource. This SqlDataSouce should be configured to select, and optionally insert, update, and delete exactly one record (typically based on the primary key.

Depending on how the FormView control is configured, there will be several child elements as follows. These templates are rendered based on the current status of the FormView (Editing, Viewing, Inserting).

  • EmptyDataTemplate is rendered when there is no data to display
  • HeaderTemplate is rendered at the top of the control when the HeaderText is set or a HeaderTemplate is defined
  • ItemTemplate is rendered when the control is displaying a record
  • EditItemTemplate is rendered when the control is editing a record
  • InsertItemTemplate is rendered when the control is inserting a record

Exercise 7 – Creating and Configuring a FormView control instance

In this next part of the exercise, you will create the FormView control

  1. Create a FormView control instance on the form immediately after the Label controls that you just created. Again, click the small arrow, if necessary, to activate the tool.

  2. Using Design view and the Wizard, set the data source to SqlDataSource2. This is the name of the second data source that you just created.
  3. In the following steps, you will configure the FormView by hand and configure the HeaderTemplate and ItemTemplate. Modify the FormView control so that it contains the following code:

Note the following about the code that you just wrote:

  • If there is no data to display, then the EmptyDataTemplate is rendered. That is, the content No Row to display will be rendered.
  • By default, the FormView appears in view / browse mode, and the content of the ItemTemplate is rendered. In this case, you have created a two-column table with 6 rows. Prompts appear in the first column, and bound labels appear in the second column. Again, the Eval statement is a binding expression. It gets the field from the current row.
  1. If you were to run the program at this point, you would see that there are no rows displayed in the FormView. While the FormView is bound to the SqlDataSource, the parameter in the WHERE clause has not been specified. Thus, no rows are selected.

  2. Modify the lstProperty_SelectedIndexChanged event handler by adding the following two statements.

SqlDataSource2.SelectParameters["fldProperty"].DefaultValue = lstProperty.SelectedValue;

SqlDataSource2.DataBind();

The SelectParameters property of the SqlDataSource contains a collection of parameters. In this case, the SelectCommand has one SelectParameter as follows.

By calling the DataBind() method of the DataSource, you are forcing the database to be queries again (bound).

  1. Run the program and select different records. As you select different records, the FormView is updated accordingly to display the currently selected record.

Exercise 8 – Creating and Configuring a FormViewfor Inserts

In this set of steps, you will configure the FormView control so that the user can insert records. There are three steps to this process:

  • First, on the ItemTemplate display, a button needs to be created that will cause the control enter Insert mode and render the InsertItemTemplate.
  • Second, the InsertItemTemplate needs to be created.
  • Finally, the input needs to be optionally validated.
  1. Activate the Source window for the page named Default.aspx.
  2. At the end of the code for the FormView control, add the following statement to create a new table row, just before the end of the table.

<tr

<tdasp:LinkButtonID="lbInsert"runat="server"Text="Insert"CommandName="New">

</asp:LinkButton

</td

</tr

</table
ItemTemplate