Updating a Row
Supporting editing and UPDATEs is a bit more complicated. The DataGrid has
built in support for an edit row and "Edit", "Update",
and "Cancel" buttons. You can register the event handlers for these
buttons with the "OnUpdateCommand", "OnEditCommand",
and "OnCancelCommand" properties. The DataSet has built in support
for Optimistic Concurrency by storing both the @original values and pending
update values. Finally, the SqlAdapter supports Optimistic Concurrency by
generating SQL UPDATE statements that look for unaltered records using the
@original values stored in the DataSet. However, if you want to implement
Optimistic Concurrency, you will need to persist the DataSet in the ViewState
or Session. In this project, the DataSet is _not_ persisted. Instead the
DataSet is filled on each page load.
Register the Edit, Update and Cancel Event Handlers
If you look at the file WebForm1.aspx in the HTML view you will see where
the OnUpdateCommand, OnEditCommand and OnCancelCommand property is set
to the
appropriate event handler. The DataGrid will use these properties to register
the button event handlers:
<asp:datagrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 23px; POSITION:
absolute; TOP: 221px" runat="server"
DataKeyField="au_id" DataSource="<%# view %>" Height="270px" Width="679px"
OnUpdateCommand="DataGrid1_Update" OnCancelCommand="DataGrid1_Cancel" OnEditCommand="DataGrid1_Edit"
OnDeleteCommand="DataGrid1_Delete" BorderColor="Blue" OnItemCommand="Item_Click" AllowSorting="True"
OnSortCommand="DataGrid1_Sort"
AllowPaging="True" OnPageIndexChanged="DataGrid1_Page" BackColor="#C0FFFF">
Add an Edit, Update and Cancel Buttons to the DataGrid
Using the Design view, you can add a "Edit", "Update" and "Cancel" button
to the DataGrid by right clicking on the DataGrid and choosing the property
builder. Under "Columns", expand the "Available Column" "Button
Column". You will see a "Edit, Update, Cancel" button that can
be added to the "Selected Columns". Don't forget to "Apply" any
changes.
Add the Event Handlers to WebForm1.aspx.cs
Finally, you need to add the Edit, Update and Cancel event handlers with
the proper signature to the WebForm1.asp.cs file. Here is the implementation
of the DataGrid1_Edit event handler:
protected void DataGrid1_Edit(Object sender, DataGridCommandEventArgs
e)
{
DataGrid1.EditItemIndex = e.Item.ItemIndex;
ResetPageIndex(DataGrid1,view);
DataGrid1.DataBind();
}
This function simply sets the edit row index and then refreshes the DataGrid
from the View.
Here is the DataGrid1_Cancel event handler:
protected void DataGrid1_Cancel(Object sender, DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = -1;
ResetPageIndex(DataGrid1,view);
DataGrid1.DataBind();
}
This function simply sets the edit row index to -1, disabling editing, and
then refreshes the DataGrid from the View.
Here is the DataGrid1_Update event handler:
protected void DataGrid1_Update(Object sender, DataGridCommandEventArgs
e)
{
string debug="No Errors On Update.";
int numCols = e.Item.Cells.Count;
// Gets the value of the key field of the row being updated
string key = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
// Finds the row in the dataset table that matches the
// one the user updated in the grid. This example uses a
// special Find method defined for the typed dataset, which
// returns a reference to the row.
DataRow dr= dataSet11.authors.FindByau_id(key);
// Update the dataSet. Skip non data columns in data grid.
// Note use of generic index, not column names.
try
{
for (int i=NUM_LINK_COLUMNS; i<numCols; i++) //skip
non data columns
{
String colvalue =((TextBox)e.Item.Cells[i].Controls[0]).Text;
dr[i-NUM_LINK_COLUMNS]= colvalue;
}
sqlDataAdapter1.Update(dataSet11);
DataGrid1.EditItemIndex = -1; // leave user on edit
if update fails
}
catch (Exception exc)
{
debug= exc.Message;
}
// Refresh the grid
ResetPageIndex(DataGrid1,view);
DataGrid1.DataBind();
textBoxMessage.Text= debug;
}
As you can see, the Update event handler is a bit more complicated. Note the
use of a generic index based scheme for reading data from the edit row and
writing the new data to the appropriate row in the DataSet.
Since the data in the DataGrid may not be synchronized with the data in the
DataSet, you must find the proper row in the DataSet using the primary key
of the selected row. You can retrieve the selected row's primary key by calling:
string key = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
This works only if you have declared the primary key field in the DataGrid
by setting the "DataKeyField" to "au_id". Once you have
the primary key of the selected row in the DataGrid, you can find the corresponding
row in the DataSet by calling:
DataRow dr= dataSet11.authors.FindByau_id(key);
In order to implement a generic iteration scheme, you need to know the number
of data columns in the DataGrid. You can get total number of columns (action
columns and data columns) in the DataGrid by calling:
int numCols = e.Item.Cells.Count;
The number of action columns is set using a static variable declared at the
top of the WebForm1.aspx.cs file:
// There are three non data columns in the data grid
protected const int NUM_LINK_COLUMNS= 3;
You need to edit this value if you add or delete action columns from the
DataGrid. Knowing these two values, you can now iterate over the data fields
in the DataGrid
and insert the new values into the DataRow dr:
for (int i=NUM_LINK_COLUMNS; i<numCols; i++) //skip non data columns
{
String colvalue =((TextBox)e.Item.Cells[i].Controls[0]).Text;
dr[i-NUM_LINK_COLUMNS]= colvalue;
}
The DataSet now contains both the @original and new values. To commit the
changes to the database, you call Update():
sqlDataAdapter1.Update(dataSet11);
Now you must disable editing in the DataGrid by setting the edit row index
to -1:
DataGrid1.EditItemIndex = -1;
Finally, you refresh the data in the DataGrid to reflect the Update by calling
DataBind():
// Refresh the grid
ResetPageIndex(DataGrid1,view);
DataGrid1.DataBind();
As usual, any exceptions are trapped and the error message returned to the
user in a multi-line text box.