Building Web Database Applications Using Microsoft Active Server Pages ASP

1
Building Web Database ApplicationsUsing
Microsoft Active Server Pages (ASP)

• AIR Pre-Forum Workshop
• June 2, 2001
• Timothy J. Thomas
• Indiana University Purdue University Indianapolis

2
Purpose and Goals

• Purpose
• Extend sites functionality and usefulness
• Database applications
• Programming (simple!!!)
• Exposure to an available tool set
• MS Active Server Pages (ASP)
• MS Front Page
• Goals
• First-hand experience
• Add new functionality to our sites

3
Agenda

• Introductions, Purpose and Goals 
• ASP and FrontPage 2000
• Passing information between web pages
• Connecting databases to web pages
• Making connections Writing to a database
• Making connections Pulling from a database
• Wrap-up and Questions

4
HTML Quick Review Attachment A

• Structural
• ltHTMLgt, ltHEADgt, ltBODYgt
• Elements
• Links ltA HREFgt
• Images ltIMG SRCgt
• Tables ltTABLEgt, ltTRgt, ltTDgt
• Forms ltFORMgt
• Scripts (Java Applets, ASP)
• Formatting
• ltCENTERgt, ltBOLDgt, ltPgt, ltBRgt, ltHRgt

5
Whats the Connection?

• Web Server
• Internet Information Services (IIS)
• Netscape
• OReilly WebSite
• Unix

• Databases
• MS Access
• SQL Server
• Oracle
• Sybase

6
Bridging the Gap Middleware

• CGI (Perl, etc.)
• Active Server Pages (www.microsoft.com/frontpage)
• Cold Fusion (www.allaire.com)
• iHTML (www.ihtml.com)

7
ASP - Some History

• Python family
• Ambiguous associations
• Realm of the dead - fear inducing - portending
  ill
• Rejuvenation - sly - spread of pure humanity
• Killed Cleopatra and several attendants
• Symbolizes vital energy to be awakened

When a snake appears in a dream it represents
powers from the depths of the psyches of others,
powers as old as the primordial reptile
itself.- Hans Biedermann
8
Active Server Pages (ASP)

• Server-side execution environment
• Translation Runs on the server, not the client
• Create dynamic content
• Translation Pages change upon action of user
• Combine HTML, scripting and components
• Translation Some HTML, Some programming
• Returns HTML to user

9
Active Server Pages (ASP)

• Free
• Included in IIS 4.0 -
• Additional setup in IIS 3.0, other servers
• Platforms
• Windows 2000
• NT Server 4.0
• NT Workstation 4.0 (Peer Web Services)
• Windows 95 (Personal Web Server)
• NT 3.51 is NOT supported
• Servers
• IIS 3.0 4.0
• OReilly WebSite
• Others?
• File Extension filename.asp
• Language Delimiters lt gt

10
MS FrontPage 2000 HTML Editor

• WYSIWYG (sometimes - though better than before)
• Formatting Aids
• Themes
• Navigation Bars
• Site Maintenance Tool
• broken link checks
• Built-in Wizards and Templates
• Forms
• Database Connection Wizard
• Advantages / Disadvantages

11
Passing Information b/w Pages

• Request.Querystring Developer Input
• Uses
• Structure
• Example
• Web Forms User Input
• Uses
• Structure
• Example

12
Request.Querystring

• Uses
• Pass variables from one page to another
• Persistent variables
• Database connection

13
Request.Querystring

• Structure
• Extended hyperlink
• filename.asp?abc
• filename.asp?nameabcinstitutionxyz
• Reference querystring in body of page
• lt Request.Querystring gt
• lt Request.Querystring(name) gt

14
Request.Querystring - Example

• Goal Pass variable to linked page
• Build extended hyperlink
• http//../../exPass.asp?Querystring

15
Request.Querystring - Example

• Create file to link to - exPass.asp (Att.B)
• Optional assign shorter variable name
• lt fldExampleRequest.Querystring gt
• Reference querystring in body of page
• lt Request.Querystring gt
• lt fldExample gt
• Add other page elements, formatting, links,
  forms, etc.
• Exercise!!

16
Web Forms

• Uses
• Pass variables from one page to another
• Persistent variables
• Database connection
• E-mail connection (mailto timthom_at_iupui.edu)
• Search engines

