Web-Enabled Decision Support Systems

1
Web-Enabled Decision Support Systems

• Database Connectivity in Web Applications

Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

3
Introduction

• A Web-based, data-driven application is a program
  that allows users to manipulate data in a DBMS
  through a Web interface
• Databases are typically stored on a Web server
• Accessed from a Web client using a Web browser
  program
• Examples
• Online shopping sites Amazon.com, Buy.com
• Driving-direction providers Mapquest.com, Yahoo
  Maps
• Movie information portals Imdb.com
• In this chapter, we will learn how to use the
  Visual Studio environment to develop Web-based,
  data-driven applications using ASP .NET and an MS
  Access database

4
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

5
Connecting to the Access Database

• How-to Add a Database Connection Using the
  Server Explorer Window
• Create a new ASP .NET website named
  WebDBConnectivity.
• Open the Server Explorer Window by choosing the
  View Server Explorer option from the Main menu.
• Right-click on the Data Connections icon, and
  select the Add Connection option to open the Add
  Connection dialog box.

Invoking the Add Connection Dialog Box
6
Adding and Testing a Connection

1. In the Add Connection dialog box, click the
   Browse button, and locate and select the
   University.mdb database file for Chapter 19
   (see book Web site to download related files).
2. Click on the Test Connection button to verify the
   database connection. Click the OK button to add
   the connection.

Connecting to the University Database
7
Exploring Database Tables and Views

• How-to View and Modify the Data in an Access
  Database
• In the Server Explorer Window for the
  WebDBConnectivity project, open the newly added
  connection node.
• To access the database tables and queries, open
  the Tables and Views nodes.

Server Explorer Window Showing Tables and
Queries
8
Showing Table Data

• To view a database table, right-click on the
  listed table and select the Show Table Data
  option from the short-cut menu.
• Opens the table in a separate tab in the Design
  Window of Visual Studio IDE.
• Presented as a grid-like structure, very much
  like in Access Datasheet View.
• We can view and edit columns values using this
  grid interface.

Viewing Table Data in the Visual Studio
Environment
9
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

10
Displaying Data on a Web Page

• How-to Display Contents of a Database Table on a
  Web Page
• Add a Web page named Page1.aspx to the
  WebDBConnectivity project.
• Drag and drop the tblStudent table from the
  Server Explorer Window onto Page1.aspx in the
  Design Window.
• Automatically creates an AccessDataSource control
  and a GridView control.
• Configures the GridView control and sets its
  DataSourceID property to the AccessDataSource
  control.

Adding an AccessDataSource Control and Data-Bind
GridView Control
11
Testing and Formatting the GridView

1. Run the application (Ctrl F5) to view the
   student table on a Web page.
2. Select the GridView control and click on its
   smart tag, which is located on the top-right
   corner of the control. Select the AutoFormat
   option.

The Student Table Displayed on a Web Page
Formatting a GridView Control Using its
AutoFormat Feature
12
Formatting the GridView (cont.)

1. This should pop up an AutoFormat dialog box.
   Select from the available pre-defined formatting
   schemes, and click the OK button.

Selecting a Pre-Defined Formatting Scheme for a
GridView Control
ASP Tag for Data-Bind GridView Control
13
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

14
Reconfiguring a Query in an AccessDataSource

• How-to Edit a SQL Query Associated with an
  AccessDataSource
• Open the Web page, Page1.aspx, created in the
  previous section. Select the auto-generated
  AccessDataSource1 control below the GridView
  control.
• Click on the smart tag of AccessDataSource1 to
  view its Tasks list. Select the Configure Data
  Source option to open a Configure Data Source
  wizard.

Configuring an AccessDataSource Using its Tasks
List
15
Configuring the Data Source

• On the first page of the Configure Data Source
  wizard, accept the default path. Click the Next
  button.
• Since we have created the database connection
  before, the wizard page displays the path of a
  connected database.

Choose a Database Page of the Wizard
16
Configuring the Select Statement

1. On the Configure the Select Statement page,
   select the tblStudent table from the Name
   drop-down box. Select the table columns from the
   Columns area as shown below.

Reconfiguring a Select Query Statement
17
Adding a Where Clause

