O'Reilly logo

Learning ASP.NET 2.0 with AJAX by Dan Hurwitz, Jesse Liberty, Brian MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Displaying and Updating the Data

Now that your SqlDataSource object is ready to go, you only have to set up your GridView control. In Design view, click on the GridView Smart Tag and choose "Edit Columns." Verify that the checkboxes to enable editing and deleting are selected, as shown in Figure 4-17.

If you prefer to have buttons for Edit and Delete, rather than links, click on the Smart Tag and select "Edit Columns…" When the Fields dialog box opens, click the Command Field entry in the Selected Fields area (lower-left corner). This brings up the Command Field Properties in the right-hand window. In the Appearance section of the Fields editor, choose ButtonType and then change Link to Button in the drop-down menu next to ButtonType, as shown in Figure 4-18.

The result is that the commands (Edit and Delete) are shown as buttons, as shown in Figure 4-19.

Select the Smart Tag on the GridView, and check the boxes to enable editing and deleting.

Figure 4-17. Select the Smart Tag on the GridView, and check the boxes to enable editing and deleting.

Click the Smart Tag of the GridView, then click Edit Columns to get this Fields dialog box where you can select and edit the columns in the GridView. Here, the CommandField button type is being changed.

Figure 4-18. Click the Smart Tag of the GridView, then click Edit Columns to get this Fields dialog box where you can select and edit the columns in the GridView. Here, the CommandField button type is being changed.

You can change the Edit and Delete links in the GridView to buttons, if you prefer.

Figure 4-19. You can change the Edit and Delete links in the GridView to buttons, if you prefer.

Take It for a Spin

Start the application. The product database information is loaded into your GridView. When you click the Edit button, the data grid automatically enters edit mode. You'll notice that the editable text fields change to text boxes and checkboxes, as appropriate, and the command buttons change from Edit and Delete to Update and Cancel. Make a small change to one field, as shown in Figure 4-20.

When you click the Update button for that row, the grid and the database are both updated, which you can confirm by opening the table in the database, as shown in Figure 4-21.

To open the database table, stop the application first. Then on the right side of the IDE, click the Database Explorer tab (in VWD; it is called Server Explorer in VS2005). Expand the AdventureWorks folder, and then expand the Tables folder. Scroll down until you find the Product (Production) table (in the IDE, the schema name is displayed in parenthesis after the table name—go figure), then right-click it, and select "Show Table Data." This will show you the contents of the table from within the IDE.

When you click Edit on a row, that row enters edit mode. Any fields that can be edited change to text boxes and checkboxes.

Figure 4-20. When you click Edit on a row, that row enters edit mode. Any fields that can be edited change to text boxes and checkboxes.

Modifying the Grid Based on Events

Suppose you would like you to modify the grid so the contents of the Name column are red when the MakeFlag column is checked, that is, when its value is True. In addition, you want all the ProductNumbers that begin with the letters CA to display in green. You can do this by handling the RowDataBound event. As the GridView is populated with data, each row of data is bound to the GridView individually, and the RowDataBound event is fired once for each row.

To modify the GridView, switch to Design view, click the GridView, click the lightning bolt in the Properties window, and double-click in the method name column (currently blank) to the right of the RowDataBound event. The IDE will create an event handler named GridView1_RowDataBound() and then place you in the code-behind file within the skeleton of that method, ready for you to start typing code.

The second argument to this method is of type GridViewRowEventArgs. This object has useful information about the row that is databound, which is accessible through the Row property of the event argument.

If you view the table in the database after editing it in the GridView, you'll see that the changes have been saved.

Figure 4-21. If you view the table in the database after editing it in the GridView, you'll see that the changes have been saved.

Enter the code shown in Example 4-3.

Example 4-3. Handling theRowDataBound event

Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
              ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
  If e.Row.RowType = DataControlRowType.DataRow Then
      Dim cellProductNumber As TableCell = e.Row.Cells(3) ' ProductNumber column
      If cellProductNumber.Text.Substring(0, 2) = "CA" Then
          cellProductNumber.ForeColor = Drawing.Color Green
      End If

      Dim cellMakeFlag As TableCell = e.Row.Cells(4) ' MakeFlag column
      Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox)
      If cb.Checked Then
          e.Row.Cells(2).ForeColor = Drawing.Color.Red
      End If
  End If
End Sub

The first If statement (highlighted in Example 4-3) tests if the type of Row passed in as a parameter—in other words, the row that was bound and triggered this event—is a DataRow (rather than a header, footer, or something else).

Once you know you are dealing with a DataRow, you can extract the cell(s) you want to examine from that row. Here, we will look at two cells: the ProductNumber cell is the fourth cell in the row, at offset (index) 3, and the MakeFlag cell is the fifth cell in, at offset 4. (Remember, all indices are zero-based.)

