Transcript Developing Web Based Database Applications with ASP.NET and VB

Web-Enabled Decision Support Systems
Database Connectivity in Web Applications
Don McLaughlin
IE 423
West Virginia University
[email protected]
(304) 293-0388
1
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
2
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
3
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
4
Connecting to the Access Database

How-to: Add a Database Connection Using the Server Explorer Window
1. Create a new ASP .NET website named WebDBConnectivity.
2. Open the Server Explorer Window by choosing the View | Server Explorer
option from the Main menu.
3. 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
5
Adding and Testing a Connection
4. 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).
Connecting to the
University Database
5. Click on the Test Connection button to verify the database connection. Click
the OK button to add the connection.
6
Exploring Database Tables and Views

How-to: View and Modify the Data in an Access Database
1. In the Server Explorer Window for the WebDBConnectivity project, open the
newly added connection node.
2. To access the database tables and queries, open the Tables and Views
nodes.
Server Explorer
Window Showing
Tables and Queries
7
Showing Table Data
3. 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
8
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
9
Displaying Data on a Web Page

How-to: Display Contents of a Database Table on a Web Page
1. Add a Web page named “Page1.aspx” to the WebDBConnectivity project.
2. 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
10
Testing and Formatting the GridView
3. Run the application (Ctrl + F5) to view the student table on a Web page.
The Student Table
Displayed on a
Web Page
4. 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.
Formatting a GridView
Control Using its
AutoFormat Feature
11
Formatting the GridView (cont.)
5. This should pop up an AutoFormat dialog box. Select from the available predefined formatting schemes, and click the OK button.
Selecting a Pre-Defined
Formatting Scheme for a
GridView Control
ASP Tag for Data-Bind
GridView Control
12
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
13
Reconfiguring a Query in an AccessDataSource

How-to: Edit a SQL Query Associated with an AccessDataSource
1. Open the Web page, Page1.aspx, created in the previous section. Select the
auto-generated AccessDataSource1 control below the GridView control.
2. 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
14
Configuring the Data Source
3. 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
15
Configuring the Select Statement
4. 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
16
Adding a Where Clause
5. 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
17
The Add Where Clause Dialog Box
6. 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
18
Configuring the Select Statement
7. 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
19
Advanced Configuration and Testing
8. 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.
Generating INSERT,
UPDATE, and DELETE
Queries for a Data Source
9. 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.
20
Testing the Application
10. Click the Finish button. Confirm GridView column changes by clicking the
Yes button to refresh the columns and the key of the GridView control.
11. 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
21
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
22
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 control’s 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
23
Using GridView’s Tasks List

How-to: Enable Paging, Sorting, and Data Manipulation for a GridView
1. Continue with Page1.aspx from the previous sections. Click on the smart tag
of the GridView control to view its Tasks list.
2. Check the Enable Paging, Enable Sorting, Enable Editing, Enable Deleting,
and Enable Selection options from the Tasks list as shown below.
Using GridView’s
Tasks List
24
Testing the Application
3. Run and test the application.
Selecting a
Student Record
Updating a
Student Record
Paging
Functionality
25
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
26
Adding an AccessDataSource Control

How-to: Display Data on a Web Page from a Multiple Table Query
1. Add a new Web page, Page2.aspx, to the WebDBConnectivity project.
2. Open the Toolbox window by choosing the View | Toolbox option from the
Main menu.
3. 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
27
Selecting a Database
4. 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.
5. Select the University.mdb database file, and click on the Next button.
Selecting the Existing Database for an AccessDataSource
6. On the Configure the Select Statement page, choose the Specify a custom
SQL statement or stored procedure option, and click the Next button.
28
The Query Builder Dialog Box
7. 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
29
Query Building and Adding a GridView
8. 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.
9. Click the Finish button to close the Configure Data Source wizard.
10. Drag and drop a GridView control onto Page2.aspx.
11. Use the Choose Data Source drop-down list of the GridView control’s Tasks
list to select the AccessDataSource1 data source created in steps 1-9.
Binding a GridView
Control to an
AccessDataSource
30
Formatting GridView and Testing
12. Select the AutoFormat option, and choose the RainyDay template.
13. Select the Enable Paging, Enable Sorting, and Enable Selection options.
14. Set Page2.aspx as the start page for the application.
15. Run and test the application.
List of Faculty Members
on a Web Page
AccessDataSource
ASP Tag
31
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
32
Reconfigure the SQL Query

How-to: Display Data-Bind Images in a GridView Control
1. 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.
2. 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
33
Testing the Application
3. 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
34
Editing GridView Columns
4. Open the GridView control’s Tasks list, and select the Edit Columns option to
open the Fields dialog box.
Accessing the
Edit Columns
Option
Removing the
Picture Field
5. 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.
35
Configuring the ImageField
6. From the Available fields area, select the ImageField entry and click Add.
Adding an
ImageField
Binding the ImageField
to the Picture Column
7. 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.
36
Testing the Application
8. Run and test the application.
Running Application with an ImageField
The ImageField ASP Tag
37
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
38
Adding a Page and AccessDataSource

How-to: Use Parameterized Queries and Provide Look-Up and Filter
Functionalities on a Web Page
1. Add another page, Page3.aspx, to the WebDBConnectivity project.
2. Add an AccessDataSource from the Toolbox Window onto the Web page.
3. Click the smart tag of the AccessDataSource, and choose the Configure
Data Source option to open the Configure Data Source wizard.
4. Select the University.mdb database file, and click the Next button.
39
Configuring the Select Statement
5. On the Configure the Select Statement page, select tblDepartment from the
Name drop-down list, and select the DeptID and Name columns as shown.
Configuring an
AccessDataSource
to Query the
tblDepartment Table
6. Drag and drop the DropDownList control from the Toolbox onto page.
40
Configuring the DropDownList
7. 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
41
Configuring the DropDownList (cont.)
8. Select the AccessDataSource1 control from the Select a data source dropdown list. Select the Name field for the field to display and DeptID field for
the value field. Click OK.
Specifying Data Source
and the Display and
Value Fields
9. Check the Enable AutoPostBack option of the DropDownLists’s Tasks list.
10. Drag and drop a Label control just above the DropDownList control, and set
its Text property to “Select a Department:”.
42
Setting the Start Page and Testing
11. Set Page3.aspx as the start page of the application.
12. Run and test the application.
Running Application
DropDownList and AccessDataSource Tags
43
Configuring the AccessDataSource
13. Drag and drop the AccessDataSource control onto the page. Use the
control’s Tasks list to open the Configure Data Source wizard.
14. On the Configure the Select Statement page, select the tblStudent table and
its columns as shown below.
Selecting Fields for
the Student Query
44
Adding a Where Clause and a GridView
15. 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.
Adding a WHERE Clause;
Specifying a Parameter
and its Value Source
16. Test the query and click Finish to close the Configure Data Source wizard.
17. Drag and drop a GridView control onto the page. Use the control’s Tasks list
to set its data source to the AccessDataSource2 control created in step 13.
Also, choose the Enable Paging option.
45
Testing the Application
18. Run and test the application.
Application Output:
Department Lookup and
Filtered Student Records
AccessDataSource
ASP Tag
46
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
47
Enabling Selection and Adding Controls

How-to: Use the DetailsView Control to Show Details about a Record
1. Open the Page3.aspx file of the WebDBConnectivity project.
2. 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.
3. Add another AccessDataSource control, AccessDataSource3, to the Web
page.