17
Web Forms

• Structure (Att.A-2)
• Action File ltActionfilename.aspgt (a)
• Fieldstext (f) textarea (h)checkbox (I) radio
  (j)hidden (k)
• Field Attributes
• ltNamefldNamegt
• ltValuexyzgt

18
Web Forms - Example

• Goal Pass form contents from one page to another
• Name
• Title
• Institution
• Email
• Phone

19
Web Forms - Example

• Build HTML form - exPass.asp (Att. B)
• Attention to form field names
• Set actionexPassForm.asp
• Create action file - exPassForm.asp (Att. C)
• Reference form fields in body of page
• lt Request.Form(fieldname) gt
• Add other page elements, formatting, links,
  forms, etc.
• Exercise!!

20
Making Connections

• PUSH Write information TO a database
• Online surveys
• Feedback forms
• Data gathering
• Other?
• PULL Read information FROM a database
• IR Reporting
• Skys the limit
• Data based sites
• Other?

21
Making Connections - Push

• Create database and table
• ODBC database (Access, Excel, Oracle, etc.)
• Column names Form field names
• Define database as System Data Source (DSN)
• Start/Settings/Control Panel/ODBC/System DSN
• Create / Edit ASP file(s)
• Open Connection to database
• SQL INSERT INTO tblName (fields) VALUES
  (fields)
• Lather, Rinse, Repeat

22
Making Connections Push Exercise

• Create database table
• MS Access mdbAir2001.mdb
• Table tblParticipants
• Name
• Title
• Institution
• Email
• Phone
• Define database as System Data Source (DSN)
• Start/Settings/Control Panel/ODBC/System DSN
• DSN Air2001

23
Making Connections Push Exercise

• Create exPush.asp (Att.D)
• Form field names database column names
  (Name, Title, Institution, Email, Phone)
• Form Action exPushAction.asp

24
Making Connections Push Exercise

• Create exPushAction.asp (Att.E)
• To handle form data
• Convert Form fields to variable names lt
  fldName Request.Form(name) gt
• Open Database Connection, using DSN
• SQL Statement INSERT INTO tblParticipants
  (name, ) VALUES ( fldName , )
• Acknowledgement Text / Formatting

25
Making Connections - Pull

• Verify layout of database table
• Define database as a System Data Source
• Start/Settings/Control Panel/ODBC/System DSN
• Create / Edit ASP file(s)
• Open Connection to database
• SQL SELECT FROM tblName ORDER BY
• Incorporate query results into web page
• Table
• Hyperlinks
• Populate forms
• Querystrings

26
Making Connections Pull Exercise

• Verify table layout / attributes
• Column names (Name, Title, Institution, Email,
  Phone)
• Data types (Text, Text, Text, Text, Text)
• Allow Zero Length? (Y, Y, Y, Y,Y)
• Create System Data Source
• Start/Settings/Control Panel/ODBC/System DSN
• DSN Air2001

27
Making Connections Pull Exercise

• Create / Edit exPull.asp (Att.F)
• ASP action file
• Open Connection to database, using DSN
• SQL SELECT FROM tblParticipants
  ORDER BY institution
• Note temporary data set name rs_part

28
Making Connections Pull Exercise

• Incorporate query results into web page
• Read one data record at a time
• lt Do While Not rs_part.EOF gtlt
  rs_part(name) gtlt rs_part.movenext loop
  gt
• Hyperlink - Querystring
• ltA HREFfilename.asp?lt rs_part(name)gt

29
Parting Shots

• Per John Milam and Mike Wood (5/99)
• Take the risk. Build it and theyll come.
• Build it in-house to get more control
• Learn SQL. Use it everywhere you can.
• Webify everything you can.
• Be sure what you want to do with your life.
• Per Tim Thomas
• Content, content, content.
• Align development with office goals.
• Just because youve figured out how to do
  something doesnt mean you should do it.
• Use your powers for good, not evil.

30
Contact Information

• Tim Thomas
• [phone]
• timthom_at_iupui.edu
• Information Management and Institutional Research
• Indiana University Purdue University Indianapolis
• 355 North Lansing Street, AO 139
• Indianapolis, IN 46202
• http//data.imir.iupui.edu/air2001/