Using GridView to Save and Retrieve Data with AJAX

In this third part of a four-part series on making your ASP.NET applications featuring AJAX save, retrieve, and change data, you’ll learn more about the GridView. This article is excerpted from chapter four of Learning ASP.NET 2.0 with AJAX: A Practical Hands-on Guide, written by Jesse Liberty, Dan Hurwitz and Brian MacDonald (O’Reilly, 2007; ISBN: 0596513976). Copyright © 2007 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

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.

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.

{mospagebreak title=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.

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 colum
      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).

If-Then Statements

When you’re working with data, you usually don’t know what the data will be when you’re writing your code. You might want to take different actions depending on the value of a variable. That’s what the If-Then statement is for. You’ve seen how the Checked value of a checkbox or radio button can affect the behavior of other controls. With the If-Then statement, you can be even more flexible:

  If chkMyCheckBox.Checked = true Then
txtMyTextBox.Text = "It’s true!"
  End If

The condition you want to evaluate comes after the If, but before the Then. In this case, you want to determine if the checkbox is checked, so the condition is chkMyCheckBox.Checked = true .

If it’s true, the statement after the Then is executed, setting txtMyTextBox.Text to “It’s true!” You can execute any number of statements in the Then section.

If the condition is false, nothing happens.

You must insert the statement End If at the end of the Then block so that your code knows where the Then block ends and can continue executing as normal from that point.

The Else statement comes into play when you want to take one of two actions. With just an If-Then statement, if the condition you’re evaluating is false, nothing happens. However, you might want to take one action if the condition is true, and another if it’s false, like this:

  If chkMyCheckBox.Checked = true Then
txtMyTextBox.Text = "It’s true!"
txtMyTextBox.Text = "Not true!"
  End If

This code sends one message to txtMyTextBox if the condition is true, and a different message if it’s false.

You have lots of different options when you specify conditions, which are based on a set of “operators” that you’re probably already familiar with. For example, instead of testing to see if one part of your condition is equal (=) to another, you could use one of these other operators:

  1. <> not equal to
  2. < less than
  3. > greater than
  4. <= less than or equal to
  5. >= greater than or equal to

In short, you can test for any condition that evaluates to true or false—in other words, a Boolean. In fact, the Checked property of a textbox is a Boolean all by itself, so you could have used this for the condition:

  If chkMyCheckBox.Checked Then

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

If 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)

CType Method

CType converts its first argument into an object of a new type as specified by its second argument. In the case shown here, it is converting a control to a CheckBox. If the object you pass is not of the appropriate type, CType generates an error. Read this statement:

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

as follows: “Find the first item in the Controls collection in cellMakeFlag and convert it to type CheckBox.” The result will be an object of type CheckBox or an exception will be thrown. If no exception is thrown, assign the result to the variable cb, which is of type CheckBox.

If you want to be extra careful, you can wrap the CType conversion in a try/catch block, discussed in Chapter 8, but that isn’t really necessary here as you know it is a 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.)

{mospagebreak title=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 Toolbox 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.

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 TextBox 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.

{mospagebreak title=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.

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.

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.

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.

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"
<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]">

                Type="Int32" />

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.

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

Please check back next week for the conclusion to this article.

One thought on “Using GridView to Save and Retrieve Data with AJAX

  1. This article is an excerpt from the book “Learning ASP.NET 2.0 with AJAX: A Practical Hands-on Guide,” published by O’Reilly. We hope you found it to be enjoyable and educational. Please let us know what you thought of it, and if you would like to see more content of this nature.

[gp-comments width="770" linklove="off" ]