• On the same wizard page, click the WHERE button
  to add a WHERE clause to the query statement.
• This should pop up the Add WHERE Clause dialog
  box.

Adding a WHERE Clause to the Query
18
The Add Where Clause Dialog Box

1. Select the Class column from the Column drop-down
   box. Enter the criteria Graduate using the
   Operator and Value input areas. Click the Add
   button to add the selection criterion to the
   query. Similarly, add the criterion Class Full
   Time. Click OK to return to the wizard page.

Selection Criteria for the WHERE Clause
19
Configuring the Select Statement

1. On the Configure the Select Statement page, click
   the ORDER BY button to add an ORDER BY clause to
   the query and open the Add ORDER BY Clause dialog
   box. Select the Name column from the Sort by
   drop-down list, and specify ascending sorting
   order. Click OK to return to the wizard page.

Adding an ORDER BY Clause to the Query
20
Advanced Configuration and Testing

1. Click on the Advanced button on the Configure the
   Select Statement page to open the Advanced SQL
   Generation Options dialog box. Select the
   generate CheckBox control, and click the OK
   button to return to the wizard page.
2. Click the Next button on the Configure the Select
   Statement page to open the Test Query wizard
   page. Use the Test Query button to test the query.

Generating INSERT, UPDATE, and DELETE Queries
for a Data Source
21
Testing the Application

• Click the Finish button. Confirm GridView column
  changes by clicking the Yes button to refresh the
  columns and the key of the GridView control.
• Test the application to verify the change.
• Note that the records are now sorted in ascending
  order of the Name column.

Student Page with Reconfigured Query
AccessDataSource ASP Tag
22
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

23
Paging and Sorting

• If a data source has hundreds of records to
  display, it may clutter a Web page
• The GridView control provides a well-structured
  and well-formatted solution
• Through paging, the control allows us to display
  a large number of records on multiple pages
• Users can view a fixed number of records at a
  time
• Navigate through multiple pages to view all the
  records
• When the data-bind GridView controls data source
  supports a sorting operation, extending this
  functionality to a Web interface is easy
• In this section we will
• Enable paging and sorting features for a GridView
  control
• See how to select, edit and delete a row in a
  GridView control

24
Using GridViews Tasks List

• How-to Enable Paging, Sorting, and Data
  Manipulation for a GridView
• Continue with Page1.aspx from the previous
  sections. Click on the smart tag of the GridView
  control to view its Tasks list.
• Check the Enable Paging, Enable Sorting, Enable
  Editing, Enable Deleting, and Enable Selection
  options from the Tasks list as shown below.

Using GridViews Tasks List
25
Testing the Application

1. Run and test the application.

Selecting a Student Record
Updating a Student Record
Paging Functionality
26
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

27
Adding an AccessDataSource Control

• How-to Display Data on a Web Page from a
  Multiple Table Query
• Add a new Web page, Page2.aspx, to the
  WebDBConnectivity project.
• Open the Toolbox window by choosing the View
  Toolbox option from the Main menu.
• Drag and drop the AccessDataSource control under
  the Data category in the Toolbox onto Page2.aspx.

Adding an AccessDataSource from the Toolbox onto
a Page
28
Selecting a Database

1. Click on the smart tag of the AccessDataSource
   control to view its Tasks list. Select Configure
   Data Source to open the Configure Data Source
   wizard.
2. Select the University.mdb database file, and
   click on the Next button.
3. On the Configure the Select Statement page,
   choose the Specify a custom SQL statement or
   stored procedure option, and click the Next
   button.

Selecting the Existing Database for an
AccessDataSource
29
The Query Builder Dialog Box

• Click the Query Builder button on the Define
  Custom Statements or Stored Procedures page to
  open the Add Table dialog box. Select the
  tblFaculty and tblDepartment tables and click the
  Add button.
• Query Display the details of faculty members
  who joined the university after 1995 and have a
  salary of more than 50,000.

Designing a Multi-Table Faculty Query in the
Query Builder Dialog Box
30
Query Building and Adding a GridView

• In the Query Builder dialog box, select the
  appropriate columns. In the Filter column of the
  grid, enter the comparison values for the Salary
  and JoiningDate fields. Click the Execute Query
  button test the query. Click OK.
