Practical ASP.NET

Implement Advanced GridView Techniques

ASP.NET 2.0 can let you implement common scenarios without writing code. However, if you want to handle multiple fields or link tables together using something other than the primary key, you're going to have to write some code and know what you're doing.

Technology Toolbox: ASP.NET, Visual Basic

One of Microsoft's goals with ASP.NET 2.0 is to enable developers to implement common scenarios without writing code. For instance, a master/detail page that lists the Customers in the Northwind database and the Orders for a selected customer is a zero-code solution in ASP.NET 2.0: You only have to interact with the smart tags and wizards for two sets of SqlDataSource and GridView controls. This is great—if all you want to do is display some records and let the user change them (see Figure 1).

p> The zero-code solution ties tables together using the primary key of the master table. However, even that will work only if the master table has a single field as its primary key. If you want to handle multiple fields or link tables together using something other than the primary key, you're going to have to write some code—and know what you're doing. You also need to step in if you want to control when updates are done, handle errors in updates, or simply display the master records on a separate page from the details record. I'll provide a quick review of the zero-code solution, then show you how to implement all of these scenarios (download the sample code here).

You can implement a master/detail page while taking advantage of ASP.NET 2.0 and Visual Studio 2005 simply by dragging drag two tables (such as the Northwind Customers and Orders tables) from Server Explorer onto the same page. Visual Studio 2005 adds two SqlDataSource and GridView controls to the page to retrieve the data and display it. Join the tables by working through the Orders table's SqlDataSource wizard until you get to the page that sets the Where clause. This page lets you tie the CustomerId field in the Orders table to the current record in the Customers GridView. Allow your user to perform updates by checking options on the GridViews' smart tags to enable updating, inserting, and deleting. Enable Selection on the Customers GridView so the customer can click on a row to specify which Orders to display.

Roughly the same process works if you use ObjectDataSource to display a list of Customer and Order objects. The major difference is that instead of dragging a table to the page, you use the ObjectDataSource Wizard to specify the methods on the objects that produce Customer or Order objects.

Control the Data
Out of all the columns in the Customers GridView, you might wonder how the Orders SqlDataSource selects the correct field to control the Orders GridView. It's not magic: The Orders GridView uses the value in the Customers GridView's SelectedValue property. When a GridView is bound to a SqlDataSource, the GridView's SelectedValue property defaults to the table's primary key. For the Customers table, the SelectedValue defaults to the CustomerId that you need in order to retrieve related Orders records. The process is more obvious if you use an ObjectDataSource to retrieve the data for your GridView, because the SelectedValue property doesn't default to any value. Instead, you must use the DataKeyNames property to specify the property to be returned by SelectedValue (see Figure 2).

You can use the DataKeyNames property to control the field used with SqlDataSource when the default settings for SelectedValue aren't appropriate. You need the SelectedValue property to return the customer's Region rather than the CustomerId if, for instance, you want to display a list of the Suppliers in the customer's area. You can have the SelectedValue property return the Region from the selected row by setting the DataKeyNames property for the GridView to Region.

As DataKeyNames' name implies, you can specify multiple fields; each field name must be followed by a comma. This is useful when you need to pass more than a single value to the GridView with the detail records. Your list of suppliers, for instance, will be more useful if you show only suppliers that are in both the same city and the same region as the selected customer. Unfortunately, the SelectedValue property returns only the value of the first field in DataKeyNames' list.

Unlike the previous scenarios, you must write some code to implement passing multiple values. The code to retrieve those values goes in the Customers GridView's SelectedIndexChanged event, which fires after the user selects a row. In that event, you can extract the values for the keys specified in DataKeyNames from the DataKeys collection of the GridView. However, the Orders data won't display automatically. You can trigger a data retrieval by calling the DataBind method of the GridView.

This code runs when the user selects a new customer to extract the required fields and call the DataBind method of the GridView to display:

Dim strRegion As String
Dim strCity As String

Protected Sub gvCustomers_SelectedIndexChanged( _
	ByVal sender As Object, _
	ByVal e As System.EventArgs) _
	Handles gvCustomers.SelectedIndexChanged

	strRegion = Me.gvCustomers.DataKeys(0).ToString
	strCity = Me.gvCustomers.DataKeys(1).ToString

End Sub

Next, pass the extracted values to the data source that's retrieving the values for the Suppliers GridView. The SqlDataSource must have a SQL statement that includes two parameters for the Region and City:

Select *
	From Suppliers
	Where Region = @Region 
		And City = @City

Put this code in the Supplier's SqlDataSource's Selecting event, which fires just before the data is retrieved. The e parameter for the event contains, in its Command property, a reference to the Select statement used to retrieve the data. You can use the Parameters collection of the Command property to set the values of the parameters:

If strRegion > "" Then
	e.Command.Parameters("@Region").Value = _ 
	e.Command.Parameters("@City").Value = strCity
End If

The process is almost identical when working with the ObjectDataSource, except that in the Selecting event, you set the InputParameters of the e parameter to the values that you want to use:

e.InputParameters("Region") = strRegion
e.InputParameters("Region") = strCity

Configuring the DataKeyNames property makes sense if you're supporting only a single other GridView. However, if you want to support multiple related GridViews (for instance, if you want to display both Orders and Suppliers on a single page), you need to access the GridView's selected values directly.

The GridView's SelectedRow returns the row that the user selected. You can use the SelectedRow's Cells collection to retrieve the value of a particular column. For example, this code retrieves the Region and City values from the second and third columns of the selected row:

strRegion = Me.gvCustomers.SelectedRow.Cells(1).Text
strCity = Me.gvCustomers.SelectedRow.Cells(2).Text

Integrate Multiple Pages
Up until now, I've been assuming that you want to display the Orders or Suppliers list on the same page as the Customers GridView. The process is different if you prefer to send the user to a second page to display the detail record. Almost all of the required changes are made in the Customers GridView (the Master table). Instead of enabling selection for the Customers GridView, add a Hyperlink column to the GridView by selecting the Edit Columns choice in the Customers' smart tag.

As a minimum, you must set two properties in the Hyperlink to link to another page. Set the Hyperlink's Text property to the string that you want to display in the row, and set the NavigateURL property to the URL of the new page to be displayed when the user clicks on the hyperlink. These two changes create a link to the new page, but they won't pass any data to the SqlDataSource on the new page.

You pass data by setting two additional properties: the DataNavigateUrlField and the DataNavigateFormatString. You must set the DataNavigateURLField to the field (or fields) that you want to pass to the new page. You need to set the DataNavigateFormatString to a URL that you want to integrate the data from the DataNavigateUrlFields into. Incorporate the data fields into the format string by including field markers (numbers enclosed by French braces) to indicate where the data fields should go.

Link the Customers table to the Supplier table by specifying the Region and City fields in the DataNavigateUrlField and this string in the DataNavigateFormatString:


At run time, the GridView places the first field specified in the DataNavigateUrlField in the string at the {0} format marker, and places the second field at {1}. The GridView generates the URL when the Region is BC and the city is Vancouver:


You can implement a solution on the new page with no code by using the SQLDataSource's Wizard. On the Where clause page, bind each parameter in the SQL statement to the appropriate field in the QueryString.

Handle Updates
The default functionality of the GridView and data source objects is all you'll need if you only want to let users enter data and use that data to update the database. However, many scenarios require that you do more. For instance, you might want to allow updates only when some set of the conditions are met. At the least, you'll want to check for errors during updating and handle them yourself rather than let the default error handling terminate your application.

SqlDataSource and ObjectDataSource both fire Updating, Inserting, and Deleting events before any data is changed. You can prevent the update from proceeding in one of these events by setting the event's e parameter's Cancel property to True. This code from the Updating event checks to make sure that the account hasn't been closed before allowing updates:

If Me.chkAccountClosed.Checked = True Then
	e.Cancel = True
End If

See if any errors occur in either SqlDataSource or ObjectDataSorce after your data has been changed by checking the Updated, Inserted, and Deleted events. The e parameter passed to these events includes an Exception property that returns a reference to any Exception object created during data changes. If you do handle the exception in your code, set the e parameter's ExceptionHandled property to True to prevent ASP.NET's default error handling from stepping in. This code from the Updated event checks to see if an error occurred and, if so, displays the Exception's error message and suppresses the default error handling:

If e.Exception IsNot Nothing Then
	Me.txtUpdateError = e.Exception.Message
	e.ExceptionHandled = True
End If

These "post-change" events are also where you should retrieve any data that you need to pass on to some other process, because you shouldn't pass on data if the updates fail for any reason.

The number of scenarios where you can use GridView, SqlDataSource, and ObjectDataSource are infinite. For instance, if you choose optimistic locking for your data source, no exception is raised if an update fails. You will probably want to check the AffectedRows property of the e parameter passed to the post-change events and notify your user if nothing was actually changed. When something does go wrong, it's a good idea to keep the GridView in edit mode so the user can take another try at making his or her changes, which can be handled by setting the GridView's EditIndex property. I've ignored the DetailsView and FormView controls in this article, but you'll find that their methods, properties, and events mimic those of the GridView (except you don't need to specify the row number when working with those two controls). Not every scenario can be handled with zero code, but you'll find that ASP.NET lets you handle most of your scenarios with exceptionally little code.

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.