We will fetch the student’s details using this data source.
4. Use AccessDataSource control’s Tasks list to invoke the Configure Data
Source wizard.
48
Configuring the Select Statement
5. 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 Control’s Data Source
49
The Where Clause
6. 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.
Specifying the WHERE
Clause and Linking its
Value Source
7. On the same page of the wizard, click the Advanced button, and select the
Generate INSERT, UPDATE, and DELETE statements option.
50
The DetailsView Control
8. Drag and drop the DetailsView control from the Toolbox onto the Web page.
Adding a DetailsView Control
9. 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 control’s data source.
51
The DetailsView Control (cont.)
10. 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
52
Testing the Application
11. Run and test the application.
Running Application with
the DetailsView Control
DetailsView
ASP Tag
53
Displaying Student Pictures
12. Click the Edit Fields option from the Tasks list of the DetailsView control to
open the Fields dialog box.
13. 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.
14. 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
54
Testing the Application
15. Run and test the application.
Ability of a DetailsView
Control to Update Records
DetailsView Displaying
a Student’s Picture
55
DetailsView ASP Tag
Fields Tag of the DetailsView Control Showing ImageField ASP Tag
56
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
57
Adding a Repeater Control

How-to: Use a Repeater Control on a Web Page
1. Add another page, Page4.aspx, to the WebDBConnectivity project.
2. Drag and drop a Repeater control from the Data tab of the Toolbox onto the
page.
Adding a Repeater Control
58
The AccessDataSource
3. 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 Control’s Data Source
59
Configuring the Repeater Control
4. Click on the smart tag of the Repeater control to open its Tasks list. Select
AccessDataSource1 from the Choose Data Source drop-down list.
Binding the
Repeater Control
5. 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.
Defining a Template
of a Repeater Control
60
Setting Start Page and Testing
6. 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
61
Adding an Image ASP Tag and Testing
7. Modify the page’s 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.
Adding an ASP
Image Tag
8. Run and test the application.
Faculty Repeater Control
with Faculty Pictures
62
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
63
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
1. Add a new page, Page5.aspx, to the Web site we have developed so far.
2. 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”.
64
The Report Expert Wizard
3. 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
65
Selecting a Data Connection
4. 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
66
The Data Page
5. On the Data page, expand the Tables node and select the tblStudent table.
Click on the “>” button to move it to the Selected Tables pane. Click the Next
button.
Selecting the Data Source Table for the Report
67
The Fields Page
6. 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
68
The Grouping Page
7. 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
69
The Summaries Page
9. On the Summaries page, add the tblStudent.StudentID field from the
Available Fields pane to the Summarized Fields pane.
10. 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
70
The Group Sorting Page
10. On the Group Sorting page of the wizard, accept the default selection and
click the Next button.
Selecting the Group Ordering for the Report
71
The Chart Page
11. 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
72
The Record Selection Page
12. On the Record Selection page, move the tblStudent.DeptID field from the
Available Fields pane to the Filter Fields pane.
13. 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
73
Adding a CrystalReportViewer Control
14. On the Report Style page of the wizard, click the Finish button to close the
wizard and to add the report to the project.
15. 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
74
Binding a Database to the CrystalReportViewer
16. 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.
Choosing the
Report Source
17. Accept the default control name. In the Specify a Crystal Report drop-down
list, select StudentReport.rpt. Click OK.
Creating a
CrystalReportSource
75
Setting Start Page and Testing
18. Set Page5.aspx as the start page. Run and test the application.
Page Displaying the Student Crystal Report
76
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
77
Programmatically Accessing a Database

How-to: Access a Database Programmatically at Run-Time
1. Add another page, Page6.aspx, to the application.
2. Drag and drop the GridView control onto the Web page, and set its ID to
“grvCourses.” Format the grid using the AutoFormat option.
3. 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 dropdown list. Click on the smart tag of the DropDownList control to view its
Tasks list, and check the Enable AutoPostBack option.
78
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.
4. 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
79
Adding Code (cont.)
5. 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
80
Adding Code (cont.)
6. Enter the code shown below in the DropDownList control’s
SelectedIndexChanged event handler.
Update Course Details for a Selected Department in the GridView
81
Setting Start Page and Testing
7. 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
82
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
83
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.
84
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
85
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 control’s data source supports a sorting
operation, extending this functionality to a Web interface is easy.
86
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.
87