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.
Comments