Including a Data Bound CheckBox in an Editable DataGrid...

I've had several requests to show how a data bound checkbox could be included in an editable datagrid. This article shows how to bind a bit field from a table to a checkbox in a datagrid as well as how to update the table with changes in the checked state of the checkbox.

By: John Kilgo Date: July 29, 2003 Download the code.

Despite the lack of information available on the web about how to include a databound checkbox in a datagrid, it turns out that it is really not very hard to do. As you can see in the .aspx page below, all it takes to include the checkbox in the datagrid is an <asp:ItemTemplate>. An <ItemTemplate> encases the <asp:Checkbox which is disabled until the Edit linkbutton is clicked. That is when the <EditItemTemplate comes into play. The only thing different about it, besides having a different ID, is it enables the <asp:Checkbox.

Notice that we are setting the value to the bit value (0 or 1) of the Discontinued column in the Products table of the Northwind database. Even though the numeric value of a checked checkbox is -1, the positive 1 from the table still sets the checkbox to checked where appropriate. The .aspx file (DataGridCheckBox.aspx) is shown below.

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DataGridCheckBox.aspx.vb" Inherits="DotNetJohn.DataGridCheckBox"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DataGridCheckBox</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="form1" runat="server">
<asp:DataGrid id="dtgProducts"
              AutoGenerateColumns="False"
              runat="server"
              CellPadding="4"
              OnEditCommand="dtgProducts_Edit"
              OnCancelCommand="dtgProducts_Cancel"
              OnUpdateCommand="dtgProducts_Update"
              BorderColor="#336666"
              BorderStyle="Double"
              BorderWidth="3px"
              BackColor="White"
              GridLines="Horizontal">

  <ItemStyle ForeColor="#333333" BackColor="White" />

  <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#336666" />

  <Columns>

    <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit" />

    <asp:TemplateColumn HeaderText="ProductID">
      <ItemTemplate>
        <asp:Label ID="lblProdID"
                   Text='<%# Container.DataItem("ProductID") %>'
                   Runat="server" />
      </ItemTemplate>
    </asp:TemplateColumn>

    <asp:TemplateColumn HeaderText="Product Name">
      <ItemTemplate>
        <%# Container.DataItem("ProductName") %>
      </ItemTemplate>
    </asp:TemplateColumn>

    <asp:TemplateColumn HeaderText="Discontinued">
      <ItemTemplate>
        <asp:CheckBox ID="DiscontinuedCheckBox" Enabled="False"
                      Checked='<%# Databinder.Eval(Container.DataItem, "Discontinued") %>'
                      runat="server" />
      </ItemTemplate>
      <EditItemTemplate>
        <asp:CheckBox ID="edit_DiscontinuedCheckBox" Enabled="True"
                      Checked='<%# Databinder.Eval(Container.DataItem, "Discontinued") %>'
                      Runat="server" />
      </EditItemTemplate>
    </asp:TemplateColumn>

  </Columns>

</asp:DataGrid>
</form>
</body>
</html>

The code behind file will be shown in two parts for ease of discussion. This first part is just the usual database access to obtain the data, plus the usual subroutines for putting the datagrid into edit mode and cancelling out of edit mode. I don't believe any further comment is needed.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.UI.WebControls

Public Class DataGridCheckBox
  Inherits System.Web.UI.Page

  Protected dtgProducts As System.Web.UI.WebControls.DataGrid
  Public strSql As String

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
      BindTheData()
    End If
  End Sub

  Public Sub BindTheData()
    Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    strSql = "Select Top 20 ProductID, ProductName, Discontinued From Products"
    Dim objCmd As SqlCommand = New SqlCommand(strSql, objConn)

    objConn.Open()

    dtgProducts.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
    dtgProducts.DataBind()
  End Sub

  Public Sub dtgProducts_Edit(sender As System.Object, e As DataGridCommandEventArgs)
    dtgProducts.EditItemIndex = e.Item.ItemIndex
    BindTheData()
  End Sub

  Public Sub dtgProducts_Cancel(sender As System.Object, e As DataGridCommandEventArgs)
    dtgProducts.EditItemIndex = -1
    BindTheData()
  End Sub

The second and last part of the code behind file is where the updating takes place. First we use the FindControl method to find the ProductID which we will need in our WHERE clause in our UPDATE statement. Next we use FindControl again to find our checkbox and get the Checked property. Notice that at the end of the line we multiply the result by -1. This is because if the checkbox is checked, a -1 gets returned. Since our database table wants a positive 1 if checked we have to multiply by -1. If the checkbox is unchecked a 0 is returned and the multiplation by -1 is of no consequence. We then build our UPDATE statement and execute the statement to update the database. That's all there is to it.

  Public Sub dtgProducts_Update(sender As System.Object, e As DataGridCommandEventArgs)
    Dim chkBoxChecked As Integer
    Dim ProductID As Integer

    ProductID = CType(e.Item.FindControl("lblProdID"),Label).Text
    chkBoxChecked = (CType(e.Item.FindControl("edit_DiscontinuedCheckBox"),CheckBox).Checked) * -1

    strSql = "Update Products Set Discontinued=" & chkBoxChecked & " Where ProductID=" & ProductID

    Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim objCmd As SqlCommand = New SqlCommand(strSql, objConn)

    objConn.Open()
    objCmd.ExecuteNonQuery()
    objConn.Close()
    dtgProducts.EditItemIndex = -1
    BindTheData()
  End Sub

End Class

I believe this code to be fairly straight forward. If you get lost, just go over the code one line at a time and imagine what the program is doing at each step along the way.

You may download the code here.