Making a Datagrid Row Editable

Two of the most popular methods of editing a datagrid in asp.net are to either select the row and take the user off to a different presentation of the data, or to change the formatting of the row presented in the database with appropriate edit text boxes, checkboxes and radio buttons. It is this second method which we shall be covering in this article.

We require the following in the datagrid definition

<Asp:DataGrid id="MyDataGrid" runat="Server"
 DataKeyField="CustomerID"
 OnEditCommand="MyEditAction"
 OnUpdateCommand="MyUpdateAction"
 OnCancelCommand="MyCancelAction"
 OnDeleteCommand="MyDeleteAction"
 AutoGenerateColumns="False">
</Asp:DataGrid>

Within the datagrid definition on the .ascx page we need to add reference to the edit funtions:

<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:TemplateColumn>
  <ItemTemplate>
    <asp:LinkButton runat="server" ID="Delete" Text="Delete" CommandName="Delete" CausesValidation="false">
  </asp:LinkButton> </ItemTemplate>
</asp:TemplateColumn>

In our code behind page we require the functions to handle the row editing:

MyEditAction

Enables editing of the datagrid row

Sub MyEditAction(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
    Try
      MyDataGrid.EditItemIndex = e.Item.ItemIndex
      BindMyDataGrid()
    Catch
      'msgProducts.Text = "Error in update"
    End Try
End Sub 'MyEditAction

MyCancelAction

Canel the editing and return the formatting of the row back to normal.

Sub MyCancelAction (ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
  MyDataGrid.EditItemIndex = -1
  BindMyDataGrid()
End Sub 'MyCancelAction

MyUpdateAction

Causes the row to be updated and returns the datagrid back to normal in its presentation

Sub MyUpdateAction(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
  If e.CommandName = "Update" Then
    Try
      Dim Purchasing_id As Integer = e.Item.Cells(0).Text
      Dim Purchasing_Price As String = CType(e.Item.FindControl("purchasingPrice"), TextBox).Text
      Dim Purchasing_Quantity As String = CType(e.Item.FindControl("purchasingQuantity"), TextBox).Text
      Dim myConnection As SqlConnection
      Dim myCommand As SqlCommand
      Dim myDataReader As SqlDataReader
      Dim strSQL As String strSQL = "
      strSQL = "UPDATE products SET name = '" & Products_name & "' WHERE ID = " & Products_id
      myConnection = New SqlConnection(ConfigurationSettings.AppSettings("MyDSN"))
      myCommand = New SqlCommand(strSQL, myConnection)
      Dim myDataSet As DataSet myConnection.Open()
      myDataReader = myCommand.ExecuteReader()
      myConnection.Close() 'Rebind the DataGrid
      MyDataGrid.EditItemIndex = -1
      BindMyDataGrid()
    Catch
      'msgProducts.Text = "Error in update"
    End Try
  End If
End Sub 'MyUpdateAction

MyDeleteAction

Deletes the row of data from the database.

Sub MyDeleteAction(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
  Try
    MyDataGrid.EditItemIndex = -1
    If e.CommandName = "Delete" Then
        '
        'Delete row from datagrid
        '
    End If
    BindPurchasing()
  Catch
    'msgProducts.Text = "Error"
  End Try
End Sub 'MyDeleteAction

NAT September 2005 (Revised)