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.

<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

ASP.NET Data Web Controls Kick Start 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. [Buy this Book] [Visit the Book's Companion Web Site]