• Click the Finish button to close the Configure
  Data Source wizard.
• Drag and drop a GridView control onto Page2.aspx.
  
• Use the Choose Data Source drop-down list of the
  GridView controls Tasks list to select the
  AccessDataSource1 data source created in steps
  1-9.

Binding a GridView Control to an
AccessDataSource
31
Formatting GridView and Testing

1. Select the AutoFormat option, and choose the
   RainyDay template.
2. Select the Enable Paging, Enable Sorting, and
   Enable Selection options.
3. Set Page2.aspx as the start page for the
   application.
4. Run and test the application.

List of Faculty Members on a Web Page
AccessDataSource ASP Tag
32
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

33
Reconfigure the SQL Query

• How-to Display Data-Bind Images in a GridView
  Control
• Click on the smart tag of the AccessDataSource1
  control to view its Tasks list. Choose the
  Configure Data Source option to open the
  Configure Data Source wizard.
• On the Define Custom Statements or Stored
  Procedures page of the wizard, modify the
  existing SQL query to add the Picture field from
  the faculty table.

Adding the Picture Field from the Faculty
Table to the SQL Query
34
Testing the Application

• Run and test the application.
• We should see the path of the image file rather
  than the image itself.
• This is because the table column actually has
  image paths stored.
• Actual images are stored externally to the
  database.

Faculty Information with the New Picture Column
35
Editing GridView Columns

• Open the GridView controls Tasks list, and
  select the Edit Columns option to open the Fields
  dialog box.
• Delete the existing Picture column. Select the
  Picture column from the list under the Selected
  fields area, and click the Delete button.
• Note that removing the Picture field from the
  GridView control does not remove it from the
  AccessDataSource.

Accessing the Edit Columns Option
Removing the Picture Field
36
Configuring the ImageField

1. From the Available fields area, select the
   ImageField entry and click Add.
2. Select the newly added ImageField, and view its
   properties in the ImageField Properties pane. Set
   the HeaderText property to the text Picture and
   the DataImageUrlField property to the data source
   column, Picture. Click OK.

Adding an ImageField
Binding the ImageField to the Picture Column
37
Testing the Application

1. Run and test the application.

Running Application with an ImageField
The ImageField ASP Tag
38
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

39
Adding a Page and AccessDataSource

• How-to Use Parameterized Queries and Provide
  Look-Up and Filter Functionalities on a Web Page
• Add another page, Page3.aspx, to the
  WebDBConnectivity project.
• Add an AccessDataSource from the Toolbox Window
  onto the Web page.
• Click the smart tag of the AccessDataSource, and
  choose the Configure Data Source option to open
  the Configure Data Source wizard.
• Select the University.mdb database file, and
  click the Next button.

40
Configuring the Select Statement

1. On the Configure the Select Statement page,
   select tblDepartment from the Name drop-down
   list, and select the DeptID and Name columns as
   shown.
2. Drag and drop the DropDownList control from the
   Toolbox onto page.

Configuring an AccessDataSource to Query
the tblDepartment Table
41
Configuring the DropDownList

1. Click the smart tag of the DropDownList control
   to open its Tasks list. Select the Choose Data
   Source option to open the Data Source
   Configuration wizard with the Choose Data Source
   page on top.

Adding a DropDownList Control to a Web Page
42
Configuring the DropDownList (cont.)

1. Select the AccessDataSource1 control from the
   Select a data source drop-down list. Select the
   Name field for the field to display and DeptID
   field for the value field. Click OK.
2. Check the Enable AutoPostBack option of the
   DropDownListss Tasks list.
3. Drag and drop a Label control just above the
   DropDownList control, and set its Text property
   to Select a Department.

Specifying Data Source and the Display and
Value Fields
43
Setting the Start Page and Testing

1. Set Page3.aspx as the start page of the
   application.
2. Run and test the application.

Running Application
DropDownList and AccessDataSource Tags
44
Configuring the AccessDataSource

1. Drag and drop the AccessDataSource control onto
   the page. Use the controls Tasks list to open
   the Configure Data Source wizard.
2. On the Configure the Select Statement page,
   select the tblStudent table and its columns as
   shown below.

Selecting Fields for the Student Query
45
Adding a Where Clause and a GridView

