Exploiting relations in a DataGrid (II)

In the previous tip, I demonstrated how to use a DataRelation object (created automatically through by using the XML Designer for the DataSet) to display related data from two tables. I noted that the advantage of

    Requires Free Membership to View

this technique would not be immediately obvious, but in this tip, I show how it can be extremely useful.

Suppose you want to extend the example from the previous tip to allow a user to edit Employee data. When the DataGrid is in edit mode, a textbox will by default appear in each of the cells, allowing the user to modify each column's data for the currently edited row. But what if I don't want a textbox for the Job column? What if I want to use a drop down list box that displays all the possible jobs. This would be a much more user-friendly approach to allowing the user to edit this data, instead of just using the JobID or the JobDesc field displayed in a textbox. (There was a recently published book that said this couldn't be done, but I'll show you how...)

See Figure 1.

First, you'll need to start with the source code from the previous article to get the "before" code, then download this tip's source to inspect the "after" code. I'll merely comment on those lines of code that have been added and what is significant about them.

Also, if you want to see an instructional screen-cam video that demonstrates how to build this example, visit www.LearnVisualStudio.NET and join the Supporters area for this and 20 hours of additional .NET videos.

The key to this technique is modifying the HTML in the .ASPX file. You'll modify the TemplateColumn for Job Position to include the drop down list box:

<asp:TemplateColumn HeaderText="Job Position">
  <asp:Label runat=server Text='<%# BindJob(Container.DataItem) %>' ID="Label1">
  <asp:DropDownList Runat="server" ID="ddlJob"
 DataSource="<%# Employees1 %>" DataMember="jobs" DataValueField="job_id"
 DataTextField="job_desc" />

Notice that we bind the DataSource to our instance of the DataSet, set the DataMember to "jobs and select the corresponding DataValueField and DataTextField. This is important.

Also, since we're adding the ability to edit the row inline, we'll update the DataGrid reference:

<asp:DataGrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 24px; POSITION:
 absolute; TOP: 48px" runat="server" DataMember="employee" DataSource="<%# 
Employees1 %>" Width="836px" Height="104px" DataKeyField="emp_id" 
AutoGenerateColumns="False" BorderColor="#E7E7FF" BorderStyle="None" 
BorderWidth="1px" BackColor="White" CellPadding="3" GridLines="Horizontal" 
OnEditCommand="EditCommand" OnDeleteCommand="DeleteCommand" 
OnUpdateCommand="UpdateCommand" OnItemDataBound="ItemDataBound" 

Take note of the OnEditCommand, OnDeleteCommand, and the other "On" properties. These map events that happen in the DataGrid to methods we'll implement in code.

Also, we'll need to add two columns to allow a user to begin Edit mode, then while in edit mode Update or Cancel the changes, as well as a Delete column. We do that by adding two additional columns to the DataGrid:

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

<asp:ButtonColumn Text="Delete" CommandName="Delete"></asp:ButtonColumn>

The next task is to add five methods in the code-behind that handle the events raised by the DataGrid. The method names must correspond to the "On" properties of the DataGrid as set in the ASPX file. There are two in particular that I'll address in this tip.

The first is the ItemDataBound which handles the OnItemDataBound event. Typically, you would want to handle the OnItemEdit event for all functionality pertaining to the Edit mode of a DataGrid, but the OnItemEdit happens in sequence before the drop down list box is data bound to the Jobs table. This would make it impossible to locate and select the entry in the drop down list box for the employee's current job. Therefore, the proper event to handle when the data binding occurs is the OnItemDataBound event.

It's important to get the method signature correct (the compiler will keep you honest) because you use the DataGridItemEventArgs argument to get a reference to the current row. Once inside the row you'll check to see if it is in "Edit" mode, and if so, you'll use the argument to get a reference to the drop down list box control (ddlJob) and the DataRowView, a reference to the row's corresponding data bound DataRow.

    Public Sub ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs)

        Dim lit As ListItemType = e.Item.ItemType

        If lit = ListItemType.EditItem Then

            Dim ddlJob As DropDownList
            ddlJob = e.Item.FindControl("ddlJob")

            Dim drv As DataRowView = e.Item.DataItem

            If Not drv Is Nothing Then
                ddlJob.Items.FindByValue(drv("job_id")).Selected = True
            End If

        End If

    End Sub

The next method we'll discuss is the UpdateCommand which handles the OnUpdateCommand event. This event fires when in Edit mode, the user selects the "Update" button link. What is significant in this method is that you MUST call the Rebind() function (created in the previous tip) which fills the DataSets and binds them to the DataGrid. After all, you can't update a row in the DataSet if the DataSet is empty. This time the DataGridCommandEventArgs are important, because we'll get a reference to the current DataSetIndex, or rather the Row in the DataSet that is linked to the row currently being edited, as well as the ddlJob drop down list box. Once we update the job_id with the drop down list box's selected item, we have to do a little "hokey pokey" with the job_lvl/min_lvl fields because of a constraint added to the pubs database. Finally, we can call the Update statement for our DataSet's corresponding DataAdapter, and set the EditItemIndex = -1 which essentially ends Edit mode.

    Public Sub UpdateCommand(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)


        Dim dr As Employees.employeeRow = Employees1.employee.Item(e.Item.DataSetIndex)

        Dim ddlJob As DropDownList
        ddlJob = e.Item.FindControl("ddlJob")

        dr.job_id = ddlJob.SelectedItem.Value

        ' Due to the constraint that is application specific
        ' to the pubs database, I had to get the min_lvl
        ' for the selected job, and place that into the 
        ' job_lvl fields, else I get an exception.
        dr.job_lvl = dr.jobsRow.min_lvl

        ' I don't have to Update here, but since my dataset
        ' is not in memory (session, cache, viewstate, etc.)
        ' not updating here would lose the changes I just made.
        SqlDataAdapter1.Update(Employees1, "employee")

        DataGrid1.EditItemIndex = -1


    End Sub

This opens up some exciting possibilities with related tables in DataSets, and how you can modify their contents using the control type that makes the most sense for that situation. It's also interesting to see the depth of information you're able to access through the arguments that are passed into the event handlers for the DataGrid.

Don't forget change the DataSource in the SqlConnection1 object to your pubs Database and server!

In the next tip, I'll show how to create a grid inside a grid... in other words, allow you to drill down from one data table represented in a DataGrid to another related table represented in a DataGrid. Now THAT is cool!

About the Author

Robert Tabor is a Microsoft Certified Professional in Visual Basic with over six years of experience developing n-tier Microsoft-centric applications for some of the world's most prestigious companies and consulting organizations, such as Ernst & Young, KPMG, Cambridge Technology Partners, Sprint, American Heart Association, and the Mary Kay Corporation. Bob is the author of Microsoft .NET XML Web services by Sams Publishing, and contributes to SoapWebservices.com and LearnVisualStudio.NET. He is currently working on initiatives within Mary Kay, the second largest eCommerce site in retail volume on the net, of how to utilize .NET within their e-business group.

This was first published in May 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.