Click here to Skip to main content
Click here to Skip to main content
Go to top

GridView Redux

, 20 Feb 2006
Rate this:
Please Sign up or sign in to vote.
How to add/edit a GridView in style.

Introduction

The ASP.NET 2.0 GridView is a wonderful beast that can allow you to create new database rows, edit in place, even provide DropDownLists and CheckBoxes to aid you in the entry process. It can even do data validation.

I have added the following additional functionality over and above what you get "out of the box":

  • Insert new rows.
  • Edit via DropDownLists.
  • Validation.
  • Delete confirmation.
  • Checkbox input.
  • Current row highlighting.
  • Selective cell highlighting based on value.

None of the functionality that I will show you how to build is rocket science, and most of it is around the internet someplace (although I have never seen the "Insert new row before"). This is my humble attempt to consolidate it all in one article.

Background

I was presented with a project to convert an Access single-user application into a Web-based multi-user SQL Server application. The Access application had about 10 tables that needed to be managed through an Add/Edit/Delete interface. This table maintenance was to be done by a single individual, so concurrency issues would not be a problem. I decided that with some tweaking, the GridView could perform what needed to be done. Out of the box, the GridView will not do row inserts, nor will it provide DropDownLists where the data to be edited is really an ID representing a row in another table. This example uses the NorthWind Products table to add and update data rows. It is a step-by-step guide to producing a web page which will accomplish this task. It is long and arduous, but this is what it takes. When you have completed this exercise, you should be ready to tackle your own tables with confidence. You may want to print out the steps and keep them handy so you don't have to be constantly switching screens (unless you have two monitors Wink | ;-)

Using the code

Download the project, unzip it to somewhere on your hard disk, and then create a new web project from an existing folder. Build the project and test drive it so that you see what it does. Now, create a new ASPX file, Default2.aspx, and do it yourself manually.

Steps to create the project manually

  • Create a blank project.
  • Create an Images folder.
  • Right click on Images.
  • Choose "Add Existing Item".
  • Go to the folder you unzipped the download to, and select all the .GIF files and click "Add".
  • In the design view, add a DataSource.
  • Configure the DataSource to select each individual field.
  • Create a GridView and select the previously created DataSource.
  • Click the GridView SmartTag to open up its Tasklist.
  • Enable Paging, Sorting, Editing and Deleting.
  • Select Edit Columns.
  • In the lower right pane, the top item is CommandField.
  • Click it, and the top right pane shows the attributes.
  • Change Appearance > Button Type to Image.
  • Set CancelButtonUrl to Images/Cancel.gif.
  • Set the rest of the ButtonUrls appropriately.
  • Select ProductID from the lower left pane.
  • Press the red X to the right to remove it as it is not an editable field.
  • Build and run the project.

You now have a working editable/delete-able table maintenance program.

Editing using DropDownLists

  • Create a new SqlDataSouce (in the Toolbox under Data).
  • Configure this DataSource to connect to the Suppliers table, and in the SELECT statement, choose SupplierID and CompanyName.
  • Create a new SqlDataSouce (you know where it is).
  • Configure this DataSource to connect the the Categories table, and in the SELECT statement, choose CategoryID and CategoryName.
  • Click on the GridView SmartTag to bring up the Tasklist.
  • Click "Edit Columns...".
  • Highlight SupplierID (in the lower left pane), then click on the phrase "Convert this field into a template" (over to the right).
  • Do the same for CategoryID.
  • Click on OK.
  • You should now be looking at the GridView Tasklist.
  • Click Edit Templates at the bottom.
  • Click the down arrow and you will see a list of templates, choose (under Column [2] - SupplierID) "EditItemTemplate".
  • Do not choose "ItemTemplate".
  • Select the text box and delete it.
  • From the toolbox, drag a DropDownList control and place it where the textbox was.
  • Click its SmartTag and click "Choose data source".
  • Choose the Supplier DataSource that you set up earlier.
  • Bind the display field to CompanyName, and the value to SupplierID.
  • Click the GridViews SmartTag.
  • Now, pick "EditItemTemplate", but this time, under Column [3] - CategoryID.
  • Repeat what you have just done to connect up the CategoryID.
  • Now, build and run the application.
  • You will see in the SupplierID and CategoryID columns, the Supplier/Category IDs (we'll fix that shortly), but when you click Edit, they magically transform into dropdown lists (well, it's not really magic, but it is pretty impressive).
  • Click Cancel (because we're not ready to do an Update yet).
  • Close the application.
  • Now, we're going to turn those irritating Supplier/Category IDs into more user friendly names.
  • You need to modify the first DataSource you created to dig the supplier's name and the category's name out of their respective tables. You could do it the manly (stupid) way by editing the source code for the SELECT statement and doing the INNER JOINs yourself, or just click the DataSource's SmartTag and click "Configure data source".
  • Click Next once, and then change the retrieval type to Custom SQL Statement.
  • Click Next and make sure Select is the highlighted tab.
  • Click Query Builder.
  • Right click in the pane showing the Products table and choose Add table.
  • Add the following tables: Suppliers, Categories, then close.
  • Add CompanyName from Suppliers and CategoryName from Categories to the query result.
  • You're now done with the DataSource. Now, click the GridView SmartTag. At some point, an alert box will ask you if you want to clear the work you have already done. Just say no!
  • Click Edit Templates in the Tasklist.
  • Choose the ItemTemplate for Column [2] - SupplierID.
  • Click the SmartTag for the Label control and choose Edit Databindings.
  • Select Text in the left pane and change the Bound to column to CompanyName.

    Important: Make sure that the two-way binding is still checked after you do this.

  • Close the box.
  • Click the SmartTag for GridView1 - Column [2] - SupplierID.
  • Choose the ItemTemplate for Column [3] - CategoryID.
  • Click the SmartTag for the Label control and choose Edit Databindings.
  • Select Text in the left pane and change the Bound to column to CategoryName.

    Important: Make sure that the two-way binding is still checked after you do this.

  • Close the box.
  • Click the SmartTag for GridView1 - Column [3] - CategoryID.
  • Click End Template Editing.
  • Build and execute the application.

    Now, you will see that CategoryID and SupplierID numbers have been replaced by names.

  • Click around and edit the various lines but don't save - this is the real live NorthWind database that other tutorials depend on. We'll get to adding next, so you can add your own rows and mangle/delete them without injuring the real data.

Follow these steps to make the GridView - Add New Records

  • Click the Product DataSource SmartTag.
  • Click Configure data source.
  • Click Next twice.
  • Make sure Select is the highlighted tab.
  • Click Query Builder.
  • Change the existing statement:
    SELECT Products.ProductID, Products.ProductName, Products.SupplierID, 
      Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, 
      Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, 
      Products.Discontinued, Categories.CategoryName, Suppliers.CompanyName 
      FROM Products INNER JOIN Categories ON 
      Products.CategoryID = Categories.CategoryID 
      INNER JOIN Suppliers ON 
      Products.SupplierID = Suppliers.SupplierID 
      ORDER BY Products.ProductName

    to be:

    SELECT 0 as ProductID, '' as ProductName, 0 as SupplierID, 0 as 
      CategoryID, '' as QuantityPerUnit, 0.00 as UnitPrice, 
      0 as UnitsInStock, 0 as UnitsOnOrder, 0 ReorderLevel, 
      convert(bit, 0) as Discontinued,'' as CategoryName, 
      '' as CompanyName
    
    UNION
    
    SELECT Products.ProductID, Products.ProductName, Products.SupplierID, 
      Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice, 
      Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, 
      Products.Discontinued, Categories.CategoryName, Suppliers.CompanyName 
      FROM Products INNER JOIN Categories ON 
      Products.CategoryID = Categories.CategoryID INNER JOIN 
      Suppliers ON Products.SupplierID = Suppliers.SupplierID 
      ORDER BY Products.ProductName
  • Insert the following JavaScript code into the <head> of the ASPX file:
    <script>
    function FixGrid(idGrid, PageIndex, EditIndex)
    {
      var Start = 1;
      if(EditIndex != 0)
      {
        if(PageIndex == 0)
        {
          Start = 2;
          // see the actual source for explanation
          // replace Edit image with Add image, remove Delete
          idGrid.firstChild.childNodes[1].childNodes[0].childNodes[0].src=
            "Images/Add.gif";
          var i = 
            idGrid.firstChild.childNodes[1].
                   childNodes[0].innerHTML.indexOf("&nbsp;");
          idGrid.firstChild.childNodes[1].childNodes[0].innerHTML = 
            idGrid.firstChild.childNodes[1].childNodes[0].innerHTML.slice(0, i);
        }
      }
      // put delete confirmations in
      for(var i=Start; ; i++)
      {
        try
        {
          var ctl=idGrid.firstChild.childNodes[i].childNodes[0].childNodes[2];
          if(ctl.tagName == "INPUT")
          {
            var onc = ctl.onclick.toString();
            // window.alert(onc);
            // uncomment this to see what the onclick actually contains
            if(onc.indexOf("Delete$") == -1)
             continue;    // don't want to add confirm to "update cancel"
            var j = onc.indexOf("__do");
            var k = onc.indexOf(")", j)+1;
            onc = "if(confirm('Are you sure') == false)" + 
                  " return(false); "+onc.slice(j, k);
            ctl.onclick = onc;
            // if you don't do this then the onclick will not work.
            // it is probably related to how the onclick is actually
            // defined (see window.alert above)
            ctl.outerHTML = ctl.outerHTML;
          }
        }
        catch(e)
        {
          // when we land here, we have run the table rows out
    
          break;
        }
      }
    }
    </script>
  • Put this script after the closing </form> tag:
    <script> 
      FixGrid(document.all.GridView1, 
         <%=GridView1.PageIndex.ToString()%>, 
         <%=EditIndex %>);     
    </script>
  • Right click the GridView and select Properties.
  • Click the Event button.
  • Double click Action > RowUpdating.
  • Double click Action > RowEditing.
  • This will create the empty event handlers and the required event hookup code.
  • Now, replace the code in the empty ASPX.CS file with:
    public partial class YOUR_CLASSNAME_HERE : System.Web.UI.Page
    {
        public int EditIndex = -1;
        protected void Page_Load(object sender, EventArgs e)
        {
    
        }
    
        protected void GridView1_RowUpdating(object sender, 
                       GridViewUpdateEventArgs e)
        {
          if (e.RowIndex > 0 || GridView1.PageIndex > 0)
            return;
            // only RowIndex 0 on Page 0 is the row we want to insert
          // find the DataSource for the GridView control
          System.Web.UI.WebControls.SqlDataSource ds = 
            (System.Web.UI.WebControls.SqlDataSource)
              this.FindControl(this.GridView1.DataSourceID);
          // Get the DataSource's connection string
          System.Data.SqlClient.SqlConnection conn = 
            new System.Data.SqlClient.SqlConnection(ds.ConnectionString);
          // open the connection
          conn.Open();
          // get the Insert command string
          string s = ds.InsertCommand;
    
          // create the new command
          System.Data.SqlClient.SqlCommand c = 
            new System.Data.SqlClient.SqlCommand(s, conn);
          System.Data.SqlClient.SqlParameter p;
          // the NewValues collection contains
          // the name value pairs that need to be inserted, 
          // make them parameters to the command
          foreach (System.Collections.DictionaryEntry x in e.NewValues)
          {
            p = new System.Data.SqlClient.SqlParameter("@" + x.Key, x.Value);
            c.Parameters.Add(p);
          }
          // execute the command
          c.ExecuteNonQuery();
          // the GridView framework will execute an update
          // on a row where the identity column=0
          // since none exists it will silently do nothing, but that is ok
        }
    
      protected void GridView1_RowEditing(object sender, 
                     GridViewEditEventArgs e)
      {
        EditIndex = e.NewEditIndex;
      }
    }
  • Now the hack:

    The INSERT statement that is generated by VS2005 can not be executed as is. It is originally:

    InsertCommand="INSERT INTO [Products] ([ProductName], 
       [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], 
       [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
       VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, 
       @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)"

    You need to change it to:

    InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], 
       [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], 
       [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
       VALUES (@ProductName, @SupplierID, @CategoryID, 
       @QuantityPerUnit, convert(money, @UnitPrice), 
       @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)"

    You can change it manually in the source code or through the IDE.

Checkboxes

You pretty much get checkboxes for free. The GridView by default will bind a SQL column of type bit (and only bit) to a CheckBoxField (you'll get a runtime conversion exception if you don't). If you look at the SelectCommand that we modified above, I had to add convert(bit, 0) as Discontinued.

Sorting

By default, the SortExpression for a field is the same as the field. In this case, we are displaying CompanyName and CategoryName in the SupplierID and SortExpression fields, so I have set the SortExpression for SupplierID to CompanyName and the SortExpression for CategoryID to CategoryName.

Look in the code behind file, and you will see how sort direction glyphs were added (code courtesy of Dino Esposito's New Grid in Town article). This code makes use of the OnRowCreated event, so make sure you hook it up in the GridView's Property Events sheet.

Other business

Data presentation

There are two ways you can format data.

To statically format the data

  • Choose Edit Column from the GridView's SmartTag.
  • Highlight the column you want to format in the lower left pane. Scroll the right hand pane to the bottom.
  • Expand ItemStyle and make your changes.
  • In this sample program, I have set each item's VerticalAlign to top, you'll understand why in the section on validation.
  • I also set the HorizontalAlign to right for the numeric columns.
  • If the item you have chosen is also an ItemTemplate, you can also make some formatting changes to its constituent controls by navigating to it as described above and making your required changes to the ItemTemplate not the EditItemTemplate.

To format the data at runtime

You must create a RowDataBound event handler. To do this:

  • Right click the GridView and select Properties.
  • Click the lightning bolt at the top of the Properties window to access the events sheet.
  • Double click in the input area to the right of RowDataBound to auto-generate an empty event handler.

I then modified the empty template to look like this:

protected void GridView1_RowDataBound(object sender, 
                                      GridViewRowEventArgs e)
{
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
    if (e.Row.RowIndex != EditIndex)
    {
      // highlight the UnitsInStock cell if it is zero
      int unitsInStock =
        Convert.ToInt32(System.Web.UI.DataBinder.Eval(e.Row.DataItem, 
        "UnitsInStock"));
      if (unitsInStock == 0)
        e.Row.Cells[7].BackColor = System.Drawing.Color.Yellow;
      // highlight the row that the mouse is over
      // save the original attribute in a custom attribute
      // so that you can restore it later
      e.Row.Attributes.Add("onmouseover", 
         "this.originalcolor=this.style.backgroundColor;" + 
         " this.style.backgroundColor='Silver';");
      e.Row.Attributes.Add("onmouseout", 
         "this.style.backgroundColor=this.originalcolor;");
    }
  }
}

The first part of the above code gets the value of the UnitsInStock field from the current row, and if the value is 0, change the cell's background to yellow. If you have seen other examples of RowDataBound functions, you may wonder why not just do Convert.ToInt32(e.Row.Cells[7].Text). Well here is the nasty secret that no-one wants to tell you about (or at least not document anywhere). Once you turn a BoundField into an ItemTemplate, you must access the original data, because at the time that RowDataBound is called, it is not yet in the cell for you to access. But if you think about it, this makes perfect sense!

At the RowDataBound time, the cell still contains the controls which make up the template, and there may be good reasons for you to still want to manipulate the constituent controls before rendering.

The second part shows you how to highlight the row that the mouse is over. The only trick here is to save the underlying background color (in the custom attribute originalcolor) before changing it so that it can be restored on the OnMouseOut event. I have seen other solutions that hard code the background color in the restore, but that technique does not handle alternating background colors.

Validation

I have added validation controls to each EditItemTemplate. Each control is validated as it is edited (i.e.: when it loses focus). If there is an error, the error message is displayed below the control. The reason why I formatted each control to be VerticalAlign = top was so that the line would look nicer when there were errors.

Styles

All elements, whether DataBound, ItemTemplates, or controls within an ItemTemplate, expose a CssClass element to which you can assign one or more classes to affect the look of the element. If you are specifying more than one class, separate them with a space. It is interesting that none of the GridView elements support inline styling via the CssStyle.Add(...) method that most other classes support. This means that you can not add individual inline style elements to a cell.

Images

If anyone has better images that I can use for this example, please send them to me or send the URLs. These ones suck.

Conclusions

I have taken you through the long and complex VS2005 process of what should be the simple task of maintaining a table. While it is a great step forward, it could have gone a lot further. Come on Visual Studio team, we need a button in the GridView's SmartTag TaskList that will automate most of the process I have just described manually!

Errata

This has been a long step-by-step article, and I am sure that there are mistakes. Please be kind. Any corrections will be dutifully recognized and the article corrected.

External links

If you have any other suggestions, please send them along.

SmashGrab / Redux series

I have recently started two series of articles here at CodeProject. Smash & Grab is intended as series of short articles on one specific code technique. Redux is intended as a series of longer articles which attempt to reduce a complicated topic (like GridView) into its basic constituent parts and show that once you have all the information, it isn't really that hard after all. To find the Smash&Grab articles, search for the keyword SmashGrab. To find the Redux articles, search for the keyword Redux. I welcome any contributions to either series, but please follow the guidelines when submitting articles to either.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Gary Dryden
Software Developer (Senior)
Canada Canada
No Biography provided

Comments and Discussions

 
GeneralMy vote of 4 Pinmemberrezax25-Jun-11 2:26 
Explaining clearly for me
General'firstChild' is null or not an object Pinmemberxavier3331-Sep-08 23:56 
Generalgridview PinmemberSSN23-Jul-08 18:45 
GeneralThe step by step is great Pinmemberohyeahbaby6-Oct-07 11:35 
Generalonclick event Pinmembersmithersgs1-Oct-07 10:59 
GeneralRe: onclick event Pinmemberpoo_test2-Jun-08 2:25 
GeneralInstructions Pinmemberwilliammcq24-Jul-07 15:36 
GeneralDummy Record in the Database PinmemberSue Low8-Mar-07 15:02 
QuestionVB.Net Version?? PinmemberSlapStick15-Dec-06 9:57 
AnswerRe: VB.Net Version?? PinmemberGary Dryden16-Dec-06 3:46 
GeneralRe: VB.Net Version?? PinmemberSlapStick18-Dec-06 2:37 
QuestionBut, if we use sorting for the same grid??? PinmemberkvPriya28-Sep-06 1:41 
QuestionFixGrid Function not working PinmemberTejoo4-Sep-06 20:17 
NewsBug corrections and currency problem fixed Pinmembershogedal23-Aug-06 7:44 
QuestionBuild Error: The multi-part identifier "Products.ProductName" could not be bound. Pinmemberberneroberland9-Jul-06 22:42 
AnswerRe: Build Error: The multi-part identifier "Products.ProductName" could not be bound. Pinmembercasdsvbsdh9-Aug-06 9:34 
GeneralRe: Build Error: The multi-part identifier "Products.ProductName" could not be bound. Pinmemberdogadder6-Sep-06 7:46 
GeneralI'd love to see a good way to scroll a GridView PinmemberEric Engler5-Jul-06 4:11 
GeneralRe: I'd love to see a good way to scroll a GridView Pinmembershogedal23-Aug-06 10:18 
GeneralValidation Pinmemberanga20-Apr-06 5:11 
GeneralRe: Validation Pinmemberbob789878618-Jul-06 11:13 
QuestionVery Time-saving Pinmembertcadwall21-Mar-06 8:48 
GeneralFurther Directions ... Pinmembervrajaramanv14-Mar-06 1:39 
GeneralZIP file PinmemberPie_Hair19-Feb-06 22:21 
GeneralRe: ZIP file PinmemberGary Dryden20-Feb-06 3:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 20 Feb 2006
Article Copyright 2006 by Gary Dryden
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid