Visual Web Developer™ 2005 Express Edition For Dummies - Alan Simpson

270 Part III: Personalization and Databases

Limiting the records you retrieve from a table or view is called filtering the rows. To specify how you want rows filtered, click the WHERE button on the Configure the Select Statement page. The Add WHERE Clause dialog box opens.

The first step is to choose which column you want to filter on. For example, if you want to extract rows for a particular user only, choose UserName or UserId. If you want to retrieve rows for a specific item, choose ItemId.

After you’ve chosen a column for the filter, choose an operator. All the usual suspects are available and summarized in Table 12-1.

Table 12-1	Operators Used in a SQL WHERE Clause
Operator	Meaning
=	Equals
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
<>	Does not equal

After you’ve chosen the column name and operator, specify where the value to compare to will come from. You use the Source drop-down list for that. It offers several options, but you’re most likely to use these two:

Control: Choose this if the value you want to compare to will come from some other control on the same page, such as a drop-down list.

Profile: Choose this if the criterion for selecting records has anything to do with choosing only records that are relevant to the specific user who is viewing the page.

It’s unlikely that you’d ever have to choose Cookie, Form, QueryString, or Session from the Source drop-down list. You should be able to do anything needed here if you use either the Control or Profile source.

What happens next depends on what you chose in the steps leading up to this point. You’ll see more information and examples shortly. But for the sake of example, let’s say you chose UserName as the column to query, = as the operator, and Profile as the Source. The next step would be to specify the property name which, in this case, is UserName (same as the column name). Figure 12-13 shows an example in which only records that have the current user’s name in the UserName column of the table will be extracted from the table.

Chapter 12: Using Data in Web Pages 271

Figure 12-13:

Retrieve records for the current user only.

When you’ve specified your criterion, you must click the Add button to go on. (If you forget this step, the query won’t work and you’ll drive yourself nuts trying to figure out what’s wrong.) When you click Add, your current selections are converted to SQL down at the bottom of the dialog box. The other controls are cleared so you can create additional criteria, if need be. If you don’t need to add any more criteria, then click OK to return to the previous wizard page.

The SELECT statement you’re creating now contains a WHERE clause that looks like this:

WHERE ([UserName] = @UserName)

That’s SQL for “get records only for whichever user requested this page.”

It’s important to understand that the WHERE clause you create must refer to columns in the current query. The query shown here wouldn’t work in the Items table because there’s no UserName column in the Items table. But it works in DetailedUsersItemsView because that view does contain a

UserName column.

When you test a query that includes a WHERE clause, you’ll need to manually type in some plausible data to verify that the SELECT statement works. This means click Next, and then click Test Query. You won’t see query results immediately; instead, you see the Parameter Values Editor dialog box shown in Figure 12-14.

A parameter is a chunk of information being passed to the SQL statement. For example, we can feed the WHERE clause the parameter Bob. In return, the SQL statement spits back only records that have Bob in the UserName column.

272 Part III: Personalization and Databases

Figure 12-14:

The Parameter Values Editor dialog box.

To test the query, you must enter a realistic value into the Value box in the query editor. For example, I know for a fact that I have a user named Bob in my current membership system. So I could type that name into the Value box, (refer to Figure 12-14).

After you enter a test value and click OK, the query executes. In this example, only rows that have Bob in the UserName field are retrieved from the view, as shown in Figure 12-15.

Figure 12-15:

Only rows for user Bob are returned by the

SELECT statement.

The query results show every transaction made by user Bob. When you bind this to a data control on your page, however, the query won’t show Bob’s record on the page. It will show records for whatever user requested the page.

Given those options you just discovered, you can retrieve anything you want from your database, no matter how large or small. When you’ve accurately defined the columns and rows that your data-bound control needs, click Next. You’re taken to the Test Query page again. There you can click the Test Query button once to make sure your query is retrieving the data you need. Then click Finish when you’re certain the query provides the data your control needs.

Chapter 12: Using Data in Web Pages 273

If you tested a query previously, the results that show up on the Test Query page will be the ones left over from that previous text. To see what your current query produces, you must click the Test Query button.

