Providing Data Editing Capability in a DataGrid Control

We can enable the user to edit data in a DataGrid or DataList control. Typically, we accomplish this by employing the OnEditCommand, OnCancelCommand, and OnUpdateCommand properties. If needed, we can also use the OnDeleteCommand property of a DataGrid control to allow deletion of a selected record.The OnDeleteCommand property is not available in a DataList. In this example, we will illustrate how to allow data editing capability to the user.The run-time view of the application is shown in Figure 3.86.
Figure 3.86 Editing Data in a DataGrid Control

The code for this application is shown in Figure 3.87.We have a number of major issues to cover here. First, we have used four additional properties of the DataGrid as shown in the following code excerpt:
DataKeyField="ProductId" OnEditCommand="setEditMode"
OnCancelCommand="cancelEdit" OnUpdateCommand="updateDataBase"
As you can see from the previous code, we have set the OnEditCommand property to a subprocedure named setEditMode.When we specify such a property, the data grid automatically places a ButtonList control captioned as “Edit” in the first column of the displayed table. On the click of this ButtonList, the control triggers the OnEditCommandEvent and passes a DataGridCommandEventArgs parameter to the wired-up event procedure (in this case, to the setEditMode procedure). In our setEditMode subprocedure, we have simply placed the clicked row in the edit mode as follows:
Sub setEditMode(s As Object, e As DataGridCommandEventArgs)
dataGrid1.EditItemIndex= e.Item.ItemIndex
bindDataGrid
End Sub
When the Edit button is clicked, the data grid also displays the Update and Cancel buttons automatically. Furthermore, the editable columns in the clicked row (item) are replaced with textboxes.The user can enter appropriate data in these textboxes and subsequently click the Update or Cancel button.
Second, on the click event of the Update button, we need to update the database. But how would we know which record in the database to update? This is why we have used the DataKeyField property (in the DataGrid tag) to identify the ProductId field as the key field. Our primary objective is to prepare an appropriate SQL Update statement like UPDATE Products SET ProductName=‘givenName’, Price=‘givenPrice’ WHERE ProductID=‘selectedProductId’.When the Update procedure is triggered, it is passed with a DataGridCommandEnentArgs-type parameter.We can retrieve the key value of the clicked row as dataGrid1.EditItemIndex= e.Item.ItemIndex.
Getting the value of the key field is not enough.We will also have to know the new values of the other edited columns.The desired values can be retrieved using the DataGridCommandEventArgs, too. For example, the ProductName field happens to be the second cell of the selected row.The Controls(0) of a given Cell of an Item object contains the value. But the parameter was passed to the routine as an object.Thus, we need to cast the Controls(0) to a textbox type, so that we can extract its Text data.The following statement will capture the new data in the ProductName column and will place it in a string varianble. Once we have done all these things, it is just a matter of building the necessary SQL string for the appropriate UPDATE query.
strPName=(CType(e.Item.Cells(2).Controls(0), Textbox)).Text
An UPDATE query is typically executed by using the ExecuteNonQuery method of a Command object (to be learned in the database chapter).This is what we did here. Finally, we need to set the edit-mode off.We have done this with the dataGrid1.EditItemIndex= –1 statement. Obviously, we do not want the user to edit the primary key.Therefore, we have set the ReadOnly property of the ProductID column to True.
Figure 3.87 Editing in DataGrid (DataGrid6.aspx)
<!— Chapter3/DataGrid6.aspx —>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" Debug="true" runat="server">
Sub Page_Load(Source As Object, E As EventArgs)
If Not IsPostBack Then
bindDataGrid
End If
End Sub
Sub bindDataGrid
Dim myConn As OleDbConnection
Dim myOleDbAdapter As OleDbDataAdapter
Dim connStr, sqlStr As String
Dim myDataSet As New Dataset
connStr="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Products.mdb"
sqlStr="SELECT ProductId, ProductName, Price " _
+ " FROM Products WHERE Price > 40 ORDER BY ProductId"
myConn= New OleDbConnection(connStr)
myConn.Open()
myOleDbAdapter =New OleDbDataAdapter(sqlStr,myConn)
myOleDbAdapter.Fill(myDataSet,"dtProducts")
dataGrid1.DataSource=myDataSet.Tables("dtProducts")
dataGrid1.DataBind()
myConn.Close()
End Sub
Sub setEditMode(s As Object, e As DataGridCommandEventArgs)
dataGrid1.EditItemIndex= e.Item.ItemIndex
bindDataGrid
End Sub
Sub cancelEdit(s As Object, e As DataGridCommandEventArgs)
dataGrid1.EditItemIndex=-1
bindDataGrid
End Sub
Sub updateDatabase(s as Object, e As DataGridCommandEventArgs)
Dim myConn As OleDbConnection
Dim connStr, sqlStr, strPName As String
Dim myUpdateCommand As OleDbCommand
Dim intPid As Integer
Dim dblPrice As Double
' Get the key-value of the clicked row
intPid=dataGrid1.DataKeys.Item(e.Item.ItemIndex)
' Get the new value of ProductName
strPName=(CType(e.Item.Cells(2).Controls(0), Textbox)).Text
' Get the new value of Price
dblPrice=cDbl((CType(e.Item.Cells(3).Controls(0), Textbox)).Text)
' Build the SQL
sqlStr="UPDATE Products SET ProductName=' " + strPName _
+ " ', Price=" + dblPrice.ToString _
+ " WHERE ProductID=" + intPid.ToString
connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Products.mdb"
myConn= New OleDbConnection(connStr)
myConn.Open()
myUpdateCommand=New OleDbCommand(sqlStr, myConn)
' Execute the Update SQL statement
myUpdateCommand.ExecuteNonQuery
myConn.Close()
dataGrid1.EditItemIndex=-1
BindDataGrid
End Sub
</script>
<html><head></head><form runat="server">
<asp:DataGrid id="dataGrid1" AutoGenerateColumns="False"
DataKeyField="ProductId" OnEditCommand="setEditMode"
OnCancelCommand="cancelEdit" OnUpdateCommand="updateDataBase"
CellPadding="2" Font-Name="Verdana" Font-Size="8pt" runat="server">
<HeaderStyle Font-Size="8" Font-Names="Arial" Font-Bold="True"
BackColor="Yellow" HorizontalAlign="center"></HeaderStyle>
<Columns>
<asp:EditCommandColumn EditText="Edit"
UpdateText="Update" CancelText="Cancel">
</asp:EditCommandColumn>
<asp:BoundColumn HeaderText="Product ID" DataField="ProductId"
ReadOnly="True" />
<asp:BoundColumn HeaderText="Description" DataField="ProductName"/>
<asp:BoundColumn HeaderText="Unit Price" DataField="price"
DataFormatString="{0:c}" />
</Columns>
</asp:DataGrid></form></html>

posted by Mr.Q at 4:30 AM