1. Click the WHERE button to add the WHERE clause to
   the query design. Set the Add WHERE Clause dialog
   box as shown. Click the Add button.
2. Test the query and click Finish to close the
   Configure Data Source wizard.
3. Drag and drop a GridView control onto the page.
   Use the controls Tasks list to set its data
   source to the AccessDataSource2 control created
   in step 13. Also, choose the Enable Paging option.

Adding a WHERE Clause Specifying a Parameter
and its Value Source
46
Testing the Application

1. Run and test the application.

Application Output Department Lookup and
Filtered Student Records
AccessDataSource ASP Tag
47
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

48
Enabling Selection and Adding Controls

• How-to Use the DetailsView Control to Show
  Details about a Record
• Open the Page3.aspx file of the WebDBConnectivity
  project.
• Click on the smart tag of the GridView control to
  open its Tasks list. Check the Enable Selection
  option from the list.
• When the user clicks this Select link, we display
  detailed information about the selected student.
• Add another AccessDataSource control,
  AccessDataSource3, to the Web page.
• We will fetch the students details using this
  data source.
• Use AccessDataSource controls Tasks list to
  invoke the Configure Data Source wizard.

49
Configuring the Select Statement

1. Choose the University database on the first page
   of the wizard. On the second page, Configure the
   Select Statement, select the tblStudent table and
   its columns as shown.

Configuring a Select Query for the DetailsView
Controls Data Source
50
The Where Clause

1. Click on the WHERE button to add the WHERE clause
   to the select query. Follow below to set the Add
   WHERE Clause dialog box. Click the Add button.
   Click the OK button to return to the Configure
   Data Source wizard.
2. On the same page of the wizard, click the
   Advanced button, and select the Generate INSERT,
   UPDATE, and DELETE statements option.

Specifying the WHERE Clause and Linking its
Value Source
51
The DetailsView Control

1. Drag and drop the DetailsView control from the
   Toolbox onto the Web page.
2. Click on the smart tag of the DetailsView control
   to view its Tasks list. In the Choose Data Source
   drop-down list, select the AccessDataSource3
   control to assign the DetailsView controls data
   source.

Adding a DetailsView Control
52
The DetailsView Control (cont.)

1. Check the Enable Inserting, Enable Editing, and
   Enable Deleting options for the DetailsView
   control. Also, use the AutoFormat option to set
   the formatting of the DetailsView control.

Binding the DetailsView Control to
AccessDataSource3 Enabling its Insert, Update,
and Delete Functions
53
Testing the Application

1. Run and test the application.

Running Application with the DetailsView Control
DetailsView ASP Tag
54
Displaying Student Pictures

1. Click the Edit Fields option from the Tasks list
   of the DetailsView control to open the Fields
   dialog box.
2. Remove the existing Picture field from the list
   under the Selected fields area. Add an ImageField
   from the Available fields list to Selected
   fields list.
3. Set the HeaderText property to Picture, and set
   the DataImageUrlField property to the data source
   field, Picture. Click OK.

Adding a Template ImageField and Binding it to
the Student Picture Field
55
Testing the Application

1. Run and test the application.

Ability of a DetailsView Control to Update
Records
DetailsView Displaying a Students Picture
56
DetailsView ASP Tag
Fields Tag of the DetailsView Control Showing
ImageField ASP Tag
57
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

58
Adding a Repeater Control

• How-to Use a Repeater Control on a Web Page
• Add another page, Page4.aspx, to the
  WebDBConnectivity project.
• Drag and drop a Repeater control from the Data
  tab of the Toolbox onto the page.

Adding a Repeater Control
59
The AccessDataSource

1. Drag and drop an AccessDataSource control to the
   Web page, and configure it using the Data Source
   Configuration wizard. Configure the Select query
   statement of the data source as shown below.

Configuring Faculty Information for the Repeater
Controls Data Source
60
Configuring the Repeater Control

1. Click on the smart tag of the Repeater control to
   open its Tasks list. Select AccessDataSource1
   from the Choose Data Source drop-down list.
2. Switch to the Source tab of the Page4.aspx file,
   and write the code shown below for the
   ItemTemplate tag under the ASP Repeater tag.

