When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, December 15, 2004

An Extensive Examination of the DataGrid Web Control: Part 17, Part 2

By Scott Mitchell


  • Read Part 1 of this Article!

  • In Part 1 of this article we saw how to use TemplateColumns to create a column such that all records of the DataGrid provided an editable interface. In this second and final part, we'll see how to provide the user with an "Update All" Button that, when clicked, will commit all of the user's modifications to the database.

    - continued -

    Saving All of the DataGrid's Changes to the Database


    Now that we have a DataGrid with a completely editable interface, all that remains is writing a bit of code to iterate through the DataGrid's records and update the database accordingly. To accomplish this we'll add an "Update All" Button Web control to the page and add the necessary code in the Button Web control's Click event handler.

    We can loop through each row in the DataGrid by enumerating the DataGrid's Items collection. Each DataGrid row is represented by a DataGridItem class instance. For each item, we'll need to access each of its editable columns' values. We can do this through the FindControl() method, passing in the ID of the Web control for the column's editable interface. (For more on how to programmatically access the contents of a TemplateColumn, read: Accessing the Contents of a TemplateColumn.)

    For each record in the DataGrid, once we have read in the TemplateColumns' values we need to issue an UPDATE statement to save any changes made back to the underlying database table. To accomplish this we'll need to be able to have information that uniquely identifies each row. If the data you are displaying has a single primary key field, you can just use the DataGrid's DataKeyField property to track the unique ID for each record. The pseudocode for doing an "Update All" will look like the following:

    For Each record in the DataGrid
      Read in the values from the TemplateColumns
      
      Issue an UPDATE statement to the database
    Next
    

    The actual code isn't much more complex than the pseudocode, and is shown below. This code should be placed in the "Update All" Button Web control's Click event handler:

    'Create connection and command objects
    Dim myConnection as New SqlConnection(connection string)
    Dim myCommand as New SqlCommand(strSQL, myConnection)
    
    Dim dgi as DataGridItem
    For Each dgi in dgPopularFAQs.Items
      'Read in the Primary Key Field
      Dim id as Integer = Convert.ToInt32(dgPopularFAQs.DataKeys(dgi.ItemIndex))
      Dim question as String = CType(dgi.FindControl("txtDescription"), TextBox).Text
      Dim submittedBy as String = CType(dgi.FindControl("txtSubmittedBy"), TextBox).Text
          
      'Issue an UPDATE statement...
      Dim updateSQL as String = "UPDATE TableName SET Question = @Question, " & _
                  "SubmittedByName = @SubmittedByName WHERE FAQID = @ID"
      myCommand.Parameters.Clear()
      myCommand.Parameters.Add("@Question", question)
      myCommand.Parameters.Add("@SubmittedByName", submittedBy)
      
      myCommand.ExecuteNonQuery()
    Next
    
    [View a Live Demo!]

    When the user clicks the "Update All" Button, the page is posted back, the rows of the DataGrid are enumerated, and for each record an UPDATE statement is reissued to the database. (In the live demo no UPDATEs are issued; rather the output is merely displayed with a Response.Write() statement...)

    Conclusion


    In this article we saw how to create a fully-editable DataGrid. To accomplish this, the DataGrid must first be created so that each column is a TemplateColumn, with its <ItemTemplate> containing the editable user interface. Next, code needs to be written that will iterate through the records of the DataGrid when some "Update All" Button is clicked, sending an UPDATE statement to the database for each row in the DataGrid. For situations where users need to edit many records of a DataGrid, the fully-editable DataGrid UI can prove to be more efficient than requiring users to repeatedly click Edit and Update buttons, one row at a time.

    Happy Programming!

  • By Scott Mitchell



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article