Clicking Finish closes the wizard and brings you back to the Design view of the page. The data-bound control will probably look completely different (though still random and ugly, because it’s only a placeholder for data to be provided later).

Data controls in Design view

In Design view, data controls don’t look like much. The control shows column names from the underlying table or view. For example, Figure 12-16 shows a DataList control after binding it to the Items table (without the ItemId column). Instead of showing actual data from the table, the control just shows placeholders like abc for text and 0.1 for numbers.

Figure 12-16:

A

DataList control after binding to columns from the

Items

table.

To see what the data-bound control will display to people accessing your Web site normally, view the page in a Web browser. The placeholder text is replaced with actual text from the table, as shown in Figure 12-17. (There’s more text than can be seen without scrolling. But trust me, the page shows all records from the Items table.)

Keep in mind that Figure 12-17 is only an example. There are many data controls to choose from, and an infinite number of ways you can display data on a page. But the general procedure of going through the Data Configuration Wizard is the same, regardless of what data control you use or how you format your data.

274 Part III: Personalization and Databases

Figure 12-17:

The page from Figure 12-6 as seen in a Web browser.

If you ever change your mind about the columns and rows you chose for a data-bound control, it’s no big deal. Just click the control’s Common Tasks menu and choose Configure Data Source. Click Next on the first wizard page, and you’ll be taken back to the Configure SQL Statement page, where you can change which columns are retrieved, the sort order, the WHERE clause, or any combination of those.

Formatting Dates and Numbers

Unless you specify otherwise, data from SQL Server tables look on a page as they do in a table, which means money fields display in the format 29.9500 and date/times appear in the format 6/15/2006 12:00:00 AM. Of course, you’re not stuck with those formats.

Exactly how you change the format of a date or number varies from one data control to the next. (You’ll see examples in upcoming sections.) But the symbols you use to format dates and times are always the same.

The full set of things you can do, formatting-wise, are all documented in the

.NET Framework and C# documentation under the general moniker of composite formatting. The ones you’re most likely to actually use in a Web site are summarized in Table 12-2. The first one, {0:C} or {0:c}, works with any field that’s the money or smallmoney data type. The others work with fields of the datetime and smalldatetime data type.

			Chapter 12: Using Data in Web Pages 275
	Table 12-2	Formatting Symbols for Currency
		and Date/Time Columns
	Symbol	Name		Sample Output
	{0:C} or {0:c}	Currency		$29.95
	{0:d}	Short date		6/1/2006
	{0:D}	Long date		Thursday, June 01, 2006
	{0:t}	Short time		12:00 AM
	{0:T}	Long time		12:00:00 AM
	{0:f}	Full (short time)		Friday, June 02, 2006 12:00 AM
	{0:F}	Full (long time)		Thursday, June 01, 2006 12:00:00 AM
	{0:g}	General (short time)		6/1/2006 12:00 AM
	{0:G}	General (long time)		6/1/2006 12:00:00 AM

Some Security Considerations

In any given database, there is sure to be information that users should never see. There will also be much information that users are allowed to see but not change. It’s up to you to decide which is which — and to provide all the necessary security.

A relatively simple way to deal with this is to create a new folder for pages that no user (other than you) can see. Here’s how:

1.Right-click the site name at the top of Solution Explorer.

2.Choose New Folder.

3.Name this folder AdminPages.

4.Using the Web Site Administration Tool, create a new role, perhaps named Admin.

5.Add a new access rule that allows people in the Admin role to access pages in AdminPages, and denies access to both anonymous users and site members.

6.Finally, create a new user account for yourself and put yourself in both the Admin and SiteMembers roles.

You’ll need to log in to that new user account before you can view any pages you put into the new AdminPages folder.

276 Part III: Personalization and Databases

If you don’t have the slightest idea what I’m talking about here, see Chapter 3.

Using the GridView Control

As its name implies, the GridView control shows data from a table or view in a grid consisting of rows and columns. As always, you can specify exactly which columns and rows (and from which tables or views) in your database the grid should show. The GridView can be used both to display data, as well as to add, change, and delete data in a table.

