By: John Kilgo
The data choices for some database table columns are relatively fixed. Examples would be countries, states, and counties for instance. In these cases a dropdown list of fixed choices makes more sense than keyboard input where mistakes can be made easily. In an editable datagrid you can include dropdownlistboxes, although they must be populated at run time. You cannot do it at design time. In order to accomodate the dropdownlistbox the datagrid must use TemplateColumns with ItemTemplates.
For this article I have chosen to present selected data from the Northwind Customers table. The data includes a country column that is perfect for presentation with a dropdownlistbox when in edit mode.
As is usually the case in DotNetJohn articles, we will separate code from presentation by using an aspx page with a .vb code-behind page. The aspx page is presented first. The first part of the page, presented below, just defines our datagrid. There is nothing unusual about the design of the datagrid except that we setup for OnEditCommand, OnUpdateCommand, and OnCancelCommad event handlers. These are necessary for any datagrid to be placed in edit mode. The actual event handlers will be in our code-behind page.
<%@ Page Language="vb" Src="DataGridDropDown.aspx.vb" Inherits="DataGridDropDown" %>
<html>
<head>
<title>DataGrid with a DropDownListBox</title>
</head>
<body>
<form runat="server" ID="Form1">
<h3>DataGrid with a DropDownListBox</h3>
<asp:DataGrid ID="dtgCustomers" runat="server"
AutoGenerateColumns=False
HeaderStyle-BackColor="IndianRed"
HeaderStyle-Font-Bold="True"
HeaderStyle-Font-Name="Verdana"
HeaderStyle-Font-Size="12px"
HeaderStyle-ForeColor="White"
ItemStyle-BackColor=Gainsboro
ItemStyle-Font-Name="verdana"
ItemStyle-Font-Size="12px"
OnCancelCommand="dtgCustomers_Cancel"
OnEditCommand="dtgCustomers_Edit"
OnUpdateCommand="dtgCustomers_Update"
Width="75%">
Next comes our TemplateColumns. We use asp:labels to hold data for presentation. All of this is pretty straight forward until we come to the code marked in blue. In the first few lines we have our ItemTemplate wherein the country will be shown when the datagrid is in presentation mode (i.e. not in edit mode). In this respect it is just like all of the other columns in the grid. We then include a label with its visible property set to false. This invisible label holds the CustomerID column. It is not needed for presentation but will be needed as a key field for our WHERE clause when updating the row. It could have been included elsewhere in the grid, but logically belongs with the section where data will be updated. (For purposes of this article only the country column will be updateable, although all columns could have been eligible for update.) Notice that we have made arrangements to present the country in two different places. The first is for normal presentation mode, while the second is so that we can see the existing value while we are in edit mode with the dropdownlistbox showing.
Finally comes our dropdownlist box. It is made ready by the line: DataSource="<%# BindTheCountry() %>". While we cannot populate the dropdown at design time, we can reference a function (BindTheCountry()) that will fill the dropdown at run time. BindTheCountry() will be seen in the code-behind page.
<Columns>
<asp:EditCommandColumn EditText="Edit"
CancelText="Cancel"
UpdateText="Update"
ItemStyle-Width="100px"
HeaderText="Commands" />
<asp:TemplateColumn HeaderText="Company Name">
<ItemTemplate>
<asp:Label ID="lblCompanyName"
Text='<%# DataBinder.Eval(Container.DataItem, "CompanyName") %>'
Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Contact Name">
<ItemTemplate>
<asp:Label ID="lblContactName"
Text='<%# DataBinder.Eval(Container.DataItem, "ContactName") %>'
Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Contact Title">
<ItemTemplate>
<asp:Label ID="lblContactTitle"
Text='<%# DataBinder.Eval(Container.DataItem, "ContactTitle") %>'
Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblCountry"
Text='<%# DataBinder.Eval(Container.DataItem, "Country") %>'
Runat="server" />
</ItemTemplate>
<EditItemTemplate>
<asp:Label runat="server"
id="lblCustomerID"
Visible="False"
Text='<%# DataBinder.Eval(Container.DataItem, "CustomerID") %>'/>
<asp:Label ID="lblTempState"
Text='<%# DataBinder.Eval(Container.DataItem, "Country") %>'
Runat="server" />
<asp:DropDownList id="ddlCountry"
DataSource="<%# BindTheCountry() %>"
OnPreRender="SetDropDownIndex"
DataTextField="Country"
DataValueField="Country"
runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Telephone">
<ItemTemplate>
<asp:Label ID="lblPhone"
Text='<%# DataBinder.Eval(Container.DataItem, "Phone") %>'
Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>
The remainder of the code above completes the layout of our datagrid.
Next is our code-behind file where all the work gets done. Much of the code is the usual database access code to populate the datagrid. The code immediately below contains the Page_Load event where the grid is first bound to its data, the BindTheGrid() routine to actually accomplish the grid's databinding, the Edit event handler to place the grid in edit mode, and the Cancel event handler to get the grid out of edit mode without performing an update. These are necessary routines for any datagrid being presented, and placed in edit mode.
Please note: In the .aspx page and in both sections of the code-behind file being shown here, the lines displayed in purple constitue code that allows the dropdownlist to have the current country value preselected when the grid is placed in edit mode. This code was contributed by Victor Rubba after this article was originally published. I wish to thank Victor for his contribution.
Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Public Class DataGridDropDown : Inherits Page
Protected WithEvents dtgCustomers As System.Web.UI.WebControls.DataGrid
Protected strCountry As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
BindTheGrid()
End If
End Sub
Public Sub BindTheGrid()
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdCustomers As SqlCommand = New SqlCommand("SELECT Top 10 CustomerID, " _
& "CompanyName, ContactName, ContactTitle, Country, Phone " _
& "FROM Customers", objConn)
cmdCustomers.CommandType = CommandType.Text
objConn.Open()
dtgCustomers.DataSource = cmdCustomers.ExecuteReader()
dtgCustomers.DataBind()
objConn.Close()
End Sub
Sub dtgCustomers_Edit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
strCountry = CType(E.Item.FindControl("lblCountry"), Label).Text
dtgCustomers.EditItemIndex = CInt(E.Item.ItemIndex)
BindTheGrid()
End Sub
Public Sub dtgCustomers_Cancel(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
dtgCustomers.EditItemIndex = -1
BindTheGrid()
End Sub
The last three routines, shown below, contain the code to populate our dropdownlistbox, to update the table, and to preselect the existing country in the dropdownlist. Remember on our aspx page we could not populate the dropdownlistbox, but we could make room for it and call a function that would populate it at runtime? BindTheCountry() is shown first below. BindTheCountry() connects to a database table and executes a reader which is returned to the aspx page as the dropdownlistbox's data source. Normally we would access a codes table containing countries. Since such a table was not easily available to me, I just did a SELECT Distinct Country from the Customer's table to have something with which to populate the dropdownlistbox.
Sub dtgCustomers_Update does the updating of the Customers table with the country value selected from the dropdownlistbox. Notice that we must use the FindControl method to locate the dropdownlist box (ddlCountry) and then get the SelectedItem.Value property to find the actual country selected from the dropdown. We also use the hidden label on the datagrid (mentioned in the aspx file discussion) to obtain the CustomerID column to use as a key field in our WHERE clause for the UPDATE.
Public Function BindTheCountry()
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdCustomers As SqlCommand = New SqlCommand("SELECT Distinct Country FROM Customers", objConn)
cmdCustomers.CommandType = CommandType.Text
objConn.Open()
Return cmdCustomers.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Public Sub dtgCustomers_Update(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmdCustomers As SqlCommand
Dim strCustomerID As String = CType(e.Item.FindControl("lblCustomerID"), Label).Text
Dim strCountry As String = CType(e.Item.FindControl("ddlCountry"), DropDownList).SelectedItem.Value
Dim strSql As String
strSql = "UPDATE Customers SET Country='" & strCountry _
& "' WHERE CustomerID='" & strCustomerID & "'"
cmdCustomers = New SqlCommand(strSql, objConn)
objConn.Open()
cmdCustomers.ExecuteReader(CommandBehavior.CloseConnection)
dtgCustomers.EditItemIndex = -1
BindTheGrid()
End Sub
Public Sub SetDropDownIndex(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ed As System.Web.UI.WebControls.DropDownList
ed = sender
ed.SelectedIndex = ed.Items.IndexOf(ed.Items.FindByText(strCountry))
End Sub
End Class
A fair amount of code has been presented, but I believe if you will run the demo program several times to see how the editing works in the grid, and then compare what you see to the various sections of code, you will come away with an understanding of how the process works.
Once again, thanks to Victor Rubba for his code contributions (listed in purple) for this article.
You may run the program here.
you may download the code here.
