WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Welcome to the final part of the ASP.NET DataGrid mini-series. Today, we'll be looking at implementing the likes of paging and sorting in your DataGrid... but start off with that most gruesome of tasks: adding an editable drop-down box to your DataGrid!
The Lowdown on Using Dropdown Boxes in a Web Grid
Want to add a dropdown box to your Web page, populated with values from your table? You're in for a sweet surprise—it's easy!
Just add the DropDownList control to your Web form and set the DataSource, DataMember, DataTextField, and possibly the DataValueField properties. As soon as you fill the DataSource, the bound DropDownList will automatically populate itself with a list of fields and values. You then can find out what the user selects through its SelectedItem property.
If you want to add dropdown boxes inside your Web grid, however, you're in for a shocker. It's pretty darn difficult.
Practically every public sample demonstrating the use of dropdown boxes in Web grids is unfeasible: The samples are based on pure text fields (not foreign keys); the old selected dropdown values aren't preserved when you enter Edit mode; the update code is always buggy as hell. But there are so many different ways of implementing a dropdown box solution, it's hardly surprising so much confusion abounds.
I spent the best part of one whole week figuring out how dropdown boxes worked with the DataGrid control—and, here, I'm going to reveal the quickest, most stable technique I found for handling the situation. My solution is based on what I deem to be the most common use for dropdown lists: allowing the user to select an 'English' value for a foreign key field. For example, you may have a news article table containing a "reporterid" field. Your actual list of reporter names, however, will most likely be in another table altogether. Your task is to make the fact that your main table uses a foreign key totally transparent: The user just wants to be able to select a reporter, period. That's what this tip shows you.
To begin with, let's get access to our core data. Locate your "parent" and "child" tables via a data connection in the Server Explorer (View > Server Explorer), then drag-and-drop onto your Web form. When you've finished, you'll be left with one Connection object and two DataAdapter objects (as usual, you may wish to alter the ConnectionString property of the Connection object to specify a 'user id' and 'password' value). Right-click on one of the DataAdapter objects and select "Generate Dataset". In the list of tables, check both your tables. Leave the rest of the defaults and click OK. An XSD template will be created and a typed DataSet added to your form, which we'll use to feed our DataGrid. So far, this is all pretty standard stuff.
Figure: My Web form so far, hosting one Connection, two DataAdapters, and a typed DataSet
Top Tip: Don't be too afraid of graphically dragging and dropping tables. Back in the Visual Basic 6 days, visually designing database applications like this was deemed bad practice. With VB.NET however, it's exactly the same as instantiating objects and using them in code—just a little easier.
Add a DataGrid to your Web form and design it as necessary. When ready, open the Property Builder and, on the General property sheet, specify the DataSource, DataMember and, optionally, the Data key field. These values should all point to the parent "news article" table in your DataSet. Next, switch to the Columns property sheet, uncheck "Create columns automatically at run time", and add an "Edit, Update, Cancel" button to the selected columns list. This button will be used to begin editing of a row.
Next, manually add all the data fields you wish to view in your grid to the selected columns list—ensuring you include both the primary key and foreign key fields, making both invisible by unchecking the Visible checkbox. Spend a few minutes setting any column properties required, such as "Header text".
Then, cycle through each of those data columns and choose a 'Sort expression' from the dropdown box. Make sure you choose the name of the field supplying data for that column. Although this 'Sort expression' value is typically used for sorting columns of data (see the "Sorting in Seconds, with Your Web Grid" tip), we use it as a record of which columns bind to which database fields. You'll see why later.
Finally, add a "Template Column" to the list. This is the column that will hold your dropdown box. Position it appropriately and change the "Header text" if required. Click OK to close the Property Builder when finished.
Figure: Setting properties for our DataGrid columns, through the Property Builder
Moving on, and it's time to set up this new template column. Right-click on your DataGrid, select Edit Template, and choose the column you just added. The middle two portions of this "template" are most important: The ItemTemplate section displays what will appear in the cell when you're viewing data, and the EditItemTemplate shows how that cell will look when you're editing data.
In the ItemTemplate section, drag and drop a DropDownList control. We want this to display a noneditable list of options, with one selected. Change the Enabled property of the control to False and provide it with a name through the ID property. I'm calling mine "ddl1".
In the EditItemTemplate section, drag and drop another DropDownList control. This is what the user will see when they enter Edit mode. Keep its Enabled property set to True this time, so they can change the selection, then change the ID property. I'm setting mine to "ddl2".
Figure: Designing how our template cell will look, in "view" and "edit" mode
Next, we need to think about getting our actual list of options into our dropdown boxes. Change the DataSource property of both boxes to point to our main DataSet, and the DataMember to point to the child "reporter" table. Next, you need to choose the DataTextField (the field that contains the text options you want the dropdown to display) and the DataValueField (the field containing the key value for this option, such as an "id" field). As soon as we populate the DataSet, these dropdown boxes will automatically fill with values from our table.
When finished, right-click on the DataGrid and choose End Editing. Notice how your DataGrid appears now?
Figure: How the DataGrid looks so far, dropdown box and all
Next, let's think about adding code. Much of this will be relatively standard; however, we'll also need to do a little special processing. For example, we'll need to write code to initially select the correct value from our dropdown field—taking the foreign key value and choosing an appropriate option from the list. We'll also need to handle the update, by taking the value from our dropdown and putting it into the foreign key field, then cycling through the edited text boxes and updating the backend DataSet as you would usually. As you'll see, the DataGrid really does very little of this work for you.
We'll begin with a simple chunk of standard code, to be added in response to the page Load event:
If Not IsPostBack Then ParentNewsArticleDataAdapter.Fill(MyDataSet) ChildReporterDataAdapter.Fill(MyDataSet) MyDataGrid.DataBind() End If
Here, we're simply using our two DataAdapters to fill our DataSet with two chunks of information: the table containing our parent "news article" data, and the child table containing our "reporter" data. We've already set the DataGrid DataSource and DataMember properties, so we simply do a .DataBind to put it into action.
This is all standard stuff. Next, we're going to add a little special code to run after we've initiated the .DataBind. We'll add it to respond to the grid ItemDataBound event, which fires once for every row "item" being displayed in a data bound DataGrid. Here goes:
' If this is a valid item... If e.Item.ItemType = ListItemType.AlternatingItem Or _ e.Item.ItemType = ListItemType.Item Then ' Retrieve the foreign key value Dim intForeignKey As Integer = _ e.Item.Cells(FOREIGN_KEY_COL_NUM).Text ' Find the dropdown containing our list of options Dim ddl As DropDownList = _ CType(e.Item.FindControl("ddl1"), DropDownList) ' Select the correct entry in the dropdown ddl.SelectedIndex = _ ddl.Items.IndexOf( _ ddl.Items.FindByValue(intForeignKey)) End If
Here, we check that we're dealing with a valid item. We then begin by retrieving our foreign key value (replace FOREIGN_KEY_COL_NUM with the column number containing your foreign key value. You may wish to use the Property Builder to help find the column number, remembering to start counting at zero and include any hidden fields). Next, we find our dropdown list on the row, then select the relevant item depending on the foreign key value. Do you see how that works?
So, that's selected the DataGrid dropdown in regular "view" mode. Now how about editing?