The complete markup for the Default.aspx file in the AWProductData site is shown in Example 4-5 , with the code-behind shown directly after in Example 4-6.
Figure 4-26.When you selected a product in the first grid, the order details appear below in the second grid.
Partial Class _Default Inherits System.Web.UI.Page
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then Dim cellProductNumber As TableCell = e.Row.Cells(3) ' ProductNumber 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
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 txtName.Text = cellName.Text End If EndSub End Class
The complete markup for the Default.aspx file in the AWProductDataOrderDetails site is shown in Example 4-7, and the code-behind is shown in Example 4-8.
Example 4-7. Default.aspx for AWProductDataOrderDetails
Example 4-8. Default.aspx.vb for AWProductDataOrderDetails
Partial Class _Default Inherits System.Web.UI.Page
Protected Sub GridView1_RowDataBound(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then Dim cellProductNumber As TableCell = e.Row.Cells(3) ' ProductNumber 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
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 txtName.Text = cellName.Text End If End Sub End Class
Most useful web sites make use of a database. ASP.NET provides controls that make it easy to connect to a database, and retrieve and edit data.
The GridView is the most commonly used control for displaying data, although there are others. The GridView can sort data, and present it in pages, for easy reading.
Data controls need to be bound to a data source to display data. To do that, you provide a DataSource control, which connects to the database and retrieves the data.
You configure a DataSource control with a wizard that allows you to set a connection string, and then helps you construct a SQL query for retrieving data, or you can enter your own custom query.
You create a new connection with the Add Connection dialog, and then you can save it in your web.config file for future use.
The SQL SELECT statement allows you to specify which columns of data you want to retrieve, and from which table. The Wizard can configure this statement for you automatically.
The SQL INSERT, UPDATE, and DELETE statements allow you to add, edit, and remove data, respectively. The Wizard can also generate these statements for you automatically, and you can easily add buttons to perform these functions in your GridView.
Optimistic concurrency is a technique that protects your data by only changing the database if no one else has changed it since you read the data. Again, the Wizard can enable optimistic concurrency for you.
The WHERE SQL clause filters the data you retrieve by specifying a condition for the data. A row will only be retrieved if that condition is true.
You can create event handlers for the GridView, which enables you to take action on rows as they’re bound, and also allows you to take action on rows as they’re selected.
You can provide parameters to the SELECT query, which enables you to display data in a GridView based on the value of another control, even another GridView .
Adding the ability to access a database is arguably the most powerful improvement you can make to your site. It’s easy see how accessing a database would make the Order Form site from previous chapters that much more useful. Even the best order form, though, can’t retrieve the right data if users don’t give it valid input—if they enter a four-digit zip code, for example, or an improperly formatted credit card number. The whole thing would work much more smoothly if there was a way to check that the user’s responses are valid before you spend the time to access the database. The good news is that ASP.NET provides such a way, called validation, and that’s what you’ll learn about in the next chapter.
Brain Builder
Quiz
What type of control do you need to retrieve data from the database?
What is the name of the process for allowing a control, such as a GridView, to extract data from the retrieved tables and format it properly?
What is a connection string?
What are the four elements of CRUD?
How do you attach a data source to a GridView?
If your table has many rows, what should you do in the GridView to make it easier to read?
What does optimistic concurrency do?
How can you enable users to change the contents of the database from your GridView?
How can you take an action based on the data in a row, as the table is loaded?
How do you filter the amount of data returned from a SELECT query?
Exercises
Exercise 4-1. We’ll start out easy, letting you create your own GridView. Create a new web site called Exercise 4-1. Add to it a GridView control that shows records from the Product table with a Weight greater than 100. The GridView should list the Product ID, Product Name, Product Number, Color, and List Price. The user should be able to update and delete records, sort by rows, and page through the content. Use the Professional formatting scheme to give it some style. The result should look like Figure 4-27 .
Exercise 4-2. This one is a little trickier, but it lets you see how users could interact with the data in a GridView. Copy the web site from Exercise 4-1 to a new web site, called Exercise 4-2. Add the ability to select rows in your GridView. Add two labels and two read-only textboxes below the GridView to show the selected item’s Product Name and color. The result should look like Figure 4-28.
Exercise 4-3. Now it’s time to combine what you’ve learned from previous chapters with the new stuff, and throw a little AJAX into the mix as well. Create a new AJAX-enabled web site called Exercise 4-3. This site should have a radio button that gives readers the opportunity to select whether they want to see data from the Employee table, or the Customer table. The Employee panel should have a GridView showing the EmployeeID, ManagerID, and Title. The Customer panel should have a GridView showing the Customer ID, Account Number, and Customer Type. The table that the
Figure 4-27.Your goal for Exercise 4-1.
reader chooses should appear dynamically in a new panel; the other one should be invisible. The result should look like Figure 4-29.
Exercise 4-4. Ready for a bit of a challenge? Sure you are. You’re going to see how to retrieve data based on multiple customer selections—like you would in a shopping site. Create a new web site called Exercise 4-4. This site should have three drop-down menus:
A Category menu that lists the product categories from the ProductCategory table
A Subcategory menu that lists the subcategories of the Category listed in the first drop-down, by using the ProductSubcategory table
Figure 4-28.Your goal for Exercise 4-2.
A Color menu that lists the available product colors from the Product menu
In addition, there should be a Submit button that users click. Below all of this is a GridView that displays the Products (from the Product table) that match the chosen subcategory and color. (You don’t need to match the category—all that control does is dictate the contents of the Subcategory table.) The GridView should display the ProductID, Name, Product number, and the color, just so you can tell it’s working. (Hint: You can use the DISTINCT SQL statement to avoid duplication in your table.) It should look like Figure 4-30.
ASP.NET 
/ 5 
ASP.NET Export to Excel and Word using VB.NET and C#