To access the ProductNumber cell, you define a new variable, cellProductNumber, defined as a TableCell with the As keyword, and set it equal to the cell at offset 3 in the row, like this:

	Dim cellProductNumber As TableCell = e.Row.Cells(3)

Once you have the cell as a variable, you want to get the text contained in the cell to compare to your known value. You do that by accessing the Text property of cellProductNumber, and then using the Substring() function.

The Substring() function, as you might guess from its name, extracts a smaller string from a larger one. This is a pretty simple function to work with. First, you call the function on a string, and you give it two numbers as parameters: the index of the start of the substring, and the length of the substring. As with all other indices, the first character in the string is position zero. You want the first two characters from the Text string, so the starting index is 0, and the length of the substring is 2. Therefore, to get the first two characters from your string, you use the function Substring(0,2). Once you have that substring, you can use a simple If statement to compare it to the string you want to match, "CA":

	If cellProductNumber.Text.Substring(0, 2) = "CA" Then

It there is a match, you want to set the ForeColor property of the cell to green, which you can do using the Drawing.Color.Green property:

	cellProductNumber.ForeColor = Drawing.Color.Green

In the case of the MakeFlag, it is somewhat more complicated. It's easy enough to isolate the cell that contains the checkbox—it's at index 4—and then assign that value to a new variable called cellMakeFlag:

	Dim cellMakeFlag As TableCell = e.Row.Cells(4)

This is the same technique you used to isolate the ProductNumber cell. In this case, though, the Text property of this cell will always be empty. However, it does contain a CheckBox control, which is the only control in the cell. Instead of reading the text in the cell, you want to read the value of the Checked property of that CheckBox control. Each cell has a collection of all the controls contained in the cell, called Controls, which has a zero-based index. Since the checkbox you want is the only control in the collection, you know it's at cellMakeFlag.Controls(0). Next you define a new variable, cb, which you define as a CheckBox. Then you use the CType function on the control you just isolated, to convert the control to a CheckBox. This works because we know it is a CheckBox:

	Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox)

Then you test the Checked property of the CheckBox:

	If cb.Checked Then

If the box is checked, cb.Checked will evaluate to true. If it is checked, you want to set the ForeColor property of the third cell in the row (offset 2), the ProductName column:

	e.Row.Cells(2).ForeColor = Drawing.Color.Red

You set the color of the cell the same way you did for ProductNumber, but notice this time you're not changing the color of the checkbox cell itself—you're changing a different cell in the table.

Run the web site. It will look identical to Figure 4-19, except the product names for which the MakeFlag field is checked will display in red, and some of the product numbers will display in green. (Neither of these changes will be obvious in the printed book, so we will forego a figure showing the color changes.)

Selecting Data from the GridView

Often you need to select a row from the grid and extract data from that row. This is easy to do using the SelectedIndexChanged event of the GridView.

To see how this works, drag a Label control from the Standard section of the Tool-box onto the Design view, below the grid but within the UpdatePanel control. Change the Text property of this Label to Name. Then drag a TextBox control next to the Label. Change its ID property to txtName and set its ReadOnly property to True. You now have a place to display the name of the selected item from the grid.

Click on the Smart Tag of the GridView and check the "Enable Selection" checkbox. This will cause a Select button to display in the first column of the grid, next to the Edit and Delete buttons already there, as shown in Figure 4-22.

Clicking Enable Selection in the Smart Tag causes Select buttons to appear in a GridView.

Figure 4-22. Clicking Enable Selection in the Smart Tag causes Select buttons to appear in a GridView.

Now all you need to do is set up the event handler to respond to the Select buttons. Double-click on the Select button in the first row of the grid. This will open up the code-behind file with the skeleton of the SelectedIndexChanged already created for you, ready to accept your custom code. Enter the highlighted code from the following snippet:

	Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, _
	      ByVal e As System.EventArgs)
	   If GridView1.SelectedRow.RowType = DataControlRowType.DataRow Then
	      Dim cellName As TableCell = GridView1.SelectedRow.Cells(2) ' Name column
	      txtName.Text = cellName.Text
	   End If
	End Sub

This code first tests to determine if the selected row is a DataRow (as opposed to a HeaderRow or a FooterRow). If it is a DataRow, it creates a variable of type TableCell, which is assigned to the third cell in the selected row (because of zero-based indexing, the third item will have an index value of 2). Then the Text property of the Text-Box is set equal to the Text property of that cell.

Run the app and click on one of the Select buttons. The name from the selected row appears in the TextBox.

Passing Parameters to the SELECT Query

Sometimes you do not want to display all the records in a table. For example, you might want to have users select a product from your grid and display the order details for it in a second grid on the current page. To do this, you'll need a way to select a product as well as a way to pass the ID of the selected product to the second grid. The Select buttons are already in place from the previous example, so all you need to do now is pass the ID of the selected product to the second grid.

To keep the downloadable source code clear, copy the previous example, AWProductData to a new web site, AWProductDataOrderDetails.

