Something that is very useful in writing web pages that perform data inserts or updates is to restrict the options for certain fields to specific values. One way for this to be done is to have the update options be items listed in a drop-down box.

By Brad Kingsley

Something that is very useful in writing web pages that perform data inserts or updates is to restrict the options for certain fields to specific values. One way for this to be done is to have the update options be items listed in a drop-down box. Then the user can select one of the items from the list, and ONLY one of the items in the list, to set that value in the database.

I've written code to do this plenty of times in the past, but with ASP.Net 2.0 out now and the availability of tools like Visual Web Developer (VWD), I wanted to try this without writing ANY code. I got it, but it wasn't easy - mainly because I was tackling this without any direction. I did a lot of fumbling around before got the settings exactly right. I'll spare you the details of my mistakes and go right into how to do this properly so it will be easy for you to do yourself.

"Templates" are the term for how this is handled with ASP.Net. I didn't use Visual Studio with ASP.Net 1.x but I think the "template" process with v2.0 is very similar to how it worked in v1.x. In this sample I'll create a GridView then adjust one of the columns to be a drop-down list that is actually populated with data from a different table. The drop-down list should default to the value that matches the data, and it should update the main table when the drop-down list is changed in edit mode.

For this sample I'm using two tables in a Microsoft SQL Server database.

The first table is _Users and has four columns:
- UserID which is an integer data type and is the primary key
- Username which is a 20 character text field
- Password which is a 20 character text field
- TypeID which is an integer data type

The second table is _UserTypes and has two columns:
- TypeID which is an integer data type and is the primary key
- TypeDescription which is a 20 character text field

Go ahead and populate the tables with some sample data so you can play around with it when the page is done.

I first cranked up VWD and created a blank web form page. I also set up a data connection to the SQL Server that I'm using in this sample. I won't go through these steps because they are pretty simple and I'm sure there is already good information online if needed.

I then expanded the data connection and the tables folder beneath it. I dragged the _Users table from the data connections list onto my blank page. This automatically creates a GridView and formats it by default to all of the columns in the table. The GridView tasks box opens automatically - in there I checked the box for "Enable Paging" and "Enable Editing".

Running the page right now will show the data from the _Users table, let me page through the results (if there is more than will display on a single page), and let's me make updates to the data. I want to limit the updates to the TypeID field though so it has to match a value from the _UserTypes table. That is the field I want to set to use a drop-down list.

Clicking on Edit Columns brings up a dialog box that will let me set some properties of each table column. I selected TypeID in the "Selected Fields" box on the left to see the properties on the right. Down below where all the properties are listed is a link that reads "Convert this field into a templatefield". I clicked that link and then clicked OK to close that dialog box.

The GridView Tasks box should still be open from earlier. I next clicked on Edit Templates - that is where we will set up the TypeID template format. From the Template Editing Mode box I selected the TypeID in the Display dialog.

Notice that there are five different templates that can be defined for this column. The options are:

1. ItemTemplate which is how the data will look when it is displayed in a read-only format
2. AlternatingItemTemplate which controls the look of the display if it's desired to have alternating rows with a different appearance
3. EditItemTemplate which is how the column is handled in edit mode - this is what we want to control
4. InsertItemTemplate which controls how the insert of a new row is handled. This can also be a nested control similar to how we are going to configure the EditItemTemplate
5. HeaderTemplate which controls the look of the header for that column value

The EditItemTemplate has a TextBox control in there by default. I clicked the TextBox control and deleted it from the screen. Then I dragged a DropDownList control from the toolbox into the area where the TextBox was just removed.

Then I configured the data source for this control by clicking Choose Data Source. This open the dialog pages to configure the data source. On the first screen I selected New DataSource. On the next screen I chose Database since the data will be pulled from a table. On the next screen I chose my database connection string (there should be one in the drop-down list already) and clicked next. On the next screen I selected the _UserTypes table since that is where the drop-down will be populated from. When it lists the available columns I selected both TypeID and TypeDescription. Clicking Next and then Finish completed those configuration items. This now opens the Choose a Data Source option box. I left the data source as the one that was just configured and selected TypeDescription in the display box and TypeID in the value box. That will show the descriptions as the options but pass the value back to the database on updates. I clicked OK to finish.

If you were to run the page right now it would appear that it's working as desired, but when an option is selected in the drop-down for type and "update" is clicked to save the data - the option selected is not saved (it actually saves Null to that column in the table).

Here is how I got past this issue: I opened the DropDownList Tasks box and clicked Edit Databindings. On the dialog box that opened, I highlighted Selected Value on the left-hand side of the box and then choose TypeID in the "Bound to" field on the right-hand side of the box. The "Two-way databinding" option should already have a checkmark next to it (check it if it doesn't). Then I clicked OK to finish.

Run the page now and see that it works exactly as desired! Seems like a lot of work, but once you know how to do it, it's much faster than hand-writing the code to handle this manually.

Happy Coding!

Brad Kingsley is President and Founder of ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.