Now that your
SqlDataSource object is ready to
go, you only have to set up your
control. In Design view, click on the
Smart Tag and choose "Edit Columns." Verify that the checkboxes to enable editing
and deleting are selected, as shown in Figure 4-17.
If you prefer to have buttons for Edit and Delete, rather than links, click on the
Smart Tag and select "Edit Columns…" When the Fields dialog box opens, click the
Command Field entry in the Selected Fields area (lower-left corner). This brings up
the Command Field Properties in the right-hand window. In the Appearance section of
the Fields editor, choose
ButtonType and then
change Link to Button in the drop-down menu next to
ButtonType, as shown in Figure 4-18.
The result is that the commands (Edit and Delete) are shown as buttons, as shown in Figure 4-19.
Figure 4-17. Select the Smart Tag on the GridView, and check the boxes to enable editing and deleting.
Figure 4-18. Click the Smart Tag of the GridView, then click Edit Columns to get this Fields dialog box where you can select and edit the columns in the GridView. Here, the CommandField button type is being changed.
Start the application. The product database information is loaded into your
GridView. When you click the Edit button,
the data grid automatically enters edit mode. You'll notice that the editable
text fields change to text boxes and checkboxes, as appropriate, and the command
buttons change from Edit and Delete to Update and Cancel. Make a small change to
one field, as shown in Figure 4-20.
When you click the Update button for that row, the grid and the database are both updated, which you can confirm by opening the table in the database, as shown in Figure 4-21.
To open the database table, stop the application first. Then on the right side
of the IDE, click the Database Explorer tab (in VWD; it is called Server
Explorer in VS2005). Expand the AdventureWorks folder, and then expand the
Tables folder. Scroll down until you find the
(Production) table (in the IDE, the schema name is displayed in
parenthesis after the table name—go figure), then
right-click it, and select "Show Table Data." This will show you the contents of
the table from within the IDE.
Suppose you would like you to modify the grid so the contents of the Name
column are red when the MakeFlag column is checked, that is, when its value is True. In
addition, you want all the ProductNumbers that begin with the letters CA to
display in green. You can do this by handling the
RowDataBound event. As the
GridView is populated with data, each row of data is bound to the
GridView individually, and the
RowDataBound event is fired once for each
To modify the
GridView, switch to Design
view, click the
GridView, click the lightning
bolt in the Properties window, and double-click in the method name column
(currently blank) to the right of the
RowDataBound event. The IDE will create an event handler named
GridView1_RowDataBound() and then place
you in the code-behind file within the skeleton of that method, ready for you to
start typing code.
The second argument to this method is of type
GridViewRowEventArgs. This object has useful information about
the row that is databound, which is accessible through the
Row property of the event argument.
Figure 4-21. If you view the table in the database after editing it in the GridView, you'll see that the changes have been saved.
Enter the code shown in Example 4-3.
Example 4-3. Handling theRowDataBound event
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow ThenDim cellProductNumber As TableCell = e.Row.Cells(3) ' ProductNumber column If cellProductNumber.Text.Substring(0, 2) = "CA" Then cellProductNumber.ForeColor = Drawing.Color Green End If Dim cellMakeFlag As TableCell = e.Row.Cells(4) ' MakeFlag column Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox) If cb.Checked Then e.Row.Cells(2).ForeColor = Drawing.Color.Red End If End If End Sub
If statement (highlighted in
Example 4-3) tests if the type of
Row passed in as a parameter—in other
words, the row that was bound and triggered this event—is a
DataRow (rather than a header, footer, or
Once you know you are dealing with a
DataRow, you can extract the cell(s) you want to examine from
that row. Here, we will look at two cells: the
ProductNumber cell is the fourth cell in the row, at offset
(index) 3, and the
MakeFlag cell is the fifth
cell in, at offset 4. (Remember, all indices are zero-based.)
To access the
ProductNumber cell, you
define a new variable,
defined as a
TableCell with the As keyword,
and set it equal to the cell at offset 3 in the row, like this:
Dim cellProductNumber As TableCell = e.Row.Cells(3)
Once you have the cell as a variable, you want to get the text contained in
the cell to compare to your known value. You do that by accessing the Text
cellProductNumber, and then using
Substring() function, as you might
guess from its name, extracts a smaller string from a larger one. This is a
pretty simple function to work with. First, you call the function on a string,
and you give it two numbers as parameters: the index of the start of the
substring, and the length of the substring. As with all other indices, the first
character in the string is position zero. You want the first two characters from
the Text string, so the starting index is 0, and the length of the substring is
2. Therefore, to get the first two characters from your string, you use the
Substring(0,2). Once you have that
substring, you can use a simple
to compare it to the string you want to match, "CA":
If cellProductNumber.Text.Substring(0, 2) = "CA" Then
In the case of the MakeFlag, it is somewhat more complicated. It's easy enough
to isolate the cell that contains the checkbox—it's at index 4—and then assign that value to a new
Dim cellMakeFlag As TableCell = e.Row.Cells(4)
This is the same technique you used to isolate the ProductNumber cell. In this
case, though, the Text property of this cell will always be empty. However, it
does contain a
CheckBox control, which is the
only control in the cell. Instead of reading the text in
the cell, you want to read the value of the
Checked property of that
CheckBox control. Each cell has a collection of all the controls
contained in the cell, called
has a zero-based index. Since the checkbox you want is the only control in the
collection, you know it's at
cellMakeFlag.Controls(0). Next you define a new variable,
cb, which you define as a
CheckBox. Then you use the
CType function on the control you just isolated,
to convert the control to a
works because we know it is a
Dim cb As CheckBox = CType(cellMakeFlag.Controls(0), CheckBox)
If the box is checked,
true. If it is checked, you want
to set the
ForeColor property of the third
cell in the row (offset 2), the
e.Row.Cells(2).ForeColor = Drawing.Color.Red
You set the color of the cell the same way you did for
ProductNumber, but notice this time you're not
changing the color of the checkbox cell itself—you're changing a different cell
in the table.
Run the web site. It will look identical to Figure 4-19, except the product
names for which the
MakeFlag field is checked
will display in red, and some of the product numbers will display in green.
(Neither of these changes will be obvious in the printed book, so we will forego
a figure showing the color changes.)
To see how this works, drag a
from the Standard section of the Tool-box onto the Design view, below the grid
but within the
UpdatePanel control. Change
the Text property of this
Name. Then drag a
TextBox control next to the
Label. Change its
txtName and set its
ReadOnly property to
True. You now have a place to display the name of the selected
item from the grid.
Click on the Smart Tag of the
check the "Enable Selection" checkbox. This will cause a Select button to
display in the first column of the grid, next to the Edit and Delete buttons
already there, as shown in Figure 4-22.
Figure 4-22. Clicking Enable Selection in the Smart Tag causes Select buttons to appear in a GridView.
Now all you need to do is set up the event handler to respond to the Select
buttons. Double-click on the Select button in the first row of the grid. This
will open up the code-behind file with the skeleton of the
SelectedIndexChanged already created for you,
ready to accept your custom code. Enter the highlighted code from the following
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, _ ByVal e As System.EventArgs)
If GridView1.SelectedRow.RowType = DataControlRowType.DataRow Then
Dim cellName As TableCell = GridView1.SelectedRow.Cells(2) ' Name column
txtName.Text = cellName.Text
End IfEnd Sub
This code first tests to determine if the selected row is a
DataRow (as opposed to a
HeaderRow or a
it is a
DataRow, it creates a variable of
TableCell, which is assigned to the
third cell in the selected row (because of zero-based indexing, the third item
will have an index value of 2). Then the
property of the Text-Box is set equal to the
Text property of that cell.
Run the app and click on one of the Select buttons. The name from the selected
row appears in the
Sometimes you do not want to display all the records in a table. For example,
you might want to have users select a product from your grid and display the
order details for it in a second grid on the current page. To do this, you'll
need a way to select a product as well as a way to pass the
ID of the selected product to the second grid. The
Select buttons are already in place from the previous example, so all you need
to do now is pass the
ID of the selected
product to the second grid.
To keep the downloadable source code clear, copy the previous example, AWProductData to a new web site, AWProductDataOrderDetails.
See Appendix A for details about how to copy a web site.
You need to create a second
will be used to display the order details. From the Toolbox, drag the second
GridView onto the page below the first,
and then drag the
TextBox inside the
UpdatePanel. Open the Smart Tag for the
UpdatePanel. As you did earlier in the chapter, create a new data
source (name it
but use the existing connection string. Choose the
SalesOrderDetail table, select the desired columns (for this
LineTotal), and then click the Where button, as shown in Figure 4-23.
A WHERE clause is a SQL language keyword used to narrow the set of data returned by the SELECT statement. In other words, you're saying, "Get me all the records from this table, where this condition is true." The condition could be defined any number of ways—where the amount in inventory is less than 10, where the customer name is "Smith," or where the copyright date is after 1985. It all depends on the types of information you have stored in your columns.
When you click the WHERE button, the Add WHERE Clause dialog opens, which you can see in Figure 4-24. First, you pick the
column you want to match on, in this case
ProductID. Next, pick the appropriate operator for your condition
statement. Your choices include among others, equal to, less than/greater than,
like, and contains. For this exercise, use the default (=).
The third drop-down lets you pick the source for the
ProductID—that is, where you will get the term you want to match
on. You can pick from any one of several objects in the menu or choose None if
you'll be providing a source manually. In this case, you'll obtain the source of
ProductID from the first
GridView, so choose
Figure 4-23. Configuring the SalesOrderDetail table SELECT statement is similar to the way you set up the first DataSource, but this time, you'll add a WHERE clause.
When you choose
Control, the Parameter
properties panel of the dialog wakes up. You are asked to provide the
ID of the
Control containing the target parameter. Select
GridView1. Once you've made all your choices, the
screen will resemble Figure 4-24.
Click Add. When you do, the upper portion of the dialog returns to its initial
(blank) state and the
WHERE clause is added
WHERE Clause window. You could add
WHERE clauses at this point, to
further restrict the data, but we won't for this example.
Click OK to return to the Configure Select Statement dialog box. While you are at it, sort the
results by the
SalesOrderID column by
clicking on the Order By button. The Add
Clause dialog with the
selected is shown in Figure 4-25.
ORDER BY clause is another SQL keyword,
and this one does just what its name implies—it sorts the results using the
selected field for sort order.
Click OK until the Configure Data Source Wizard is finished.
Switch to Source view and again fix the name of the tables in the SQL
statements that were auto-generated. The markup for the second
GridView and its associated
SqlDataSource is shown in Example 4-4, with the corrected table
Figure 4-24. Add a Where clause to your SELECT statement with the Add WHERE Clause dialog. You select the column, the operator, and the source here.
Example 4-4. Order detail grid withSqlDataSource
<asp:GridView ID="GridView2" runat="server" DataSourceID="AdventureWorksOrderDetails"> </asp:GridView> <asp:SqlDataSource ID="AdventureWorksOrderDetails" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>" SelectCommand="SELECT [SalesOrderID], [CarrierTrackingNumber], [OrderQty], [UnitPrice], [UnitPriceDiscount], [LineTotal] FROM
WHERE ([ProductID] = @ProductID)
ORDER BY [SalesOrderID]">
Also highlighted in Example 4-4 are
the results of the
ORDER BY buttons from the Configure Select Statement Wizard.
SELECT statement now has a
WHERE clause that includes a parameterized value
(@ProductID). In addition, within the
definition of the
SqlDataSource control is a
definition of the
includes one parameter of type
ControlParameter, which is a parameter that knows how to get its
value from a control (in our example,
GridView1). In addition, a second property,
PropertyName, tells it which property in the
GridView to check. A third property,
Type, tells it that the type of the value
it is getting is of type
Int32, so it can
properly pass that parameter to the
You may now reformat your grid and edit the columns as you did for the first grid, and then try out your new page, which should look something like Figure 4-26.
The AdventureWorks database has no order details for any of the entries with ProductIDs below 707. The first entry with details is on Code-Behind Files of the grid, so be sure to move to Code-Behind Files (or later) to see product details. If you select a product that does not have any order details, the second grid will not appear.