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.
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.
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!
ASP.NET Data Web Controls Kick Start is author Scott
Mitchell's most recent book, which thoroughly examines three of the most commonly
used ASP.NET Web controls: the DataGrid, DataList, and Repeater. These three Web controls
can be difficult to master due to their numerous features and capabilities. With this book, you'll
quickly become an expert, learning the gritty details and true capabilities of each.
This 400+ page book explores the topics in this article series in much greater depth, along with
examining various topics and techniques not covered here.
Scott Mitchell is the editor and founder of 4GuysFromRolla.com, author of the An Extensive Examination
of the DataGrid Web Control article series, and author of numerous other ASP and ASP.NET
books.
