USING ACTIVE SERVER PAGES ASP AND VBSCRIPT TO PUBLISH, POPULATE AND QUERY DATABASES

1
USING ACTIVE SERVER PAGES (ASP) AND VBSCRIPT TO
PUBLISH, POPULATE AND QUERY DATABASES

• ASTINFO E-INFORMATION
• SERVICES DEVELOPMENT AND ADMINISTRATION WORKSHOP

OCTOBER 22 26, 2001, RICHMONDE HOTEL, PASIG
CITY, MANILA, PHILIPPINES
JOSEPH JENNIFER M. ESGUERRA SRS II, SCIENCE AND
TECHNOLOGY INFORMATION INSTITUTE INSTRUCTOR,
POLYTECHNIC UNIVERSITY OF THE PHILIPPINES
2
WORKSHOP OUTLINE
I . Active Server Pages and VBScript
Introduction Syntax Variables Procedures
Forms and User Inputs II. SQL, ODBC and
SQL Database Concepts SQL Basics ODBC
configuration III. ASP and Database
Integration Search and Retrieval Content
Population
3
Active Server Pages and VBScript
4
ASP INTRODUCTION
An ASP file can contain text, HTML tags, and
scripts. Scripts in an ASP file are executed on
the server

• Basic understanding of the following is needed
• WWW
• HTML
• Basics of building Web pages
• Scripting language JavaScript or VBScript

5
ASP INTRODUCTION

• What is ASP?
• ASP stands for Active Server Pages
• ASP is a program that runs inside IIS
• IIS stands for Internet Information Services
• IIS comes as a free component with Windows
  2000
• IIS is also a part of the Windows NT 4.0
  Option Pack
• The Option Pack can be downloaded from
  Microsoft
• PWS is a smaller - but fully functional -
  version of IIS
• PWS can be found on your Windows 95/98 CD

6
ASP INTRODUCTION
What is an ASP File? An ASP file is just
the same as an HTML file An ASP file can
contain text, HTML, XML, and scripts
Scripts in an ASP file are executed on the server
An ASP file has the file extension ".asp"
How Does it Work? When a browser
requests HTML file, the server returns the file
When a browser requests ASP file, IIS
passes the request to the ASP engine ASP
engine reads the ASP file, line by line,
executes the scripts in the file
Finally, the ASP file is returned to the browser
as plain HTML
7
ASP INTRODUCTION

• What can ASP do
• Dynamically edit, change or add any content
  of a Web page
• Respond to user queries or data submitted
  from HTML forms
• Access any data or databases and return the
  results to a browser
• Customize a Web page to make it more useful
  for individual users
• The advantages of using ASP instead of CGI
  and PERL, are those of simplicity and speed
  
• Provides security since ASP code can not be
  viewed from the browser
• ASP files are returned as plain HTML, they
  can be viewed in any browser
• Clever ASP programming can minimize the
  network traffic

