Practical ASP.NET

Updating Dynamic DataBound Columns with SqlDataSource

Peter continues his series on integrating cascading DropDownLists with databound controls and the SqlDataSource.

• By Peter Vogel
• 02/25/2009

You need to both write code and change the default configuration for some controls to ensure updating. Updating won't happen correctly because the first step in eliminating the error message is to remove the databinding for the DropDownList that's tied to data in the DataSource. To implement a complete solution, you need to make changes to move data from the page into the DataSource for both updates and inserts.

Handling Inserts
Surprisingly (at least for me), handling inserts is easier than handling updates. As with getting the data to display, you need to pick an event to put your code in. The best place to put your code is in the FormView's ItemInserting event which fires just before the DataSource issues the SQL Insert statement.

In the ItemInserting event, you first need to find the DropDownList in the FormView. Unlike displaying data in the DataBound event (discussed in my last column) you're guaranteed that the DropDown list is present so you don't need to check that the control is found.

Once the control is found, you just need to move its current SelectValue into the right field in the DataSource's Values collection. The Values collection is passed into the event as a property on the e parameter. You can retrieve the field you want to update from the Values collection by name.

Putting those parts together, Listing 1 is the code for the ItemInserting event.

This event, by the way, is also a great place to check any data going to the database. If you find a bad value, you just set the e parameter's Cancel property to False to prevent the update from proceeding.

Handling Updates
Updates don't work nearly as cleanly as inserts. You might expect that all you need to do is update the NewValues collection in the FormViews ItemUpdating event. Unfortunately, I've found that solution unreliable. In this column, I'm going to provide a more complicated solution but one that's never failed for me.

First, you should check to ensure that the update statement in the DataSource includes the unbound field. To do that, select the DataSource that the FormView is bound to, find the UpdateQuery property in the Properties Window and click on the builder button (the button with the three dots). When the Query Editor appears, check that the SQL statement includes a clause that updates your unbound field. In my case, I'm updating the ProductName field, so I expect to see something like this:

UPDATE [Order Details] 
  SET ProductName = @ProductName, UnitPrice...

If your field isn't present, rewrite the SQL statement to include it (I've found the clause missing in Updates on several occasions, though I've never had the same problem with the Insert statement).

To handle updates, I've found the best place to put my code is in the DataSource's Updating event. If the UpdateQuery was missing the clause for my unbound field, I find that I have to add a SqlParameter to the Command's Parameters collection for the parameter that I added to the statement (no amount of clicking on the Refresh Parameters button in the query edit window or rebuilding the application allows me to avoid this step). For my ProductName field, I need to add a SqlParameter with the same name as the Parameter that I put in the SQL statement. In this case, that parameter is of type string and can be up to 25 characters long, as shown in Listing 2.

As with handling inserts, you need to find the DropDownList in the FormView and use its SelectedValue to update the parameter you just added. Listing 3 shows what that code looks like.