Binding the Repeater Control
Defining a Template of a Repeater Control
61
Setting Start Page and Testing

1. In the Solution Explorer Window, set the
   Page4.aspx as a start page of the application.
   Run and test the application.

Faculty List Displayed in the Repeater Control
62
Adding an Image ASP Tag and Testing

1. Modify the pages HTML source by adding the code
   in lines 16 and 17 as shown below. We use an
   Image ASP tag with theImageUrl property set to
   the output of the Eval function called with
   Picture column as its input parameter.
2. Run and test the application.

Adding an ASP Image Tag
Faculty Repeater Control with Faculty Pictures
63
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

64
Web-Based Crystal Reports

• In this section, we will design a simple crystal
  report with the following requirements
• Display the students contact information (Name,
  Email, and Phone) for all students from the
  Industrial and Systems Engineering (ISE)
  department.
• Group the records by student class.
• Display a bar chart for the number of ISE
  students in each of the five student classes.
• How-to Design Simple Crystal Reports
• Add a new page, Page5.aspx, to the Web site we
  have developed so far.
• Add a new crystal report to the project by
  selecting a Crystal Report item in the Add New
  Item dialog box. Name the report,
  StudentReport.rpt.

65
The Report Expert Wizard

1. A Report Expert wizard should start and open the
   first wizard page, Crystal Reports Gallery.
   Select the default Using the Report Wizard option
   and the Standard option in the Choose an Expert
   list. Click OK.

Selecting the Report Expert
66
Selecting a Data Connection

1. On the Data page, choose the Access/Excel DAO
   item under the Create New Connection option in
   the Available Data Sources pane to open the
   Connection dialog box. Browse and select the
   University.mdb file, and click Finish to return
   to the wizard page.

Selecting a Data Connection for the Report
67
The Data Page

1. On the Data page, expand the Tables node and
   select the tblStudent table. Click on the gt
   button to move it to the Selected Tables pane.
   Click the Next button.

Selecting the Data Source Table for the Report
68
The Fields Page

1. On the Fields page, expand the tblStudent node to
   view available fields for the report. Add the
   StudentID, DeptID, Name, Class, Email, and Phone
   fields of the student table from the Available
   Fields pane to the Fields to Display pane. Click
   the Next button.

Selecting the Required Fields for the Report
69
The Grouping Page

1. On the Grouping page, add the tblStudent.Class
   field from the Available Fields pane to the Group
   By pane. Accept the default in ascending order
   sorting, and click the Next button.

Selecting the Group By Field for the Report
70
The Summaries Page

1. On the Summaries page, add the tblStudent.StudentI
   D field from the Available Fields pane to the
   Summarized Fields pane.
2. Select the StudentID summary field in the right
   pane, and choose the Count operation from the
   drop-down list below that pane. Click Next.

Selecting the Summary Field for the Report
71
The Group Sorting Page

1. On the Group Sorting page of the wizard, accept
   the default selection and click the Next button.

Selecting the Group Ordering for the Report
72
The Chart Page

1. On the Chart page of the Expert wizard, choose
   the Bar Chart type. Select tblStudent.Class as
   the On Change of field (X-axis) and Count of
   StudentID as the Show summary field (Y-axis).
   Appropriately name the chart, and click the Next
   button.

Selecting the Chart Type, Title, and Data
73
The Record Selection Page

1. On the Record Selection page, move the
   tblStudent.DeptID field from the Available Fields
   pane to the Filter Fields pane.
2. Select the DeptID field in the Filter Fields
   section and choose the is equal to item from
   the drop-down list. We assign the value of the
   expression to ISE in the drop-down list as
   shown below. Click the Next button.

Applying Filtering Criteria
74
Adding a CrystalReportViewer Control

1. On the Report Style page of the wizard, click the
   Finish button to close the wizard and to add the
   report to the project.
2. Open the Page5.aspx page. Drag and drop a
   CrystalReportViewer control from the Toolbox on
   the Web page.

Adding a CrystalReportViewer Control to a Web Page
75
Binding a Database to the CrystalReportViewer

1. Click on the smart tag of the CrystalReportViewer
   control to view its Tasks list. In the Choose
   Report Source combo box, select the New Report
   Source option to open the Create a
   CrystalReportSource Control dialog box.