8
ASP INTRODUCTION
How to Install and Run ASP I.
Windows NT Install Personal Web Server
(PWS) included in Win98 second ed. or
Download and Install "Windows NT 4.0 Option Pack"
from Microsoft. II. How to install PWS and run
ASP on Windows 98 1. Open the Add-ons folder
on your Windows98 CD, find the PWS folder and
run the setup.exe file. 2. An Inetpub
folder will be created on your harddrive. Open it
and find the wwwroot folder. 3. Create
a new folder, like "MyWeb", under wwwroot.
4. Use a text editor to write some ASP code, save
the file as "test1.asp" in the "MyWeb"
folder. 5. Make sure your Web server is
running. 6. Open your browser and type in
"http//localhost/MyWeb/test1.asp , to
view your first ASP page.
9
ASP INTRODUCTION
How to Install and Run ASP III. How to install
IIS and run ASP on Windows 2000 1. From
your Start Button, go to Settings, and Control
Panel 2. In the Control Panel window select
Add/Remove Programs 3. In the Add/Remove
window select Add/Remove Windows Components
4. In the Wizard window check Internet
Information Services, click OK 5. An
Inetpub folder will be created on your harddrive
6. Open the Inetpub folder, and find a
folder named wwwroot
7. Create a new folder, like "MyWeb", under
wwwroot. 8. Use a text editor to write some
ASP code, save the file as "test1.asp"
in the "MyWeb" folder. 9. Make sure your
Web server is running. 10.Open your browser
and type in "http//localhost/MyWeb/test1.asp",
to view your first ASP page.
10
ASP SYNTAX
Basic Syntax Rule An ASP file normally
contains HTML tags, just as a standard HTML
file. In addition, an ASP file can contain
server scripts, surrounded by the
delimiters lt and gt. Server scripts are executed
on the server, and can contain any
expressions, statements, procedures, or
operators that are valid for the scripting
languages. The Response Object
Response.Write method is used to send content
to the browser. For example, the following
statement sends the text "Hello World" to the
browser Response.Write("Hello World").
11
ASP SYNTAX
Code1 - hello.asp lthtmlgt ltbodygt lt response.write"
Hello Beautiful World!" gt lt/bodygt lt/htmlgt
c\webshare\wwwroot\asp\
12
ASP SYNTAX
Code2 - hellohtm.asp lthtmlgt ltbodygt lt response.wri
te"ltH1gtHello Beautiful World!ltbrgt This Sentence
uses HTML! lt/h1gt!" gt lt/bodygt lt/htmlgt
13
ASP VARIABLES
Variables A variable declared outside a
procedure, can be changed by any script in
the ASP file. A variable declared inside a
procedure, is created and destroyed
every time the procedure is executed. Variable
Creation Variables are used to store
information ex. Code3 - variable.asp lthtmlgt
ltbodygt lt DIM name name Jayjay
Esguerra response.write(My name is
name) gt lt/bodygt lt/htmlgt
14
ASP VARIABLES
Array Arrays are used to store
a series of related data items. This example
demonstrates how you can make an
array that stores names. Code4 -
array.asp lthtmlgt ltbodygt lt Dim famname(5) famname(
0) "Jayjay Esguerra" famname(1) "Sherry
Sarmiento" famname(2) "Melvin
Ambrosio" famname(3) "Jobet delos Santos
" famname(4) "Donna Monatalban" famname(5)
"Jungie Roasa" For i 0 to 5
response.write(famname(i) "ltbrgt") Next gt lt/body
gt lt/htmlgt
15
ASP VARIABLES
Looping This example demonstrates how you
can loop through the 6 headers in
HTML. Code5 - loop.asp lthtmlgt ltbodygt lt Dim i
for i 1 to 6 response.write("lth" i
"gtThis is header " i "lt/h" i
"gt") next gt lt/bodygt lt/htmlgt
16
ASP VARIABLES
Time Based Greeting This example will display a
different message to the user depending on the
time on the server. Code6 - time.asp lthtmlgtltbodygt
lt Dim h h hour(now()) response.write("ltpgt"
now()) response.write(" (Philippines Time)
lt/pgt") If h lt 12 then response.write("Good
Morning!") else response.write("Good
day!") end if gt lt/bodygtlt/htmlgt
17
ASP VARIABLES
Time Based Greeting This example will display
greetings and the number of times the page is
accessed with corresponding server time. Code7
greet_if.asp lt_at_ LANGUAGE"VBSCRIPT" gt lt Option
Explicit 'Define our variables. Dim
greeting gt lt if Time gt 120000 AM And Time
lt 115900 AM Then greeting"Good
Morning!" ElseIf Time gt 120000 PM And Time
lt 055900 PM Then greeting"Good
Afternoon!" Else greeting"Good Evening!" End
If gt ltFont color"RED"gtlth1gt ltgreetinggtlt/h1gtlt/f
ontgt lthrgt This page was last refreshed at
ltNowgt lt/scriptgt
18
ASP PROCEDURES
Procedures In ASP you can call a JavaScript
procedure from a VBScript and vice versa. Code8
-procedure.asp lthtmlgt ltheadgt lt sub
vbproc(num1,num2) response.write(num1num
2) end sub gt lt/headgt ltbodygt ltpgtThe
result of the calculation is lt call vbproc(3,4)
gt lt/pgt ltpgt Or, you can call a procedure like
this lt/pgt ltpgt The result of the calculation is
ltvbproc 3,4gt lt/pgt lt/bodygt lt/htmlgt
19
ASP PROCEDURES

• Calling a Procedure
• When calling a VBScript or a JavaScript
  procedure from an ASP page written in VBScript,
  you can use the "call" keyword followed by the
  procedure name.
• If a procedure requires parameters, the
  parameter list must be enclosed in parentheses
  when using the "call" keyword.
• If you omit the "call" keyword, the parameter
  list must not be enclosed in parentheses.
• If the procedure has no parameters, the
  parentheses are optional.
• When calling a JavaScript or a VBScript
  procedure from an ASP page written in JavaScript,
  always use parentheses after the procedure name.

20
ASP PROCEDURES
Calling a JavaScript code in an ASP
code Code9-procedure2.asp lthtmlgt ltheadgt lt sub
vbproc(num1,num2) Response.Write(num1num2
) end sub gt ltscript
language"javascript" runat"server"gt function
jsproc(num1,num2) Response.Write(num1num2) lt/
scriptgt lt/headgt ltbodygt The result of the
calculation is ltcall vbproc(3,4)gtltbrgtltbrgt The
result of the calculation is ltcall
jsproc(3,4)gt lt/bodygt lt/htmlgt
21
ASP FORMS AND USER INPUTS
FORMS AND USER INPUTS You can get form
information using HTML calling ASP
codes. Code10-submit.htm (This htm code accept
2 values and pass it to an ASP code) ltHTMLgt ltBODYgt
ltFORM Action"submit.asp" Method"post"gt
ltPgtPlease Enter your name ltINPUT Name"Qsearch"
Size48gt ltPgtThe country you came from
ltSELECT Name "Qfield"gt ltOPTIONgtAustralia
ltOPTIONgtIndia ltOPTIONgtPakistan ltOPTIONgtPhil
ippines ltOPTIONgtMalaysia ltOPTIONgtSingapore
ltOPTIONgtChina ltOPTIONgtThailand lt/SELECTgt
ltPgtltINPUT Type"SUBMIT" Value"Submit
Query"gt lt/FORMgt lt/BODYgtlt/HTMLgt
22
ASP FORMS AND USER INPUTS
Code11-submit.asp lt_at_ LANGUAGE"VBSCRIPT"
gt lthrgt Welcome to the E-Information Workshop
lth1gt lt Request.Form("Qsearch") gt lt/h1gt
ltbrgt You are representing lth1gt lt
Request.Form("Qfield")gt lt/h1gt lthrgt
23
RELATIONAL DATABASE CONCEPT
24
DATABASE, SQL AND ODBC

• THE RELATIONAL DATABASE CONCEPT
• Dr. E. F. Codd proposed the relational model for
  database systems in 1970.
• It is the basis for the relational database
  management system (RDBMS).
• The relational model contains the following
  components
• Collection of objects or relations
• Set of operations to act on the relations
• Data integrity for accuracy and consistency

25
DATABASE, SQL AND ODBC
THE RELATIONAL DATABASE CONCEPT A relational
database is a collection of relations or
two-dimensional tables.
DATABASE SERVER
SALES_ REP_ID
ID
LAST_NAME
NAME
ID
PHONE
FIRST_NAME
Havel
Unisports
Marta
55-2066101
10
12
201
Simms Athletics
Magee
Colin
81-20101
14
11
202
Delhi Sport
14
203
91-10351
12
Giljum
Henry
Womansport
Nguyen
Mai
11
14
1-206-104-103
204
26
DATABASE, SQL AND ODBC

• RELATIONAL DATABASE TERMINOLOGY
• Each table is composed of rows and columns.
• S_CUSTOMER Table (Relation)
• You can manipulate data in the rows by executing
  Structured Query Language (SQL) commands.

SALES_ REP_ID
NAME
ID
PHONE
Row(Tuple)
Unisports
55-2066101
12
201
Simms Athletics
81-20101
14
202
Delhi Sport
14
203
91-10351
Womansport
11
1-206-104-103
204
Column (Attribute)
27
DATABASE, SQL AND ODBC

• RELATIONAL DATABASE TERMINOLOGY
• Each row of data in a table is uniquely
  identified by a
• primary key (PK).
• You can logically relate information from
  multiple tables using Foreign keys (FK).

Table Name S_EMP
Table Name S_CUSTOMER
SALES_ REP_ID
ID
LAST_NAME
NAME
ID
PHONE
FIRST_NAME
Havel
Unisports
Marta
55-2066101
10
12
201
Simms Athletics
Magee
Colin
81-20101
14
11
202
Delhi Sport
14
203
91-10351
12
Giljum
Henry
Womansport
Nguyen
Mai
11
14
1-206-104-103
204
Primary key
Primary key
Foreign key
28
DATABASE, SQL AND ODBC

• RELATIONAL DATABASE PROPERTIES
• Access and modify objects in the database
• by executing Structured Query Language (SQL)
  
• commands
• Contains a collection of tables with no
  physical
• pointers
• Uses a set of operations
• Modifiable online
• Full data independence

29
DATABASE, SQL AND ODBC
DATABASE OBJECTS
Object
Description
Basic unit of storage composed of rows and columns
Table
Logically represents subsets of data from one or
more tables
View
Generates primary key values
Sequence
Index
Improves the performance of some queries
Alternate name for an object
Synonym
Procedure, function, or package of SQL and
PL/SQL Statements.
Program Unit
30
DATABASE, SQL AND ODBC

• DATA INTEGRITY CONSTRAINTS
• Entity
• No part of a primary key can be NULL and the
  value must
• be unique. A NULL is an absence of a value.
• Referential
• Foreign key values must match a primary key or
  be NULL.
• Column
• Values in the column must match the defined
  datatype.
• User-defined
• Values must comply with the business rules.

31
DATABASE, SQL AND ODBC
SYSTEM DEVELOPMENT CYCLE
Strategy And Analysis
Design
Build And Document
Transition
Production
32
DATABASE, SQL AND ODBC
THE BIG PICTURE
Internet Tools
Client Server
Server
Multi-Media
33
STRUCTURED QUERY LANGUAGE
34
DATABASE, SQL AND ODBC

• SQL COMMANDS
• Data Retrieval
• SELECT
• Data manipulation language (DML)
• INSERT, UPDATE, DELETE
• Data definition language (DDL)
• CREATE, ALTER, DROP, RENAME, TRUNCATE
• Transaction control
• COMMIT, ROLLBACK, SAVEPOINT
• Data control language (DCL)
• GRANT, REVOKE

35
DATABASE, SQL AND ODBC
THE BASIC QUERY BLOCK
SELECT DISTINCT,column alias,. FROM ta
ble

• SELECT identifies what columns
• FROM identifies which table

36
DATABASE, SQL AND ODBC

• WRITING SQL COMMANDS
• Commands can have one or many lines
• Tabs and indents can be used for readability.
• Abbreviations and splitting of words are not
  allowed.
• Commands are not case sensitive.

37
DATABASE, SQL AND ODBC

• SELECTING ALL COLUMNS, ALL ROWS
• Simplest SELECT statement contains the following
  two clauses
• SELECT clause
• Asterisk () indicates all columns
• FROM clause

SQLgt SELECT 2 FROM s_dept
38
DATABASE, SQL AND ODBC
SELECTING ALL COLUMNS, ALL ROWS
SQLgt SELECT 2 FROM s_dept
ID NAME
REGION_ID ----------- ---------------------------
------ ----------------------------------
10 Finance 1 31 Sales 1
32 Sales 2 33 Sales 3
34 Sales 4 35 Sales 5
41 Operations 1 42 Operations 2
43 Operations 3 44 Operations 4
45 Operations 5 50 Administration 1 12
rows selected
39
DATABASE, SQL AND ODBC
SELECTING SPECIFIC COLUMNS
SQLgt SELECT dept_id, last_name, manager_id
2 FROM s_emp

• List the columns in the SELECT clause.
• Separate columns by using a comma.
• Specify columns in the order you want them
• to appear.

40
DATABASE, SQL AND ODBC
ARITHMETIC EXPRESSIONS

• Create expressions on NUMBER and DATE datatypes
  by using operators.
• Add
• Subtract -
• Multiply
• Divide /

41
DATABASE, SQL AND ODBC
ARITHMETIC EXPRESSIONS Display the annual salary
for all employees.
SQLgt SELECT last_name, salary 12,
commission_pct 2 FROM s_emp
LAST_NAME SALARY12
COMMISSION_PCT ------------------------
--------------------------------
------------------------------- Havel 15684
Magee 16800 10 Giljum 17880 12.5 Sedegh
i 18180 10 Nguyen 18300 15 Dumas 17400
17.5 Maduro 16800
42
DATABASE, SQL AND ODBC

• OPERATOR PRECEDENCE
• Multiplication and division take priority over
  addition
• and subtraction.
• Operators of the same priority are evaluated
  from
• left to right.
• Parentheses can be used to force prioritized
  evaluation
• and to clarify statements.

43
DATABASE, SQL AND ODBC
OPERATOR PRECEDENCE Parentheses change the
order in which a statement is evaluated.
SQLgt SELECT last_name, salary, 12 salary
100 2 FROM s_emp
Velasquez 2500
30100
SQLgt SELECT last_name, salary, 12 (salary
100) 2 FROM s_emp
Velasquez 2500
31200
44
DATABASE, SQL AND ODBC

• CONCATENATION OPERATOR
• The concatenation operator
• Is represented by two vertical bars ().
• Links columns or character strings to other
• columns.
• Creates a resultant column that is a character
• expression.

45
DATABASE, SQL AND ODBC
CONCATENATION OPERATOR EXAMPLE Display the full
names of the employees with the heading Employees.
SQLgt SELECT first_namelast_name 2
FROM s_emp
Employees ----------------------------------------
---- CarmenVelasquez LaDorisNgao MidoriNagayama Ma
rkQuick-To-See AudryRopeburn MollyUrguhart
46
DATABASE, SQL AND ODBC

• DUPLICATE ROWS
• The default display of queries is all rows
  including
• duplicate rows.

SQLgt SELECT name 2 FROM s_dept

• Eliminate duplicate rows by using DISTINCT in the
  
• SELECT clause.

SQLgt SELECT name 2 FROM s_dept
47
DATABASE, SQL AND ODBC

• DISTINCT WITH MULTIPLE COLUMNS
• DISTINCT is applied to all columns in the SELECT
  list.
• sqlgt SELECT DISTINCT dept_id, title
• FROM s_emp
• When DISTINCT is applied to multiple columns,
  the results
• represents the distinct combination of of
  the columns.

48
DATABASE, SQL AND ODBC

• THE ORDER BY CLAUSE
• Sort rows with the ORDER BY clause.
• ASC ascending order, default.
• DESC descending order.
• ORDER BY clause is last in SELECT command.

SQLgt SELECT last_name dept_id, start_date
2 FROM s_emp 3 ORDER BY last_name
49
DATABASE, SQL AND ODBC

• THE ORDER BY CLAUSE
• The default sort order is ascending.
• The sort order can be reversed by using DESC.
• You can sort by expressions or aliases.

SQLgt SELECT last_name EMPLOYEE, start_date
2 FROM s_emp 3 ORDER BY EMPLOYEE DESC

• Null values are displayed.
• Last for ascending sequences.
• First for descending sequences.

50
DATABASE, SQL AND ODBC

• SORTING BY MULTIPLE COLUMNS
• You can order by position to save time.

SQLgt SELECT last_name, salary 12
2 FROM s_emp 3 ORDER BY 2

• You can sort by multiple columns.

SQLgt SELECT last_name, dept_id, salary
2 FROM s_emp 3 ORDER BY dept_id, salary
DESC

• The order of ORDER BY list is order of sort.
• You can sort by column that is not in the SELECT
  list.

51
DATABASE, SQL AND ODBC

• LIMITING ROWS SELECTED
• Restrict the rows returned by using the WHERE
  clause.
• The WHERE clause follows the FROM clause.
• Conditions consist of the following.
• Column name, expression, constant
• Comparison operator
• Literal

SQLgt SELECT last_name, dept_id, salary 2
FROM s_emp 3 WHERE dept_id 42
52
DATABASE, SQL AND ODBC

• CHARACTER STRINGS AND DATES
• Character strings and dates are enclosed within
  single
• quotation marks.
• Number values are not enclosed within quotation
  marks.
• Character values are case- sensitive.
• The default date format is DD-MON-YY.

SQLgt SELECT first_name, last_name, title 2
FROM s_emp 3 WHERE last_name Magee
53
DATABASE, SQL AND ODBC

• COMPARISON AND LOGICAL OPERATORS
• Logical comparison operators
• gt gt lt lt
• SQL comparison operators
• BETWEEN AND
• IN(list)
• LIKE
• IS NULL
• Logical operators
• AND
• OR
• NOT

54
DATABASE, SQL AND ODBC

• NEGATING EXPRESSIONS
• Sometimes it is easier to exclude rows you know
  you do not want.
• Logical Operators
• ! ltgt
• SQL Operators
• NOT BETWEEN
• NOT IN
• NOT LIKE
• IS NOT NULL

55
DATABASE, SQL AND ODBC

• BETWEEN and IN SQL Operators
• Use the BETWEEN operator to test for values
• between, and inclusive of, a range of values.

SQLgt SELECT first_name, last_name, start_date
2 FROM s_emp 3 WHERE start_date BETWEEN
09-may-91 4 AND 17-jun-91

• Use IN to test for values in a list

SQLgt SELECT first_name, region_id 2
FROM s_dept 3 WHERE region_id IN (1,3)
56
DATABASE, SQL AND ODBC

• LIKE SQL OPERATOR
• You can use the LIKE operator to perform wildcard
  
• searches of valid search string values.
• Search conditions can contain either literal
  characters
• or numbers.
• denotes none or many characters.
• _ denotes one character.

SQLgt SELECT last_name 2 FROM s_emp
3 WHERE last_name LIKE M
57
DATABASE, SQL AND ODBC

• LIKE SQL OPERATOR
• The LIKE operator can be used as a shortcut for
  some
• BETWEEN comparisons.

SQLgt SELECT last_name, start_date 2
FROM s_emp 3 WHERE start_date LIKE 91

• You can combine pattern matching characters.

SQLgt SELECT last_name 2 FROM s_emp
3 WHERE last_name LIKE _a

• You can use the ESCAPE identifier to search
• for or _.

58
DATABASE, SQL AND ODBC

• IS NULL SQL OPERATOR
• Test for null values with the IS NULL operator.
• Do not use the operator.

SQLgt SELECT id, name, credit rating 2
FROM s_customer 3 WHERE sales_rep_id is
NULL
59
DATABASE, SQL AND ODBC

• MULTIPLE CONDITIONS
• Use complex criteria.
• Combine conditions with AND or OR operators.
• AND requires both conditions to be TRUE.

SQLgt SELECT last_name, salary, dept_id, title
2 FROM s_emp 3 WHERE dept_id 41
4 AND title Stock Clerk

• OR requires either condition to be TRUE.

SQLgt SELECT last_name, salary, dept_id, title
2 FROM s_emp 3 WHERE dept_id 41
4 OR title Stock Clerk
60
DATABASE, SQL AND ODBC
RULES OF PRECEDENCE Override rules of precedence
by using parentheses.
Order Evaluated Operator 1 All Comparison
Operator 2 AND 3 OR
61
DATABASE, SQL AND ODBC

• RULES OF PRECEDENCE EXAMPLES
• Display information for those employees in
  department 44 who earn
• 1000 or . more, and any employees in
  department 42

SQLgt SELECT last_name, salary, dept_id
2 FROM s_emp 3 WHERE salary gt
1000 4 AND dept_id 44 5 OR dept_id
42

• Display information for those employees in
  department 44 or 42
• who earn 1000 or More.

SQLgt SELECT last_name, salary, dept_id
2 FROM s_emp 3 WHERE salary gt
1000 4 AND (dept_id 44 5 OR dept_id
42)
62
CONFIGURING ODBC DRIVERS
63
DATABASE, SQL AND ODBC

• SETTING-UP ODBC
• ODBC
• Open Data Base Connectivity - used as standard
  for connecting to
• database sources.
• ACCESS
• MS SQL
• ORACLE
• INFORMIX
• JAVA and others

64
DATABASE, SQL AND ODBC
SETTING-UP ODBC Click start then control panel.
Double click the ODBC SOURCES
65
DATABASE, SQL AND ODBC
SETTING-UP ODBC Select System DSN tab then
click the ADD button.
66
DATABASE, SQL AND ODBC
SETTING-UP ODBC Select appropriate Database
driver in the list then click the FINISH button.
67
DATABASE, SQL AND ODBC

• SETTING-UP ODBC
• Type in the Data Source Name that you want to
  name your connection to the database. Filling in
  the optional Description will help in the future
  when you have more data sources registered.
• Click the SELECT button under the database
  portion.

68
DATABASE, SQL AND ODBC

• SETTING-UP ODBC
• Select the database name that you want for
  connection. Browse if necessary to locate
  databases. (Data Bases can also be in another
  computer or another server so be prepared to
  browse the network too) When the database is
  located click OK.

69
DATABASE, SQL AND ODBC
SETTING-UP ODBC After selecting the database
source name or connection the DATA SOURCE is now
added in the list of the System DSN tab. The
data base can now be viewed/edited/populated
using any database tool.
70
ASP and Database Integration Database Query and
Population
71
ASP AND DATABASE INTEGRATION

• MS ACCESS DATABASE EXAMPLE
• Heres an example database that taken from a
  PERSONNEL
• MONITORING SYSTEM (PMS) of STII-DOST.

72
ASP AND DATABASE INTEGRATION

• MS ACCESS DATABASE EXAMPLE
• Snapshot of the attributes of Personal Info and
  Post_school tables
• In the PMS database.

73
ASP AND DATABASE INTEGRATION
CREATING DATABASE CONNECTION Code11-data_out.asp
THIS PROGRAM WILL LIST SELECTED INFORMATION FROM
THE POST_SCHOOL TABLE OF THE PMS DATABASE NAMED
AS PMS_DATA IN THE ODBC DATA SOURCE. IT WILL
DISPLAY IT USING A WEB BROWSER. lt Option
Explicit ' Variable definition, by the way this
is a comment Dim cnnDB, strQuery, rsInfo,
greeting 'Creation of an instance of the
Connection object. Set cnnDB Server.CreateObject
("ADODB.Connection") 'Opening the data source
named in the ODBC. cnnDB.Open "pms_data" 'Building
the SQL query string and getting information
from post_school table. strQuery SELECT FROM
post_school" 'Execute the query and return a
recordset equating it in the rsInfo variable. Set
rsInfo cnnDB.Execute(strQuery) gt
74
ASP AND DATABASE INTEGRATION
Code11-data_out.asp continued lt This
HTML snippet is for the printing of headings to
be used by the data taken from the PMS
databases Post_school table. Again this is a
comment gt ltHTMLgtltBODYgtltCENTERgtltH2gt From
POST_School TABLE OF THE PMS DATABASElt/H2gtltBRgt ltTA
BLEgt ltTRgtltTH align"left"gtEMP_NOlt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH
align"left"gtUNIVERSITYlt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH
align"left"gtDEGREE/UNITS lt/THgtlttd
width"15"gtltbrgtlt/tdgt lt/TRgt
75
ASP AND DATABASE INTEGRATION
Code11-data_out.asp continued lt 'Iterate
through the recordset, pull out the required
data, and insert it 'into an HTML table. Do While
Not rsInfo.EOF gt ltTRgt ltTDgtlt
rsInfo("Emp_No") gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt
ltTDgtlt rsInfo("Name of School")
gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Degree/Units Earned") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt lt/TRgt lt 'Move to
the next record in the record set rsInfo.MoveNext
Loop 'Close the recordset. rsInfo.Close 'And
close the database connection. cnnDB.Close
gt lt/TABLEgtlthrgtlt/CENTERgt lt/BODYgt lt/HTMLgt
76
ASP AND DATABASE INTEGRATION
DATA_OUT.ASP output
77
ASP AND DATABASE INTEGRATION
QUERYING A DATABASE Code12-QUERY_out.htm THIS
HTML WILL ACCEPT QUERIED DATA FROM PERSONAL INFO
TABLES ATTRIBUTES THEN CALL THE QUERY_OUT.ASP
THAT WILL PROCESS THE REQUEST. ltHTMLgtltBODYgt ltFOR
M Action"query_out.asp" Method"post"gt ltPgtYour
Query Please, a letter or a word is acceptable
ltINPUT Name"Qsearch" Size48gt ltPgtWhich Field
? ltSELECT Name "Qfield"gt ltOPTIONgtSurname
ltOPTIONgtFirst_name ltOPTIONgtEMP_NO ltOPTIONgtCivi
l_Status lt/SELECTgt ltPgtltINPUT Type"SUBMIT"
Value"Submit Query"gt lt/FORMgt lt/BODYgtlt/HTMLgt
78
ASP AND DATABASE INTEGRATION
Code12-QUERY_out.htm
79
ASP AND DATABASE INTEGRATION
QUERYING A DATABASE Code13-QUERY_out.asp THIS
PROGRAM WILL DISPLAY QUERIED INFORMATION FROM THE
PERSONAL INFO TABLE OF THE PMS DATABASE DATA
ENTERED FROM THE HTM FILE QUERY_OUT.HTM. lt_at_
LANGUAGE"VBSCRIPT" gt lt Option Explicit 'Define
our variables. Dim cnnDB, strQuery, rsInfo,
QAsearch, QAfield 'Create an instance of the
Connection object. Set cnnDB Server.CreateObject
("ADODB.Connection") 'And open it. cnnDB.Open
"pms_data" 'Display first the posted data gtlthrgt
ltcentergtltbgtYou Searched for lt
Request.Form("Qsearch") gt In the field lt
Request.Form("Qfield")gt lt/bgtlt/centergtlthrgt lt 'Equ
ate the inputted value to variables QAsearch
(Request.Form("Qsearch")) QAfield
(Request.Form("Qfield"))
80
ASP AND DATABASE INTEGRATION
Code13-QUERY_out.asp continued 'Build our SQL
query string strQuery "select from
Personal_Info where
("QAfield" LIKE '"QAsearch"')" 'Execute the
query and return a recordset. Set rsInfo
cnnDB.Execute(strQuery) gt ltCENTERgtltH2gt From
Personal_Info TABLE OF THE PMS DATABASElt/H2gtltBRgt lt
TABLEgt ltTRgt ltTH align"left"gtEmp_nolt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH align"left"gtLast
namelt/THgtlttd width"15"gtltbrgtlt/tdgt ltTH
align"left"gtFirst namelt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH align"left"gtMiddle
namelt/THgtlttd width"15"gtltbrgtlt/tdgt ltTH
align"left"gtCivil Statuslt/THgtlttd
width"15"gtltbrgtlt/tdgt ltTH align"left"gtDate of
Birthlt/THgtlttd width"15"gtltbrgtlt/tdgt ltTH
align"left"gtTax IDlt/THgtlttd width"15"gtltbrgtlt/tdgt
lt/TRgt
81
ASP AND DATABASE INTEGRATION
Code13-QUERY_out.asp continued lt 'Iterate
through the recordset, pull out the required
data, and insert it into an HTML table. Do While
Not rsInfo.EOF gt ltTRgtltTDgtlt rsInfo("Emp_No")
gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Surname") gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt
ltTDgtlt rsInfo("First_Name") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Middle_Name") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Civil_Status") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("Date of Birth") gtlt/TDgtlttd
width"15"gtltbrgtlt/tdgt ltTDgtlt
rsInfo("TIN") gtlt/TDgtlttd width"15"gtltbrgtlt/tdgt lt/T
Rgt lt 'Move to the next record in the
recordset rsInfo.MoveNext Loop 'Close the
recordset. rsInfo.Close 'And close the database
connection. cnnDB.Close gt lt/TABLEgtlthrgtlt/fontgtlt/C
ENTERgtlt/scriptgt
82
ASP AND DATABASE INTEGRATION
Code13-QUERY_out.asp
83
ASP AND DATABASE INTEGRATION
POPULATING A DATABASE Code14-DATA_ENTRY.HTM
THIS HTML WILL ACCEPT INPUTTED DATA ONLINE THEN
CALL THE DATA_ENTRY.ASP TO INSERT THE DATA
ENTERED IN THE POST_SCHOOL TABLE OF PMS DATABASE.
lthtmlgtltheadgtlttitlegtPersonal Info Data
Entrylt/titlegtlt/headgt ltFORM Action
"data_entry.asp" Method"POST" gt ltCENTERgt
ltTABLEgtltTRgt ltTDgtltbgtEMP NOlt/Bgtlt/TDgt
ltTDgtltINPUT Type"text" Name"T_Emp_No" Value" "
Size"20" gtlt/TDgt ltTDgtltbgtName of
Schoollt/Bgtlt/TDgt ltTDgtltINPUT Type"text"
Name"t_Name_of_School" Value" " Size"50"
gtlt/TDgt lt/TRgtltTRgt
ltTDgtltBgtDegree/Units Earnedlt/bgtlt/TDgt
ltTDgtltINPUT Type"text" Name"T_Degree_Units_Earned
" Value" " Size"20"gtlt/TDgt
ltTDgtltbgtInclusive Dates of Attendancelt/Bgtlt/TDgt
ltTDgtltINPUT Type"text" Name"t_Inclusive_Dates
_of_Attendance" Value" " Size"50"gtlt/TDgt
lt/TRgtltTRgt ltTDgtltbgtHonors
Receivedltbgtlt/TDgt ltTDgtltINPUT Type"text"
Name"T_Honors_Received" Value" "
Size"20"gtlt/TDgt lt/TRgtlt/TABLEgt ltINPUT
Type"Submit" Value"Submit"gt ltINPUT
Type"Reset" Value"Reset"gt lt/CENTERgtlt/FORMgtlt/body
gtlt/htmlgt
84
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.HTM
85
ASP AND DATABASE INTEGRATION
POPULATING A DATABASE Code15-DATA_ENTRY.ASP
THIS PROGRAM WILL ACCEPT DATA PASSED BY
DATA_ENTRY.HTM THEN INSERT VALUES TO
CORRESPONDING ATTRIBUTE IN THE POST_SCHOOL TABLE
OF THE PMS DATABASE. THIS PROGRAM ALSO CHECKS
BLANK ENTRIES AND DISPLAYS CORRESPONDING
MESSAGES. lt_at_ LANGUAGE"VBSCRIPT" gt lt Option
Explicit 'DeCLARATION OF our variables Dim
Q_Emp_No, Q_Name_of_School, Q_Degree_Units_Earned
Dim Q_Inclusive_Dates_of_Attendance,
Q_Honors_Received Dim cnnDB, strQuery,
RsInfo 'Create an instance of the Connection
object. Set cnnDB Server.CreateObject("ADODB.Con
nection") 'And open it. cnnDB.Open
"pms_data" 'Retrieving values keyed in by the
user in the user interface into
variables Q_Emp_No (REQUEST.FORM("t_Emp_No")) Q
_Name_of_School (REQUEST.FORM("t_Name_of_School")
) Q_Degree_Units_Earned (REQUEST.FORM("T_Degree_
Units_Earned")) Q_Inclusive_Dates_of_Attendance
(REQUEST.FORM("t_Inclusive_Dates_of_Attendance"))
Q_Honors_Received (REQUEST.FORM("T_Honors_Receiv
ed"))
86
ASP AND DATABASE INTEGRATION
Code15-DATA_ENTRY.ASP continued THIS SNIPPET
CHECKS IF THERE ARE ENTERED VALUES IN EACH
FIELD. IT DISPLAYS THE INCOMPLETE MESSAGE IF
ONE OR MORE OF THE IMPORTANT FIELDS ARE LEFT
BLANK. ltif Q_Emp_No " " Or Q_Name_of_School
" " or Q_Degree_Units_Earned " " or
Q_Inclusive_Dates_of_Attendance " " Then
gt lthtmlgtltheadgtlttitlegtErrors in the
Formlt/titlegt ltbodygt ltbgtltfont face"Arial,
Helvetica, sans-serif" size"5" color"red"gtltbrgt
You submitted a blank or incomplete
form! lt/fontgtlt/bgt
lt/bodygt lt/htmlgt lt
87
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.ASP WITH A BLANK ENTRY
88
ASP AND DATABASE INTEGRATION
Code15-DATA_ENTRY.ASP continued IF ALL THE
IMPORTANT FIELDS HAVE VALUES, THIS SNIPPET WILL
INSERT ALL THE INFORMATION IN THE POST_SCHOOL
TABLE THEN WILL DISPLAY THE SUCCESS MESSAGE. lt
Else strQuery "insert into POST_SCHOOL
(EMP_NO,Name_of_School,Degree_Units_E
arned, Inclusive_Dates_of_Attendance
,Honors_Received) values
('"Q_Emp_No"','"Q_Name_of_School"','"Q_Degree
_Units_Earned"', '"QInclusive_Dates
_of_Attendance"','" Q_Honors_Received"')
'Execute the query and return a recordset.
cnnDB.Execute(strQuery) gt
lthtmlgt ltheadgtlttitlegtData Entry Form of POST
GRADlt/titlegtlt/headgtltbodygtltbrgt ltbgtltfont
face"Arial, Helvetica, sans-serif" size"5"
color"green"gt The information you entered is
now searcheable in our database.lt/fontgtlt/bgtlt/bodygt
lt/htmlgt lt 'Closing the
connection cnnDB.Close set cnnDBnothing End
If gt
89
ASP AND DATABASE INTEGRATION
Code14-DATA_ENTRY.ASP COMPLETE FORM
90
ASP AND DATABASE INTEGRATION

• NOW WITH THE ASP, VBSCIPT AND SQL BASICS
• IT IS RECOMMENDED TO TRY THE FOLLOWING
• Alter data_out.Asp to display the data queried
  indexed by
• employee number.
• Practice searching information using two table
  information
• based on the data entered ( eg. Recoding
  query_out.htm and
• query_out.asp to display some details of
  employees and
• his/her post education specialties.
• An edit/update/delete/ form of data_entry.htm and
  data_entry.asp
• An online version of some existing
  networked/standalone
• administrative systems of your office.
• A Guest Book perhaps.
• Just remember practice makes better result if not
  perfect !!!

91
ASP AND DATABASE INTEGRATION

• REFERENCES
• PRACTICAL ASP ISBN 81-7656-310-2 www.bponline.co
  m
• ACTIVE SERVER 101 www.asp101.com
• ASP TUTORIAL www.w3schools.com
• ORACLE SQL SERVER MANUAL www.oracle.com
• ACTIVE SERVER PAGES 3 A. Russell Jones

92
Thank you !!!
OCTOBER 22 26, 2001, RICHMONDE HOTEL, PASIG
CITY, MANILA, PHILIPPINES
JOSEPH JENNIFER M. ESGUERRA SRS II, SCIENCE AND
TECHNOLOGY INFORMATION INSTITUTE INSTRUCTOR,
POLYTECHNIC UNIVERSITY OF THE PHILIPPINES