The DataGrid server control is one of the most widely used controls in ASP.NET-based projects to display data in a table form. It is powerful, easy to implement, customizable, and enriched with features such as sorting and inline editing.

Although sorting is simple to implement, editing and adding new rows can be tricky in cases where some part of data comes from a database and needs to be added or edited using DropDownList controls. In this article, I'll explain adding and editing rows with DropDownLists using database and manual entries as data sources. The example is based on creating an expense entry and reporting system for an imaginary company.

The imaginary company wants to have an Internet-based expense entry and reporting system.The company has numerous expense categories, such as rent, fuel, etc. and different payment methods (cash, credit cards, etc.) to pay these expenses. Expenses can be either in U.S. or Canadian currency. The company also wants to keep track of expense dates.

Expense categories and payment methods can be modified (such as adding more expense categories or payment methods in time), therefore these values will be read from a database. Expenses will also be stored in the same database. As a result, our SQL database consist of three tables: ExpenseCategories, PaymentMethods, and Expenses (see FIGURE 1).

FIGURE 1: The tables and their relationships.

As the table names imply, table ExpenseCategories, table PaymentMethods, and table Expenses store values of expense categories and values of payment methods and expense entries, respectively. In a "real" application, expense categories and payment methods tables should be accessed using Internet-based user inferfaces, but I chose to use a few manual values for each table for simplicity.

The example DataGrid has two modes: add and edit (see FIGURES 2 and 3). During add mode, users can add new expenses using text fields and DropDownLists provided in the footer section of DataGrid. To ensure data integrity, such as preventing users from entering alphanumeric values in an expense amount field or entering an expense without selecting a expense category, data validation is implemented. In the edit mode, users can edit and update any previous expense entry. During the edit mode, the footer of the DataGrid is not visible in order to provide clarity. Validating data entries is performed in the edit mode as well. Deleting any previous entry is possible in both modes.

FIGURE 2: The DataGrid in add mode.

FIGURE 3: The DataGrid in edit mode.

DropDownList controls holding the Payment Method and Expense Category values read their list items from the database. However, items in the DropDownList control holding Currency values are not expected to be modified, therefore these items are hard coded. In order to provide standardization in Date entry, the Date field is a read-only text box. Clicking  opens a pop-up window with a Calendar control, allowing the user to select any date. The selected date is copied to the Date text box. The name of the text box must be supplied to pop-up window as an argument. Clicking the  image in a row deletes the row. clicking the image in a row, takes the DataGrid to the edit mode and allows editing of the selected row.

Users can update the edited row by clicking the  image or cancel editing by clicking the  image. In both cases, the DataGrid goes back to add mode.

One of the most appreciated feature of .NET is code-behind technology. Simply adding one line to a user interface page, separates design (presentation tier) from code (business and data tiers):

Therefore, I will mostly discuss the design parameters of the DataGrid in this section, and use code parts from the code-behind page as necessary.

Although the main focus of this article is DropDownLists, the Date column also features properties that might be useful. First, take a look at the Date column. As a general rule, in order to use the footer section of any Bound Column in a DataGrid control, Bound Columns must be converted to Template Columns.

In add mode, date information is displayed in a Label (lblExpenseDate in the ItemTemplate section) control and there is a TextBox control (tbExpenseDateF in the FooterTemplate section) in the footer section along with a HyperLink control (lnkCalendarF in FooterTemplate section) and a RequiredFieldValidator (rfvtbExpenseDateF in FooterTemplate section) to add new records. RequiredFieldValidator ensures that a user selects a date before adding a new record. It is also possible to use a static hyperlink instead of a HyperLink control. However, the Date pop-up window requires a text box to supply the selected date:

The DataGrid control generates new control names during runtime using row numbers. Therefore, using a static hyperlink will not work for DataGrids. The solution is to use a HyperLink control and assign its NavigateURL property dynamically in the DataGrid's itemDataBound event handler:

