In Part 2 we looked at the needed code for selecting a row to
edit. In this part we'll examine the code needed to update the edited row and to allow the user to cancel
their editing.
The Cancel Button
As aforementioned, when a particular row is selected for editing the "Edit" button is replaced with an
"Update" and a "Cancel" button. The semantics of the "Cancel" button is to return the DataGrid to its
non-editing state without saving any changes. Like the "Edit" button, the "Cancel" button,
when clicked, fires an event, specifically the CancelCommand event. The event handler for
this event, which we'll need to write, will simply need to set the DataGrid back to its pre-editing
state. To accomplish this we simply need to set the EditItemIndex property back to -1 and
rebind the DataGrid (calling BindData()):
Sub dgProducts_Cancel(sender As Object, e As DataGridCommandEventArgs)
dgProducts.EditItemIndex = -1
BindData()
End Sub
The Update Button
Just like the "Edit" and "Cancel" buttons, the "Update" button, when clicked, trips an event, specifically
the UpdateCommand event. The code we have to write for this event handler is quite a bit
longer than a measly two lines of code, as was the case with the event handlers for the "Edit" and "Cancel"
button events. Our "Update" button's event handler must read in the values the user has entered into
the textboxes and then construct an applicable SQL statement.
The "Update" button's event handler accepts two incoming parameters, an Object and a
DataGridCommandEventArgs. The DataGridCommandEventArgs parameter contains
a property Item, which is an instance of the DataGridItem that corresponds
to the DataGrid row whose "Update" button was clicked. This DataGridItem object contains
a Cells collection, which can be interrogated to retrieve the text or controls at the various
columns of the DataGrid. We can use this DataGridItem object to determine the values
of the edited row's ProductID as well as the values the user has entered into the textboxes
for the price, name, and description.
Sub dgProducts_Update(sender As Object, e As DataGridCommandEventArgs)
'Read in the values of the updated row
Dim iProductID as Integer = e.Item.Cells(1).Text
Dim dblPrice as Double = CType(e.Item.Cells(2).Controls(0), TextBox).Text
Dim strName as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Dim strDesc as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
...
Note that the value of the ProductID, which was not a textbox in the edited mode (since it was
marked ReadOnly), can be retrieved by the Text property of e.Item.Cells(1).
Cells(1) is used to get the secondTableCell in the DataGrid row.
The first TableCell (which would be referenced via Cells(0)) is the column
that contains the "Update" and "Cancel" buttons.
Retrieving the price, name, and description is a bit more difficult due to the fact that the values
we are after are the textual content inside the textboxes inside the table cells. (Whew, try to say
that five times fast!) Hence we use the CType function to cast the first control
(Controls(0)) in the appropriate TableCell to a TextBox Web control;
then, we simply interrogate the TextBox control's Text property. (If you
are using C# you'd use casting like: ((TextBox) e.Items.Cells[2].Controls[0]).Text as opposed
to using CType, which is a VB.NET built-in function.)
Once we have these values we can construct our SQL UPDATE statement. Rather than using a dynamic
SQL statement as done in the previous DataGrid articles, I will use the parameterized form, which I
find to be a cleaner approach - feel free to use whatever approach you appreicate most.
...
'Construct the SQL statement using Parameters
Dim strSQL as String = _
"UPDATE [Products] SET [ProductName] = @ProdName, " & _
"[UnitPrice] = @UnitPrice, [ProductDescription] = @ProdDesc " & _
"WHERE [ProductID] = @ProductID"
Const strConnString as String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\GrocerToGo.mdb"
Dim objConn as New OleDbConnection(strConnString)
objConn.Open()
Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
myCommand.CommandType = CommandType.Text
' Add Parameters to the SQL query
Dim parameterProdName as OleDbParameter = _
new OleDbParameter("@ProdName", OleDbType.VarWChar, 75)
parameterProdName.Value = strName
myCommand.Parameters.Add(parameterProdName)
Dim parameterUnitPrice as OleDbParameter = _
new OleDbParameter("@UnitPrice", OleDbType.Currency)
parameterUnitPrice.Value = dblPrice
myCommand.Parameters.Add(parameterUnitPrice)
Dim parameterProdDesc as OleDbParameter = _
new OleDbParameter("@ProdDesc", OleDbType.VarWChar)
parameterProdDesc.Value = strDesc
myCommand.Parameters.Add(parameterProdDesc)
Dim parameterProdID as OleDbParameter = _
new OleDbParameter("@ProductID", OleDbType.Integer)
parameterProdID.Value = iProductID
myCommand.Parameters.Add(parameterProdID)
myCommand.ExecuteNonQuery() 'Execute the UPDATE query
objConn.Close() 'Close the connection
...
Now that the database has been updated with the user's edits, all that remains is to return the DataGrid
to its pre-editing form (by setting EditItemIndex to -1) and then rebinding the DataGrid
(via BindData()).
...
'Finally, set the EditItemIndex to -1 and rebind the DataGrid
dgProducts.EditItemIndex = -1
BindData()
End Sub
Finally, to wrap up the code, you'll need to specify that the CancelCommand and
UpdateCommand events be wired up to the appropriate event handlers by inserting the following
code into your DataGrid control:
Conclusion
As we saw in this article, the DataGrid control has uses beyond the simply presentation of data - it
can also be used to allow inline editing of said data. Furthermore, building such functionality into
a DataGrid is relatively simple, the only "real" code we had to write was in the "Update" button's
event handler. Still, this code is substantially less code than would have to be written in providing
such functionality in a classic ASP application.
