Nine Steps to a Quick, Editable Web Grid

Hello! In this mini three-part series, I'll be looking at the ASP.NET DataGrid and almost anything and everything you can do with it, all condensed into easy-to-follow tips. We begin this week with a biggie: how to create an editable DataGrid, with all the code written for you!

Setting up your own editable Windows DataGrid may be an absolute doddle, but creating the equivalent grid for the Web is a little more complicated. Most articles simply show you how to display data—and conveniently manage to skip the editing, deleting, adding, and updating stages. But not this one.

Here, we're going to create a template page that will allow you to display data from a table. You'll be able to add new records. Delete records. Edit existing records. Update the backend database. It'll handle most of your simple table operations. I've written all the code for you—and if there's any piece of functionality you don't want, just don't add it.

Let's get started.

1. Open the Server Explorer (View > Server Explorer). If you're connecting into a SQL Server database, expand the Servers node, locate your target machine (if not available, click on the Connect to Server icon and specify the machine name), then drill down to your database. If you're connecting into another type of database, right-click on the Data Connections node, select Add Connection, and connect into your database.
2. Drag the table you want the grid to be based upon onto your Web form. Two components will be added—a Connection object, which connects into the database; and a DataAdapter object, which acts as the "phone line" between your Connection object and your actual set of data (your DataSet).

Top Tip: To preview the data coming back from your DataAdapter, right-click on your DataAdapter object and select Preview Data. To change what is returned (for example, to remove certain columns or add calculated fields), right-click and select Configure Data Adapter, and then use the designer to prepare a customized SQL statement. Do not remove primary keys; instead, make them invisible. (See the next Top Tip.)

3. Right-click on the DataAdapter and choose Generate DataSet. A dialog box will appear, about to create the template upon which your DataSet will be based. (A DataSet based on a template like this is called a typed DataSet, whereas the template itself is a customizable XML schema, sometimes referred to as an XSD [XML Schema Definition] file.) Ensure that New is selected and replace the suggested name with something more sensible, such as "Customer". Ensure that the "Add this DataSet to the designer" option is checked. Click OK when finished. Two things will happen: a Customer.xsd (or similar) template will be added to your Solution, and an invisible DataSet object will be added to your form, based on the template. Rename the DataSet template to, say, "dsCustomer".
4. Drag and drop a DataGrid control from the toolbox onto your form. Resize as appropriate, then right-click on and select Auto Format. Choose a new style, such as "Professional 3" or "Colorful 3".
5. Add the following code template to respond to the page Load event. This retrieves your table data from the database and binds it to your DataGrid. Be sure to replace MyDataAdapter and MyDataGrid with names of your own DataAdapter and DataGrid objects:

If Not IsPostBack Then
  MyDataAdapter.Fill(MyDataSet)
  MyDataGrid.DataSource = MyDataSet
  MyDataGrid.DataBind()
  DataSave(MyDataSet)
  ' The DataSave function will be added later.
  ' Remove this line and stop here if you want
  ' a read-only DataGrid
End If

6. Right-click on your DataGrid and select Property Builder. Select the Columns property sheet and ensure that "Create columns automatically at run time" is checked. This means that the columns are dynamically created from your table. Next, we're going to add one button to allow you to select a record, perhaps for editing or deleting. Under the "Available columns" list, expand Button Column, highlight Select, and use the ">" button to move it across to the "Selected columns". The properties of this new column button will appear below. Change its Text property to aN angle bracket (>) and its Button type to a PushButton. Click OK when finished. You should be able to see the new record select button in your grid.

Top Tip: If you want to display only certain columns in the DataGrid, you can selectively choose those required through the Property Builder. First, in the General property sheet, select your data by choosing your DataSet and table for the DataSource and DataMember properties. Next, in the Columns property sheet, uncheck "Create columns automatically at run time", and then move individual fields from the list of available columns (under "Data Fields") over to the selected columns list. Click OK when finished and continue the instructions.

7. Add the following code to respond when the SelectedIndexChanged event of your DataGrid occurs. This event fires when a record is selected. This code simply highlights the row, making your selection more obvious:

Dim intCount As Integer
For intCount = 1 To MyDataGrid.Items.Count
  MyDataGrid.Items(intCount - 1).BorderStyle = _
                                 BorderStyle.Groove
Next
MyDataGrid.SelectedItem.BorderStyle = BorderStyle.Dashed

8. Add the following functions behind your Web form. They provide a clean and easy way of saving and retrieving the DataSet containing our table data. Here, our application uses the page ViewState (encrypted HTML sent back and forth between posts), but you could easily change it to use the Session or Application object if you're dealing with large tables:

Public Sub DataSave(ByVal DataSet As DataSet)
  If DataExists() Then
    ViewState.Item("__Data") = DataSet
  Else
    ViewState.Add("__Data", DataSet)
  End If
End Sub
Public Function DataRetrieve() As DataSet
    Return CType(ViewState.Item("__Data"), DataSet)
End Function
Public Function DataExists() As Boolean
  If Not ViewState.Item("__Data") Is Nothing Then Return True
End Function

9. Add six buttons to your Web Form, above the grid: Add, Delete, Edit, OK, Cancel, and Update. These will be action buttons. You will click Add to add a new record, Delete to remove a record, Edit to edit an existing record, OK to accept an edit, Cancel to cancel an edit, and Update to save all changes to the backend database. If you don't want to implement any one of these features, simply leave it out. Behind each of those buttons, add the relevant snippet of code (you may wish to incorporate your own error handling code, too):

' Code to respond to the Click event of the ADD button:
' Desc: Adds a new row to the DataSet, rebinds to the
' DataGrid, then makes the row editable
If DataExists() = False Then Exit Sub
MyDataSet = DataRetrieve()
Dim rowNew As System.Data.DataRow = MyDataSet.Tables(0).NewRow
' Enter sample values for non-null fields here
' ie, rowNew.Item("uniqueTag") = "sample"
' Alternatively, use separate text boxes for input
' and add field values in code, as above.
MyDataSet.Tables(0).Rows.Add(rowNew)
MyDataGrid.EditItemIndex = MyDataGrid.Items.Count
MyDataGrid.DataSource = MyDataSet
MyDataGrid.DataBind()
DataSave(MyDataSet)

' Code to respond to the Click event of the DELETE button:
' Desc: Deletes the selected row, updates the DataSet, then
' rebinds
If DataExists() = False Then Exit Sub
If MyDataGrid.SelectedIndex = -1 Then Exit Sub
MyDataSet = DataRetrieve()
MyDataSet.Tables(0).Rows(MyDataGrid.SelectedIndex).Delete()
MyDataGrid.EditItemIndex = -1
MyDataGrid.SelectedIndex = -1
MyDataGrid.DataSource = MyDataSet
MyDataGrid.DataBind()
DataSave(MyDataSet)

  ' Code to respond to the Click event of the EDIT button:
  ' Desc: Makes the selected row editable, then rebinds
  If DataExists() = False Then Exit Sub
  If MyDataGrid.SelectedIndex = -1 Then Exit Sub
  Dim MyDataSet As DataSet = DataRetrieve()
  MyDataGrid.DataSource = MyDataSet
  MyDataGrid.EditItemIndex = MyDataGrid.SelectedIndex
  MyDataGrid.DataBind()

  ' Code to respond to the Click event of the OK button:
  ' Desc: Cycles through the TextBox controls used during a
  ' standard edit, puts the values back in the DataSet, then
  ' rebinds. Add error handling as appropriate.
  ' NOTE: This code relies on the first column being a
  ' selection (>) button (it starts counting the cells from
  ' position 1, not 0). If you remove that button, you may
  ' have to change this code.
  If DataExists() = False Then Exit Sub
  If MyDataGrid.EditItemIndex = -1 Then Exit Sub
  Dim intCount As Integer
  Dim MyDataSet As DataSet = DataRetrieve()
  With MyDataGrid
    For intCount = 1 To .Items(.EditItemIndex).Cells.Count
      If intCount = .Items(.EditItemIndex).Cells.Count _
         Then Exit For
      ' Check that a control exists in this position
      If .Items(.EditItemIndex).Cells(intCount).Controls. _
         Count Then
        ' Check for a standard TextBox
        If TypeOf (.Items(.EditItemIndex).Cells(intCount). _
           Controls(0)) _
          Is TextBox Then
          Dim strValue As String = CType(.Items(. _
            EditItemIndex). _
            Cells(intCount).Controls(0), TextBox).Text
          If strValue <> "" Then
            ' This isn't null, so store value
            MyDataSet.Tables(0).Rows(.EditItemIndex).Item( _
              intCount - 1) = strValue
          Else
            ' Treat empty value as null
            MyDataSet.Tables(0).Rows(.EditItemIndex).Item( _
              intCount - 1) = System.DBNull.Value
          End If
        End If
      End If
    Next
    .SelectedIndex = -1
    .EditItemIndex = -1
    DataSave(MyDataSet)
    .DataSource = MyDataSet
    .DataBind()
  End With

  ' Code to respond to the Click event of the CANCEL button:
  ' Desc: Used to cancel an edit. Deselects an selected rows
  ' and exits the edit mode, then rebinds.
  If DataExists() = False Then Exit Sub
  MyDataGrid.SelectedIndex = -1
  MyDataGrid.EditItemIndex = -1
  MyDataGrid.DataSource = DataRetrieve()
  MyDataGrid.DataBind()

  ' Code to respond to the Click event of the UPDATE button:
  ' Desc: Updates the underlying database, then rebinds.
  ' Add error handling code as appropriate.
  If DataExists() = False Then Exit Sub
  MyDataAdapter.Update(DataRetrieve)
  MyDataGrid.DataSource = DataRetrieve()
  MyDataGrid.DataBind()

Top Tip: Receive a "Login failed" error when running this code? Your Web application is attempting to gain access to your database, but it doesn't have permission. You'll need to do one of two things: either change the ConnectionString property of your Connection object to use a valid SQL Server username and password, rather than "integrated security" (launch the Enterprise Manager to set up new users, then see the "Finding the Last Identity Number Added" tip code for an example username/password SQL Server connection string), or use the Computer Management tool to increase permissions of the ASPNET user account (not recommended for security purposes).

From this code base, you can do practically anything using the Web DataGrid and a little imagination. You could create a form that allows you to add items through regular input boxes, then adds a new row to the editable DataGrid in code. You could modify it so the DataSet actually contains items in a user's shopping basket, with an update feature to change quantities. You could simply use it to create a power user system that allows a privileged few to access and edit data in key administration tables within your company database.

In short, you can display your data on the Web in many different ways—and everyone wants to do something slightly different. Here, I've provided a standard code framework that will allow you to perform the most commonly requested tasks: adding, deleting, editing, and updating records in a table. It's now up to you to customize and take this base model to new heights.

Top Tip: Although you may be working with the DataGrid here, don't be afraid to access your data directly through the DataSet object. In the Essentials section, we looked at sample code to do this—and you can easily mix that code in with the preceding templates for a more personalized, powerful solution.

The techniques that follow this tip will be particularly useful to those who have become acquainted with the Web DataGrid and how it works. They gradually get more advanced, assuming a working knowledge of the DataGrid and ADO.NET technologies. So stay curious and have fun—there's a lot to do with this beast.

I'd like to conclude this first look at the Web DataGrid with one important recommendation: Although this tip and others throughout this series include code showing how to add rows and edit data in the Web DataGrid, it's troublesome and not always aesthetically pleasing. If you want the simpler life, take my advice: Just use the DataGrid to display data, and use your own individual Web form controls to accept input and edit data.

Trust me. It'll cure 95% of your DataGrid headaches and user complaints.

Figure: A selected record in my final, editable Web grid

About the Author

Karl Moore (MCSD, MVP) is an experience author living in Yorkshire, England. He is author of numerous technology books, including the new Ultimate VB .NET and ASP.NET Code Book, plus regularly features at industry conferences and on BBC radio. Moore also runs his own creative consultancy, White Cliff Computing Ltd. Visit his official Web site at www.karlmoore.com.