Practical ASP.NET

Integrating Dynamic DataBound Columns with SqlDataSource

You've given your users cascading DropDownLists to make it easy for them to find the item they want -- but now your page won't update. Peter has a solution for single-tier applications using the SqlDataSource.

• By Peter Vogel
• 02/18/2009

Here's the scenario: You drop a FormView on your page and add two DropDownLists on each of the EditItemTemplate and the InsertItemTemplate. The first DropDownList lets the user select a category and the second DropDownList shows items within that category. That second DropDownList also represents a field in your underlying data so you've also used the Edit Databindings dialog to bind the second DropDownList's SelectedValue property to some data.

It all works...until your users change the selected item in the category DropDownList, forcing the second DropDownList to refresh. At that point you get this message: "Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control." There's a good explanation for this (and you can find one here) but in the end, you don't really care. You want a solution.

The good news is that there's a workaround for this problem but it requires a) changing the default configurations for your data controls and b) writing some code. I'm going to need a couple of columns to cover all of this. In this one, I'll discuss how to get the data to display when using the SqlDataSource; in my next column I'll cover how to handle updates and inserts with the SqlDataSource; and in the third column I'll cover how the solution differs when using the ObjectDataSource.

For all of these columns, I'm assuming that the controls are in a FormView named FormView1 (though the solution for a GridViews or a DetailView is similar). I've also assumed that you've customized both the InsertItemTemplate and the EditItemTemplate (in a GridView you would only have to deal with the EditItemTemplate). Finally, I've assumed that the DropDownLists in the two templates have the same value for their Id property: DropDownList1.

Displaying Data with the SqlDataSource
The first step is to go to the Edit Databindings dialog and remove the databinding between the DropDownList in both templates (leave in place the DataSource settings that fill the DropDownLists). At this point, your FormView will actually start working -- sort of; the error message will go away. However, the field that the DropDownList was bound to will no longer contain the correct value when the user goes into Insert or Edit mode and, of course, the field won't be updated with the value the user enters on the page.

So how do you get the now-unbound DropDownList to display your data? Whenever the user switches between templates, the FormView's DataBound event fires, just after the data has been retrieved and moved to the bound controls in the FormView. This event is where you should put your code to update the unbound DropDownList with the correct value.

The first step is to retrieve the data associated with the FormView through the FormView's DataItem property. Depending on what mode the control is in, the property may be null (if, for instance, the control is in Insert mode). Furthermore, the data type of the FormView will differ depending on what kind of DataSource the FormView is bound to: For a SqlDataSource, the DataItem will be a DataRowView.

Putting it all together, the initial code in the DataBound event checks to see if the DataItem isn't Nothing and converts the property to a DataRowView when it isn't (as shown in Listing 1).

The next step is to see if one of the DropDownLists is being displayed (if the FormView is in Display mode, for instance, the DropDownLists won't be present). The code to find the DropDownList looks like Listing 2.

At this point, you might think you could just set the DropDownList's SelectedValue to the corresponding value from the DataRowView. Unfortunately, this will generate an error if the DropDownList's Items collection doesn't include the value. You could handle this in a number of ways but, at this point, I'll just add the retrieved value to the DropDownList if it isn't present (the third column in this series will have a better solution).

The code to check to see if the value isn't present and add it looks like this:

Dim li As ListItem
li = ddl.Items.FindByValue(drv.Item("ProductName"))
If li Is Nothing Then
   ddl.Items.Add(drv.Item("ProductName"))
End If

Now you can move the data from the DataRowView and into the page:

ddl.SelectedValue = drv.Item("ProductName")