To read the article online, visit http://www.4GuysFromRolla.com/articles/071002-1.3.aspx

An Extensive Examination of the DataGrid Web Control: Part 6

By Scott Mitchell


  • Read Part 1
  • Read Part 2


  • 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 second TableCell 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:

    <asp:DataGrid id="dgProducts" runat="server"
       ...   
       OnUpdateCommand="dgProducts_Update"
       OnCancelCommand="dgProducts_Cancel"
       ... >
            
       <Columns>
           ...
       </Columns>        
    </asp:DataGrid>
    

    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.

    Happy Programming!

    By Scott Mitchell


    Attachments


  • Download the complete code and database (in ZIP format)

  • Article Information
    Article Title: ASP.NET.An Extensive Examination of the DataGrid Web Control: Part 6
    Article Author: Scott Mitchell
    Published Date: July 10, 2002
    Article URL: http://www.4GuysFromRolla.com/articles/071002-1.3.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers