Gracefully Inserting a Row into an ASP.NET GridView

Introduction

This article will present the methods needed to insert a row into a GridView control on an ASP.NET page without using a DetailsView or FormView. We’ll cover how to do the actual Insert, what to do after that happens, and how to deal with cancellations or blank data. All of the source code that you need to perform this operation will be explained as we go. Everything on the .aspx page will be presented in Source view, and other than the event handlers that we add, there are no changes to the .cs page. This article also assumes that you have at least a basic familiarity with the GridView control and some skills writing C#.

Background

Before we begin, there is one thing that needs to be cleared up. The GridView does not contain an option for inserting a row of data into the control itself or the underlying database table. That function is handled by a DataConnection control. The GridView does nothing more than display data provided by a DataConnection and provide a means by which to delete or edit it. The DataConnection also does the Update, getting the new values from the GridView. So, to make it appear like we have inserted a row into a GridView, we have to do some behind the scenes work. The steps we have to follow are:

1. Provide a means by which the visitor can initiate this process
2. Provide the DataConnection an Insert command that will insert only the required columns into a table
3. Have the DataConnection perform the Insert
4. Have the GridView refresh its data
5. Open the new row in Edit mode, and…
6. Lastly, do not save a new row that has not been changed from the default values when:
1. The Cancel button is used
2. The Save button is used

Using the Code

We’ll start by looking at the GridView control that we’ll be working with. Here is how it looks when we start:

<asp:GridView ID="MyAddressesGridView" runat="server" 
        DataKeyNames="ID" DataSourceID="MyAddressesGridViewSqlDataSource">
   <Columns>
      <asp:BoundField HeaderText="Name" DataField="Name" />
      <asp:BoundField HeaderText="Address" DataField="Address" />
      <asp:BoundField HeaderText="City" DataField="City" />
      <asp:BoundField HeaderText="State" DataField="State" />
      <asp:BoundField HeaderText="Zip" DataField="Zip" />
      <asp:CommandField ShowCancelButton="true" ShowEditButton="true" />
   </Columns>
</asp:GridView> 

The table we are working with for this example, named MyAddresses, contains the following columns: ID, Name, Address, City, State, and Zip. The ID field is an integer that is also set up as an Identity column to give us unique values, and is also set as the primary key of the table. We also use the ID column as the value for the DataKeyNames setting in the GridView. This ensures that all of our data will be sorted by this column unless we allow sorting, which we aren’t. All of the other fields are set as varchars and allow null values. The size of each field isn’t important at this time, but you may want to check this in your own project to prevent errors. The CommandField is there to show the Edit and Cancel buttons. The Edit button will automatically change to the Save button once we get into Edit mode, and the Cancel button will not appear until we are in Edit mode.

With those things said, let’s move on to our project.

Step 1: Initiating the process. To get things started, we have to give the visitor a way to tell us that they want a row inserted. This typically requires a postback to the server. The simplest and fastest way to get this done is with a button and some commands in the OnClick event handler. So, here is our button:

<asp:LinkButton ID="InsertRowLinkButton" runat="server" 
   OnClick="InsertRowLinkButton_OnClick">Insert Row</asp:LinkButton> 

You can put this wherever you want and change the text to say whatever you want. I typically put it either right above or right below the GridView, with some text to make it stand out. I’ve chosen a LinkButton, but you can just as easily use a <asp:Button> control since the syntax for the OnClick event handler is the same. We’ll take care of the commands in the OnClick event handler next.

Step 2: The Insert command. In the OnClick event handler of our LinkButton, we have to assign the Insert query to the DataConnection. Here’s our event handler with the query:

protected void InsertRowLinkButton_OnClick(object sender, EventArgs e)
{
   MyAddressesGridViewSqlDataSource.InsertCommand = 
       "INSERT INTO MyAddresses (Name) VALUES (Null)";
}

Why are we setting the Name column to Null? We set it to Null because this is a new row that the visitor is going to provide the data for, and there is no sense in populating it with anything that the visitor will then just have to overwrite with their own data. Also, we have to have at least one column in the Insert query, or we’ll get an error when we try to run it. Since we have to have one column, but we don’t want it to have a value, we deliberately give it a Null value. Of course, this means that we will have to look for empty data when the visitor saves their changes, but that comes later. We’ll cover the processes to filter out blank data fully in Step 6. Don’t skip ahead!

Step 3: Perform the Insert. So far, we’ve assigned the DataConnection the command that it should run, but we haven’t told it to run the command. To do that, we call the Insert() method of the DataConnection. The OnClick event handler of our LinkButton now looks like this:

protected void InsertRowLinkButton_OnClick(object sender, EventArgs e)
{
   MyAddressesGridViewSqlDataSource.InsertCommand = 
       "INSERT INTO MyAddresses (Name) VALUES (Null)";
   MyAddressesGridViewSqlDataSource.Insert();
}

When this method is called, the query is run and any errors are sent back. If you get errors about columns not allowing Null values, this is where it is happening if you aren’t getting back to your page after you click the button.

Step 4: Refresh the GridView data. With our Insert performed, we have changed the data in the table. That means that the data in the GridView isn’t accurate now. To display our new data, we have to call the DataBind() method of the GridView. The OnClick event handler of our LinkButton now looks like this:

protected void InsertRowLinkButton_OnClick(object sender, EventArgs e)
{
   MyAddressesGridViewSqlDataSource.InsertCommand = 
      "INSERT INTO MyAddresses (Name) VALUES (Null)";
   MyAddressesGridViewSqlDataSource.Insert();
   MyAddressesGridView.DataBind();
}

Step 5: Setting up Edit mode. Now that we have the current data in the GridView, we have to tell it which row to edit. To do this, we set the EditIndex property of the GridView. What do we set it to? A GridView displays data in rows, and since we have just added a row to the data, the row we want will be the last one. How do we know it will be the last one? When we run a Select query, unless we specifically set a sort order, the data that is returned is automatically ordered by the column that is the primary key for the table (in this case, the ID column). Now that we know why, let’s go back to where. The Rows collection of the GridView is where we get this information, and we want the number that the Count property will give us. The snag here is that this is what is called an index value, and all index values are zero-based (meaning that they all start with 0), so we have to subtract 1 from that value. The OnClick event handler of our LinkButton now looks like this:

protected void InsertRowLinkButton_OnClick(object sender, EventArgs e)
{
   MyAddressesGridViewSqlDataSource.InsertCommand = 
      "INSERT INTO MyAddresses (Name) VALUES (Null)";
   MyAddressesGridViewSqlDataSource.Insert();
   MyAddressesGridView.DataBind();
   MyAddressesGridView.EditIndex = MyAddressesGridView.Rows.Count - 1;
}

We also have to take into consideration that this GridView may be displaying only one page of multiple pages of data. To make sure we are on the last page, we have to set the PageIndex property of the GridView to the last page. That number can be found in the PageCount property of the GridView. Since we are again dealing with an index value, we have to subtract 1 from the PageCount, too. The OnClick event handler of our LinkButton now looks like this:

protected void InsertRowLinkButton_OnClick(object sender, EventArgs e)
{
   MyAddressesGridViewSqlDataSource.InsertCommand = 
      "INSERT INTO MyAddresses (Name) VALUES (Null)";
   MyAddressesGridViewSqlDataSource.Insert();
   MyAddressesGridView.DataBind();
   MyAddressesGridView.PageIndex = MyAddressesGridView.PageCount - 1;
   MyAddressesGridView.EditIndex = MyAddressesGridView.Rows.Count - 1;
} 

You may notice that we put that command before the command to set the EditIndex. If we don’t do this in this order, the GridView may not display the row that we want to edit in the right place.

Step 6: Saving the right data. We now have a GridView that is displaying our new row in Edit mode. This in itself is good, but we still need to deal with the visitor trying to cancel adding the new row without making any changes, or trying to save the row without entering any data. To do that, we need to look at the two options that the visitor has: the Cancel button and the Save button.

Step 6a: The Cancel button. When the visitor cancels an edit, the OnRowCancelingEdit event handler of the GridView is called. Let’s look at our changed GridView:

<asp:GridView ID="MyAddressesGridView" runat="server" 
      DataKeyNames="ID" DataSourceID="MyAddressesGridViewSqlDataSource" 
      OnRowCancelingEdit="MyAddressGridView_OnRowCancelingEdit">
   <Columns>
      <asp:BoundField HeaderText="Name" DataField="Name" />
      <asp:BoundField HeaderText="Address" DataField="Address" />
      <asp:BoundField HeaderText="City" DataField="City" />
      <asp:BoundField HeaderText="State" DataField="State" />
      <asp:BoundField HeaderText="Zip" DataField="Zip" />
      <asp:CommandField ShowCancelButton="true" ShowEditButton="true" />
   </Columns>
</asp:GridView> 

When the OnRowCancelingEdit event handler is called, we need to know if the row that was being edited is the row that we just added. To find this, we’ll use the GridViewCancelEditEventArgs variable (in this case, ‘e’) and look at the RowIndex property. We need to compare it to the number of rows in the current page of the GridView, which is again found by using the Count property of the Rows collection. If they match, we need to delete that row from the table. We’ll do that using the DeleteRow method of the GridView, and then we’ll refresh the data in the GridView with the DataBind method. The OnRowCancelingEdit event handler should look like this:

protected void MyAddressGridView_OnRowCancelingEdit(object sender, 
          GridViewCancelEditEventArgs e)
{
   if (e.RowIndex.Equals(MyAddressesGridView.Rows.Count - 1))
   {
      MyAddressesGridView.DeleteRow(e.RowIndex);
      MyAddressesGridView.DataBind();
   }
}

This lets the visitor cancel out of editing a row that already has data in it, but also takes care of the cleanup that we need.

Step 6b: The Save button. We are letting the visitor cancel the addition of the new row by deleting that row in the table when they use the Cancel button, but we also have to deal with them trying to use the Save button without entering any data. When the visitor saves the row, first the OnRowUpdating event handler is called, and then the OnRowUpdated event handler. Let’s look at our changed GridView:

<asp:GridView ID="MyAddressesGridView" runat="server" 
       DataKeyNames="ID" DataSourceID="MyAddressesGridViewSqlDataSource" 
       OnRowDataBound="MyAddressesGridView_OnRowDataBound" 
       OnRowUpdating="MyAddressGridView_OnRowUpdating" 
       OnRowUpdated="MyAddressGridView_OnRowUpdated" 
       OnRowCancelingEdit="MyAddressGridView_OnRowCancelingEdit">
   <Columns>
      <asp:BoundField HeaderText="Name" DataField="Name" />
      <asp:BoundField HeaderText="Address" DataField="Address" />
      <asp:BoundField HeaderText="City" DataField="City" />
      <asp:BoundField HeaderText="State" DataField="State" />
      <asp:BoundField HeaderText="Zip" DataField="Zip" />
      <asp:CommandField ShowCancelButton="true" ShowEditButton="true" />
   </Columns>
</asp:GridView>

Let’s start with OnRowUpdating. This event handler is called when an edited row is saved by the visitor, and is where you do the actual saving of the data. We don’t want to save a row that is totally blank, so we’ll check each of the fields to make sure that we have at least some data. If we don’t find any data, we need to delete the row from the table. If we find data, we can save the row as we normally would. The OnRowUpdating event handler should look like this:

protected void MyAddressGridView_OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
   string NewName = string.Empty;
   if (e.NewValues["Name"] != null)
   {
      NewName = e.NewValues["Name"].ToString();
   }
   string NewAddress = string.Empty;
   if (e.NewValues["Address"] != null)
   {
      NewAddress = e.NewValues["Address"].ToString();
   }
   string NewCity = string.Empty;
   if (e.NewValues["City"] != null)
   {
      NewCity = e.NewValues["City"].ToString();
   }
   string NewState = string.Empty;
   if (e.NewValues["State"] != null)
   {
      NewState = e.NewValues["State"].ToString();
   }
   string NewZip = string.Empty;
   if (e.NewValues["Zip"] != null)
   {
      NewZip = e.NewValues["Zip"].ToString();
   }
   if(NewName.Equals(string.Empty) && NewAddress.Equals(string.Empty) && 
      NewCity.Equals(string.Empty) && NewState.Equals(string.Empty) && 
      NewZip.Equals(string.Empty))
   {
      e.Cancel = true;
      if (MyAddressesGridView.PageIndex == MyAddressesGridView.PageCount - 1 && 
          MyAddressesGridView.EditIndex == MyAddressesGridView.Rows.Count - 1)
      {
         MyAddressesGridView.DeleteRow(e.RowIndex);
      }
   }
   else
   {
      MyAddressesGridViewSqlDataSource.UpdateCommand = 
        string.Format("UPDATE MyAddresses SET Name = '{0}', Address = '{1}', " + 
        "City = '{2}', State = '{3}', Zip = '{4}' WHERE ID = {5}", 
        NewName, NewAddress, NewCity, NewState, NewZip, e.Keys[0]);
   }
}

This seems like a lot going on, but there is a purpose to all of it. If we tried to just put the raw values into our query and even one of them was “null”, it would cause an error. Because of this, we have to check each one individually. We also have to add a .ToString() onto each assignment because the NewValues collection of the GridViewUpdateEventArgs returns an object, not a string. Adding the .ToString() gets us the value that each item in the collection contains. This process also shows that you can go through that collection by naming the parts of it that you want (the keys) to get those specific values.

If all of our data was sent back with a null value, we don’t want to save the row. We can stop the whole process by setting e.Cancel to true. Now, what if the row that was saved was our new row? We can test for that by checking to see if it was the last row on the last page, and we can use the same values we used to set up the row for editing. If this is our new row, we want to delete it from the table. This is done by using the DeleteRow method of the GridView. We just have to give it the number of the row to delete, and that number is found in the RowIndex property of GridViewUpdateEventArgs.

If it turns out that we actually want to save this row, we have to be able to tell the database the ID value of the row we want to update. We get this information from the Keys collection of GridViewUpdateEventArgs, and the value we want is always in the first (0) position of the collection.

Once we have performed one of those two actions, we need to update the data in the GridView using the DataBind() method, which we’ve used before. This happens in the OnRowUpdated event handler. This event handler is called after all of the updating is done. Our event handler should look like this:

protected void MyAddressGridView_OnRowUpdated(object sender, GridViewUpdatedEventArgs e)
{
   MyAddressesGridView.DataBind();
}

This finishes our work, and returns all of the data to the GridView, including the new row, but without being in Edit mode. The visual aspects of what we have done here are certainly able to be embellished, but the underlying process needs to be clean.

Points of Interest

This process could certainly be done with a DetailsView or a FormView (in fact, that is how Microsoft would like you to do it), but sometimes, you just need to be able to do it where the rest of the data in the GridView can be seen. Yes, you could put those controls on the page above or below the GridView, but it’s not as clean looking.

History

• Original draft: November 29, 2009.