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

Adding a New Record to the DataGrid

By John Sanborn


Introduction


I've done a lot of reading on ASP.NET since it was ASP+, but only recently got around to actually doing anything with it. I started putting together a page with the DataGrid and was pretty happy with the ease of coding all the functionality it provides. Displaying, editing and deleting records is a breeze. (For information on editing the records in a DataGrid, be sure to read: A Thorough Examination of the DataGrid Web Control: Part 6. To see how to delete a record from the DataGrid, be sure to read Part 8.)

When I got to the question of adding new records, though, I seemed to hit a wall. While I found plenty of information in books and on the Internet on editing, deleting and customizing the DataGrid's display, I couldn't find much at all about how to add records without creating another page. Tinkering around with the grid a little I came up with a way to easily add a "New Record" row to the bottom of the grid.

Essentially, I display a series of TextBox Web controls in the Footer of the DataGrid into which the user can enter values for a new record. The screenshot below shows an example Web page using the code I will present in this article. Note that for each record in the DataSource that's bound to the DataGrid, the details are displayed, along with an "Edit" and "Delete" button. In the Footer, there is a TextBox for each column, along with an "Add" button. A user can add a new record by simply entering values into these bottom TextBoxes and clicking the "Add" button.

Screenshot of my DataGrid.

In this article I use the stores table from the SQL Server pubs database. For simplicity, this page only has five text fields and doesn't include any validation, so mind your data constraints if you try it out as-is. Displaying, editing, and deleting data with the DataGrid has been thoroughly covered already, so I'll limit my explanation here to adding the "New Record" row.

Creating the TextBox Web Controls in the Footer


Adding a TextBox Web control in the Footer is a snap if you are using TemplateColumns. TemplateColumns, contain a number of templates. The template that is rendered for a particular row of the DataGrid depends on the type of the row being added. For example, if the row is an ordinary item row, the ItemTemplate or AlternatingItemTemplate is used, depending on whether or not the row being added is an odd or even row. If the row being added is specified as the EditItemIndex row, then the TemplateColumn's EditItemTemplate is used. Finally, if the row being rendered is the Header or Footer, the HeaderTemplate or FooterTemplate is used.

To add a TextBox Web control to the Footer, we simply need to use a TemplateColumn for each column of the DataGrid, and provide a FooterTemplate. Below you will see a DataGrid declaration that uses the FooterTemplate. Before we examine this DataGrid, though, it is important to point out that the DataGrid does not display the Footer row by default. In order to have it displayed you must set the DataGrid's ShowFooter property to True.

Also note that the DataGrid's ItemCommand event is wired up to the event handler doInsert. The DataGrid's ItemCommand event fires whenever a command button in the DataGrid is clicked. The "Add" button is a command button, and when it is clicked we'll have to execute some code that inserts a new record into the database based on the values provided by the user. Later on in this article we'll be examining the doInsert event handler in detail.

<form id="Form1" method="post" runat="server">
  <asp:DataGrid id="storeGrid"
      ...
      OnItemCommand="doInsert"
	  ShowFooter="True"
	  runat="server"   AutoGenerateColumns="False"
	  ...>
    <Columns>
      <asp:TemplateColumn HeaderText="Store ID">
        <FooterTemplate>
          <asp:TextBox ID="add_storID" Columns="5" Runat="Server" />
        </FooterTemplate>
        <ItemTemplate>
          <%# Container.DataItem("stor_id") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:TextBox ID="stor_id" Columns="5" 
              Text='<%# Container.DataItem("stor_id") %>' Runat="server" />
        </EditItemTemplate>
      </asp:TemplateColumn>
      
      ... Repeat this "template" for every other databound column ...

While this code shows only one TemplateColumn, realize that this standard TemplateColumn would be used for every databound column in the DataGrid. Using the FooterTemplate, a TextBox Web control has been added to the bottom of each column.

The ItemTemplate and EditItemTemplate's are pretty straightforward. The ItemTemplate simply displays the value of the DataSource field this column is to display, while the EditItemTemplate creates a TextBox Web control for the user to edit the data.

Following these TemplateColumns needs to be an EditCommandColumn. The EditCommandColumn is a built-in DataGrid column that displays the Edit button for each row. When a particular row's Edit button is clicked, the row enters "edit mode" and the row's EditItemTemplate is used when the DataGrid is rendered. Additionally, the EditCommandColumn chnages for the row being edited so as to display an Update and Cancel button. This EditCommandColumn is added to our DataGrid like so:

      ... The TemplateColumns from the last code example would be found here ...

      <asp:EditCommandColumn
	     ButtonType="PushButton" UpdateText="Update" CancelText="Cancel"
	     EditText="Edit" HeaderText="Edit">
      </asp:EditCommandColumn>
      
      ...

The final DataGrid column is used to display a Delete button for the populated data rows and the "Add" button for the DataGrid Footer rows. Recall that this "Add" button, when clicked, will add the new record to the DataGrid. Note that the CommandName property for the Button Web control in the Footer is set to Insert. We'll need to use this knowledge in the DataGrid's ItemCommand event handler, which will execute after the user clicks the "Add" button.

      ... The TemplateColumns from the two code examples ago would be found here ...
      ... The EditCommandColumn from the last code example would be found here ...

      <asp:TemplateColumn HeaderText="Delete">
        <FooterTemplate>
          <asp:Button CommandName="Insert" Text="Add" ID="btnAdd" Runat="server" />
        </FooterTemplate>
        <ItemTemplate>
          <asp:Button CommandName="Delete" Text="Delete" ID="btnDel" Runat="server" />
        </ItemTemplate>
      </asp:TemplateColumn>
    </Columns>
  </asp:datagrid>
</form>

Examining the Source Code for the doInsert Event Handler


The code-behind (or server side script) for the update, delete or cancel procedures go pretty much unchanged from what you would expect. The only exception is a line of code added to the procedure for displaying the edit row. To make the table more user friendly, the new record row (Footer) is hidden when an existing data row is selected for editing. This is accomplished by simply setting the ShowFooter property to False programmatically:

Sub doEdit(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
    storeGrid.ShowFooter = False
    storeGrid.EditItemIndex = e.Item.ItemIndex
    BindData()
End Sub

Then the ShowFooter property is set back to True in the procedures for updating and canceling.

Sub doCancel(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
    storeGrid.ShowFooter = True
    storeGrid.EditItemIndex = -1
    BindData()
End Sub

The update event handler would need the storeGrid.ShowFooter = True line inserted as well...

In order to handle the case when the user clicks the "Add" button, we must provide an event handler for the DataGrid's ItemCommand event. This event handler, doInsert, must contain an If statement to check the CommandName property before the code to insert a new record is executed. This is because all command buttons in the DataGrid, when clicked, cause the DataGrid's ItemCommand event to fire. This includes the Edit, Update, Cancel, and Delete buttons as well. Therefore, we need to make sure that the CommandName property that caused the ItemCommand event to fire is the same as the CommandName property of the Add button.

After this check, we simply need to read in the values of the Footer TextBoxes, issue an appropriate database call, and then rebind the data to the DataGrid. The code for doInsert is given below:

Sub doInsert(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
  If e.CommandName = "Insert" Then
      Dim storID As String
      Dim txtStorID As TextBox
      ... declarations for remaining data fields

      Dim strSQL As String

      'Read in the values of the TextBoxes
      txtStorID = e.Item.FindControl("add_storID")
      storID = txtStorID.Text
      ... get values for remaining data fields

      'Create the appropriate SQL statement
      strSQL = "INSERT INTO stores (stor_id, stor_name, stor_address, " _
            & "city, state, zip) VALUES (@storeID, @storeName, @storeAddr, " _
            & @city, @state, @zip)"

      Dim con As SqlConnection
      Dim cmdExp As SqlCommand

      con = New SqlConnection(ConfigurationSettings.AppSettings("conn"))
      cmdExp = New SqlCommand(strSQL, con)
      
      'Add the parameters
      Dim storNameParam as New SqlParameter("@storName, SqlDbType.VarChar, 50)
      storNameParam.Value = txtStorName
      cmdExp.Parameters.Add(storNameParam)
      ... repeat this for all of the parameters ...
      
      con.Open()
      cmdExp.ExecuteNonQuery()
      con.Close()

      'Rebind the DataGrid
      storeGrid.EditItemIndex = -1
      BindData()
  End If
End Sub

Conclusion


As you can see, adding that much desired "New Record" row to the DataGrid control couldn't be simpler. You only need to make use of the Footer for the new data entry fields, add a button and include a procedure for inserting the record to the database. Definitely easier than creating a new page!

Happy Programming!

  • By John Sanborn

  • Article Information
    Article Title: ASP.NET.Adding a New Record to the DataGrid
    Article Author: John Sanborn
    Published Date: February 12, 2003
    Article URL: http://www.4GuysFromRolla.com/articles/021203-1.aspx


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