Tip

See Appendix A for details about how to copy a web site.

You need to create a second GridView, which will be used to display the order details. From the Toolbox, drag the second GridView onto the page below the first, and then drag the Label and TextBox inside the UpdatePanel. Open the Smart Tag for the UpdatePanel. As you did earlier in the chapter, create a new data source (name it AdventureWorksOrderDetails), but use the existing connection string. Choose the SalesOrderDetail table, select the desired columns (for this example, SalesOrderID, CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount, and LineTotal), and then click the Where button, as shown in Figure 4-23.

A WHERE clause is a SQL language keyword used to narrow the set of data returned by the SELECT statement. In other words, you're saying, "Get me all the records from this table, where this condition is true." The condition could be defined any number of ways—where the amount in inventory is less than 10, where the customer name is "Smith," or where the copyright date is after 1985. It all depends on the types of information you have stored in your columns.

When you click the WHERE button, the Add WHERE Clause dialog opens, which you can see in Figure 4-24. First, you pick the column you want to match on, in this case ProductID. Next, pick the appropriate operator for your condition statement. Your choices include among others, equal to, less than/greater than, like, and contains. For this exercise, use the default (=).

The third drop-down lets you pick the source for the ProductID—that is, where you will get the term you want to match on. You can pick from any one of several objects in the menu or choose None if you'll be providing a source manually. In this case, you'll obtain the source of the ProductID from the first GridView, so choose Control.

Configuring the SalesOrderDetail table SELECT statement is similar to the way you set up the first DataSource, but this time, you'll add a WHERE clause.

Figure 4-23. Configuring the SalesOrderDetail table SELECT statement is similar to the way you set up the first DataSource, but this time, you'll add a WHERE clause.

When you choose Control, the Parameter properties panel of the dialog wakes up. You are asked to provide the ID of the Control containing the target parameter. Select GridView1. Once you've made all your choices, the screen will resemble Figure 4-24.

Click Add. When you do, the upper portion of the dialog returns to its initial (blank) state and the WHERE clause is added to the WHERE Clause window. You could add additional WHERE clauses at this point, to further restrict the data, but we won't for this example.

Click OK to return to the Configure Select Statement dialog box. While you are at it, sort the results by the SalesOrderID column by clicking on the Order By button. The Add ORDER BY Clause dialog with the SalesOrderID column selected is shown in Figure 4-25. The ORDER BY clause is another SQL keyword, and this one does just what its name implies—it sorts the results using the selected field for sort order.

Click OK until the Configure Data Source Wizard is finished.

Switch to Source view and again fix the name of the tables in the SQL statements that were auto-generated. The markup for the second GridView and its associated SqlDataSource is shown in Example 4-4, with the corrected table names highlighted.

Add a Where clause to your SELECT statement with the Add WHERE Clause dialog. You select the column, the operator, and the source here.

Figure 4-24. Add a Where clause to your SELECT statement with the Add WHERE Clause dialog. You select the column, the operator, and the source here.

Add an ORDER BY clause to sort the results of your SELECT statement.

Figure 4-25. Add an ORDER BY clause to sort the results of your SELECT statement.

Example 4-4. Order detail grid withSqlDataSource

<asp:GridView ID="GridView2" runat="server"
    DataSourceID="AdventureWorksOrderDetails">
</asp:GridView>
<asp:SqlDataSource ID="AdventureWorksOrderDetails" runat="server"
    ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
    SelectCommand="SELECT [SalesOrderID], [CarrierTrackingNumber],
        [OrderQty], [UnitPrice], [UnitPriceDiscount], [LineTotal]
        FROM [Sales]. [SalesOrderDetail]
        WHERE ([ProductID] = @ProductID)
        ORDER BY [SalesOrderID]">
    <SelectParameters>
        <asp:ControlParameter
                ControlID="GridView1"
                Name="ProductID"
                PropertyName="SelectedValue"
                Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

Also highlighted in Example 4-4 are the results of the WHERE and ORDER BY buttons from the Configure Select Statement Wizard.

The SELECT statement now has a WHERE clause that includes a parameterized value (@ProductID). In addition, within the definition of the SqlDataSource control is a definition of the SelectParameters. This includes one parameter of type asp: ControlParameter, which is a parameter that knows how to get its value from a control (in our example, GridView1). In addition, a second property, PropertyName, tells it which property in the GridView to check. A third property, Type, tells it that the type of the value it is getting is of type Int32, so it can properly pass that parameter to the SELECT statement.

You may now reformat your grid and edit the columns as you did for the first grid, and then try out your new page, which should look something like Figure 4-26.

Warning

The AdventureWorks database has no order details for any of the entries with ProductIDs below 707. The first entry with details is on Code-Behind Files of the grid, so be sure to move to Code-Behind Files (or later) to see product details. If you select a product that does not have any order details, the second grid will not appear.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required