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 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">
<ItemTemplate>
<asp:Label runat=server Text='<%# BindJob(Container.DataItem) %>' ID="Label1">
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList Runat="server" ID="ddlJob"
DataSource="<%# Employees1 %>" DataMember="jobs" DataValueField="job_id"
DataTextField="job_desc" />
</EditItemTemplate>
</asp:TemplateColumn>
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"
OnCancelCommand="CancelCommand">
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)
Rebind()
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
Rebind()
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.