If your intent is to show data to users in the GridView control, then you can bind the GridView to any table or view in your database. If you want to use the GridView to edit data in the table, then you cannot bind the control to a view. (Data from views can never be edited by the user.) Furthermore, the GridView must be bound to one table only, and that table must contain a primary key field.

An instant GridView control

If you want to use a GridView control to show all columns and rows from a single table in your database, your job is easy. Here’s what you should do:

1.In Database Explorer, click the + sign (if necessary) to expand the tables list.

2.Drag the name of a table that you created (not one of the aspnet_ tables) onto the page.

For example, Figure 12-18 shows the results of dragging a table named Items into the content area of a page that has a Master Page.

Figure 12-18:

Drag a table to a page to create an instant

GridView.

Drag

Chapter 12: Using Data in Web Pages 277

If you want to show something in a GridView other than the contents of a single table, you can create a GridView control and bind it to appropriate data using the Data Configuration Wizard. Here are the steps:

1.In the Toolbox, expand the Data category of controls.

2.Drag a GridView control from the Toolbox onto your page.

3.From the GridView’s Common Tasks menu, select Choose Data Source <New data source>.

4.Use the Data Source Configuration Wizard to specify which data you want the control to show.

When you complete the wizard and click Finish, the GridView control shows a column heading for each column you specified in the Data Source Configuration Wizard.

In a Web browser, the data will look something like Figure 12-19 (assuming you don’t do any formatting). The control actually shows all records from the table. The figure only shows a few records.

Figure 12-19:

The page

from

Figure 12-18

in a Web

browser.

278 Part III: Personalization and Databases

Formatting the GridView control

The default appearance of a GridView control isn’t necessarily pretty. But you have lots of options for making it look and act the way you want. Most of these options are available from the control’s Common Tasks menu shown in Figure 12-20.

Figure 12-20:

Common Tasks menu for a

GridView

control.

The gray SQLConnection box is just a placeholder that defines the control’s data source, and won’t show up on your Web page. You can show or hide those gray boxes by choosing View Non Visual Controls from VWD’s menu.

As with most controls, the Common Tasks menu for the GridView has an Auto Format option that you can choose to apply a predefined formatting style to the control. Other major items on the menu are as follows:

Enable Paging: Adds a navigation bar to the bottom of the GridView, allowing users to page through multiple records. Useful for displaying data from larger tables to prevent all records from showing up on a single page.

Enable Sorting: Converts each column title to a clickable link. In the browser, users can click any column heading to sort the rows by that column.

Enable Editing: Allows users to change any value in any row or column. This option is only available if the control is bound to a single table that has a primary key.

Enable Deleting: Allows users to delete records. This option is only

available if the control is bound to a single table that has a primary key.

If you enable editing or deleting, that means anybody who views the page can change or delete records in the table. If you don’t want users to

Chapter 12: Using Data in Web Pages 279

do that, be sure to put the page that contains the control into a folder that users can’t access.

Enable Selection: Allows users to select a record. That record, in turn, can be used as a filter for other data controls on the same page. You’ll see an example in the section titled “Creating Master-Details Forms.”

Formatting GridView dates and times

If your GridView control shows currency or date/times, you use the Edit Items option on the Common Tasks menu to format those columns. After you choose Edit Items, you’ll be taken to a dialog box titled Fields. Under Selected Fields in that dialog box, click the name of the field to which you want to apply a format. The Properties box for the BoundField control then shows properties for that selected field.

Scroll down through the properties until you get to the DataFormatString property and then enter your formatting code. Figure 12-21 shows an example in which I’ve applied the {0:c} formatting code to the ItemPrice control.

Figure 12-21:

Fields dialog box for a

GridView

control.

To right-align or center text within a column, scroll down a bit further in the

Properties sheet and expand the ItemStyle category. Then set the

HorizontalAlign property to Right or Center.

Arranging and hiding columns

To arrange or hide columns in a GridView, use the arrow and Delete buttons just to the right of the Selected Fields list. For example, to move a column to the left within the GridView, click its name, then click the Up button.

To hide a column from GridView display, click its name and click the black X button to the right of the Selected Fields list.