Working with the Date column in the edit mode is no different. Again, we have a TextBox control (tbExpenseDate) with a HyperLink control (lnkCalendar) and a RequiredFieldValidator (rfvtbExpenseDate). All three controls are defined in the EditItemTemplate section.

Now, we can examine the Payment Method column, which reads its list items from a database:

In the add mode, Payment Information is displayed in a Label control (lblPMethodName in the ItemTemplate section). The footer section holds a DropDownList control (ddlPMethodF in the FooterTemplate section) for users to select a payment method. This DropDownList control gets its items from the PaymentMethods table during runtime in the ItemDataBound event handler of the DataGrid as follows.

Open a connection to SQL Server and create a SqlCommand object. oConn is a SQLConnection object defined as Dim oConn As New SqlConnection(Session("ConnProvStr")). Session("ConnProvStr") contains details of the connection to SQL Server and is defined in the Load event of page:

Define a DropDownList object, and find the ddlPMethodF control in the footer section and assign this control to the newly defined DropDownList. Execute the command object and fill the DropDownList with data.

As shown in FIGURE 2, the Payment Methods DropDownList control contains an item called "Select One". This item is added to the item list after items are read from the database. Another solution is to add the item "Select One" to the PaymentMethods table directly and read it from database. However, if you want to create a report using Payment Methods (e.g., total amount paid by each payment method), you have to manually exclude "Select One" as a payment method. Doing this may be more complex than adding this item into DropDownList list items:

Inserting an item to the Items collection of a DropDownList control, does not automatically create a value. Therefore, you have to find a new item by text and set a value for it. Because the new item is inserted at the top of the list items, setting SelectedIndex to 0 selects this item:

Using an extra item as "Select One" also facilitates validation. Our CompareValidator is named as cvddlPMethodF in the footer section, and compares the selected value of DropDownList ddlPMethodF with 0. The selected value must be greater than zero, ensuring the user has selected an item from Payment Methods except "Select One".

The workings of the Payment Method DropDownList is not very different in the edit mode. The list of items still comes from the database, but since the user edits a previously recorded entry, the value of Payment Method must be preselected when the DataGrid enters the edit mode for the selected row. The code is same until this point:

However, the rest requires a small change in code as follows. First, get the values of the current row as a DataRowView:

Second, get the value of the Payment Method for this row, and then find and select this value from the DropDownList's list items:

Next, I'll focus on the Currency DropDownList, which is the list of manually entered items. The TemplateColumn definition for the Currency column is as follows:

In the add mode, the Currency Information is displayed in a Label control (lblExpenseCurrency in the ItemTemplate section). However, you may be surprised to see DataBinder.Eval(Container,"DataItem.ExpenseCurrencyText", "") instead of DataBinder.Eval(Container,"DataItem.ExpenseCurrency", "") in the Text property of Label control. When a new record is added to the Expenses table, we only write the value of the selected Currency. However, in add mode, we want to display Currency as text. Therefore, we need to translate the Currency value to a text value. This conversion is performed in our stored procedure while reading expenses as follows:

The Footer section holds a DropDownList control (ddlExpenseCurrencyF in the FooterTemplate section) for users to select a currency while adding a new record. In the edit mode, Currency is also presented in a DropDownList control. The list items of both controls are hard coded during design time. Selecting Currency during edit mode, also requires some code similar to the code for Payment Methods in the ItemDataBound event handler of the DataGrid:

The last issue is the ADD button in add mode. The ADD button is a defined footer section of the delete button. However, placement of the button doesn't have particular importance. You can place it in the footer section of any of the columns. Clicking the ADD button invokes the ItemCommand event handler, which reads current values from the row and writes to Expenses table:

As mentioned above, the code behind page (expenses.aspx.vb) handles all user events and database operations. The page is properly commented; examining the code will ensure a better understanding of handling DropDownLists (database- and manually-driven) in a DataGrid.