2. Accept the default control name. In the Specify a
   Crystal Report drop-down list, select
   StudentReport.rpt. Click OK.

Choosing the Report Source
Creating a CrystalReportSource
76
Setting Start Page and Testing

1. Set Page5.aspx as the start page. Run and test
   the application.

Page Displaying the Student Crystal Report
77
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

78
Programmatically Accessing a Database

• How-to Access a Database Programmatically at
  Run-Time
• Add another page, Page6.aspx, to the application.
  
• Drag and drop the GridView control onto the Web
  page, and set its ID to grvCourses. Format the
  grid using the AutoFormat option.
• Drag and drop the DropDownList control onto the
  page, and set its ID to cmbDept. Also, add a
  Label control, Select Department, next to the
  drop-down list. Click on the smart tag of the
  DropDownList control to view its Tasks list, and
  check the Enable AutoPostBack option.

79
Adding Code

• We will write Visual Basic code that will
• Identify the location of the database in the
  OledbConnection object.
• Specify what data to fetch in the OledbCommand
  object.
• Post the results into an OledbDataReader object.
• To use the discussed Oledb objects, we must
  include the reference to the OleDb library. This
  is done using the Imports command (lines 1-2).
  The location of the database is stored in the
  connString variable (line 7).

Importing the OleDb Library
80
Adding Code (cont.)

• Enter the code shown below for the Page_Load
  event of the Web page.
• Executed when a page is loaded for the first
  time.
• Populates the list of departments into the
  DropDownList control.

Page_Load Event Populating the DropDownList
81
Adding Code (cont.)

1. Enter the code shown below in the DropDownList
   controls SelectedIndexChanged event handler.

Update Course Details for a Selected Department
in the GridView
82
Setting Start Page and Testing

1. In the Solution Explorer, set the Page6.aspx as
   the start page. Run and test the application.

Running the Application Displaying Courses from
the Selected Department
83
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

84
In-Class Assignment

• Design a Web application that retrieves and
  displays student name, picture, type, class, and
  grade in a GridView control based on the section
  number selected from a drop-down list.
• Use the look-up and filter functionalities to
  retrieve data from the transcript, section, and
  student tables.
• Include a DetailsView control that displays the
  details of the section selected from the
  drop-down list.
• Enable edit operations for the DetailsView
  control.
• Present well-formatted views of the GridView and
  DetailsView control.
• Hint Use the AutoFormat option.

85
Overview

• 19.1 Introduction
• 19.2 Connecting to the Database Using the Server
  Explorer Window
• 19.3 Hands-On Tutorial Displaying Data on a Web
  Page
• 19.4 Reconfiguring SQL Query in an
  AccessDataSource
• 19.5 Paging, Sorting, and Data Manipulation in a
  GridView Control
• 19.6 Hands-On Tutorial Displaying Data on a Web
  Page from a Query
• 19.7 Hands-On Tutorial Displaying Images in a
  GridView Control
• 19.8 Hands-On Tutorial Adding Look-up and Filter
  Functionalities
• 19.9 Hands-On Tutorial Displaying Related Data
  in a DetailsView Control
• 19.10 Hands-On Tutorial Working with the
  Repeater Control
• 19.11 Web-based Crystal Reports
• 19.12 Programmatically Accessing the Database at
  Run-Time
• 19.13 In-Class Assignment
• 19.14 Summary

86
Summary

• A Web-based, data-driven application is a program
  that allows users to manipulate data in a DBMS
  through a Web interface.
• The Server Explorer is used to create and
  manipulate database connections.
• Used to view and edit the data in database
  tables.
• Instead of displaying all the records in the data
  source on a single Web page, the GridView control
  automatically puts them on multiple pages.
• This feature is called paging.
• When the data-bind GridView controls data source
  supports a sorting operation, extending this
  functionality to a Web interface is easy.

87
Summary (cont.)

• We use parameterized queries to provide filtering
  capability to a Web page.
• The DetailsView control is used to display
  related information on a Web page.
• An ideal choice to display one record at a time
  on a Web page.
• The Repeater control does not have a built-in
  rendering of its own.
• Hence, we must provide the layout by creating
  templates.