Intra Internet Databases

1
Intra / Internet Databases

• Part 1

2
HTML

• Markup Language
• Content
• Tags

3
Dynamic HTML
Scripting

• Markup Language
• Content (static and dynamic)
• Tags (static/html and dynamic)
• Programming Languages
• Database (SQL)
• Procedural (VB, VBScript, Javascript, etc)

4
Multi-Tier Applications
CGI / Perl Cold Fusion (Allaire/Macromedia) Activ
e Server Pages (Microsoft) PHP (Open Source)
Server Side
Data Servers
Application Server
Client
Client Side
Client
Client
Client
Client
5
Multi-Tier Applications (Our Case)
CGI / Perl Cold Fusion (Allaire/Macromedia) Activ
e Server Pages (Microsoft) PHP (Open Source)
Server Side
MISSQL2005
Classweb2
Understands SQL
Understands HTML
Mozilla
Client Side
Safari
Opera
Firefox
Internet Explorer
6
Components of an ASP Application

• Virtually any commercial database engine
• Plain HTML files, photos, sound clips, video, etc
• Special ASP files (aka, Scripts)
• HTML tags lt gt
• ASP "tags" lt gt

7
Sample ASP Application
Sorted list from the database
His EID is passed to the next page
8
His EID is received from the previous page
9
HTML Tag Review

• HTML The Basics Handout
• ltHTMLgt, ltHEADgt, ltBODYgt
• ltH2gt, ltH3gt
• ltPgt, ltBRgt
• ltIMG SRC photo_urlgt
• ltTABLEgt, ltTRgt, ltTHgt, ltTDgt
• HTML Input Forms Handout
• ltFORM ACTION next_page_urlgt
• ltSELECTgt, ltOPTIONgt
• ltINPUTgt

10
ASP Overview

• yada yada lt blah blah gt yada yada
• conn
• rst
• X rst("DatabaseField")
• Do Until / Loop
• Response.Write("Send this to the browser")
• Request.Form("PreviousPageValue")

11
Database Design
12
Page1.asp
13
Page1.asp

• lthtmlgt
• ltheadgt
• lttitlegtMote's First ASP Demolt/titlegt
• lt/headgt
• ltbodygt
• lt
• Response.Write("lth2gtASP Demolt/h2gt")

Uninterpreted HTML
Interpreted ASP
14
Page1.asp

• ' Open connection to back-end SQL Server database
• myServer"MISSQL2005"
• myDatabase"MIS325_msbaa001"
• BackEnd"ProviderSQLOLEDB Integrated
  SecuritySSPI
• Persist Security InfoFalse Initial Catalog"
• myDatabase " Data Source" myServer
• Set connServer.CreateObject("ADODB.Connection")
• conn.CommandTimeout15
• conn.ConnectionTimeout15
• conn.Open BackEnd

15
Page1.asp

• ' Open employee recordset
• sql"SELECT EID, FirstName, LastName FROM
  tblEmployee
• WHERE CurrentEmployee'Y' ORDER BY LastName,
  FirstName"
• Set rstServer.CreateObject("ADODB.Recordset")
• rst.Open sql, conn
• Response.Write("ltform method'post'
  action'Page2.asp'gt")
• Response.Write("lth3gtEmployeeslt/h3gt")

16
Page1.asp

• ' Display employees (as drop-down list)
• Response.Write("ltselect name'EmployeeList'gt")
• Do Until rst.EOF
• EIDrst("EID")
• FullNamerst("FirstName") " "
  rst("LastName")
• Response.Write("ltoption value'" EID "'gt"
• FullName "lt/optiongt")
• rst.MoveNext
• Loop
• Response.Write("lt/selectgt")

17
Page1.asp

• ' Close employee recordset
• rst.Close
• Set rstNothing
• ' Display command button
• Response.Write("ltbrgtltbrgt")
• Response.Write("ltinput type'submit'
  value'Display'gt")
• Response.Write("lt/formgt")

18
Page1.asp

• ' Close connection to database
• conn.Close
• Set connNothing
• gt
• lt/bodygt
• lt/htmlgt

Interpreted ASP
Uninterpreted HTML
19
Page1.asp
TK999
20
TK999
SELECT FROM tblEmployee WHERE EID'TK999'
SELECT FROM tblPaycheck WHERE EID'TK999' ORDER
BY PayDate
21
Page2.asp

• lthtmlgt
• ltheadgt
• lttitlegtMote's First ASP Demolt/titlegt
• lt/headgt
• ltbodygt
• lt
• Response.Write("lth2gtASP Demolt/h2gt")

22
Page2.asp

• ' Open connection to back-end SQL Server database
• myServer"MISSQL2005"
• myDatabase"MIS325_msbaa001"
• BackEnd"ProviderSQLOLEDB Integrated
  SecuritySSPI
• Persist Security InfoFalse Initial Catalog"
• myDatabase " Data Source" myServer
• Set connServer.CreateObject("ADODB.Connection")
• conn.CommandTimeout15
• conn.ConnectionTimeout15
• conn.Open BackEnd

23
Page2.asp

• ' Fetch EID from previous page
• SelectedEIDRequest.Form("EmployeeList")
• ' Open employee recordset (only one record
  expected)
• sql"SELECT FROM tblEmployee WHERE EID'"
• SelectedEID "'"
• Set rstServer.CreateObject("ADODB.Recordset")
• rst.Open sql, conn

SELECT FROM tblEmployee WHERE EID'TK999'
24
Page2.asp

• ' Process employee data
• FullNamerst("FirstName") " " rst("LastName")
• Addressrst("Street")
• If Len(rst("Apt"))gt0 Then
• AddressAddress ", " rst("Apt")
• End If
• Locationrst("City") ", " rst("State") " "
  rst("Zip")

25
Page2.asp

• If Len(rst("Phone"))gt0 Then
• Phonerst("Phone")
• Else
• Phone"No phone number on file"
• End If
• PhotoURLrst("PhotoURL")

26
Page2.asp

• ' Display employee data
• Response.Write("lth3gtEmployeelt/h3gt")
• Response.Write("ltpgt")
• Response.Write(FullName "ltbrgt")
• Response.Write(Address "ltbrgt")
• Response.Write(Location "ltbrgt")
• Response.Write(Phone)
• Response.Write("lt/pgt")

27
Page2.asp

• ' Display employee photograph (if available)
• If Len(PhotoURL)gt0 Then
• Response.Write("ltimg src'" PhotoURL "'
  height150 border2gt")
• End If
• ' Close employee recordset
• rst.Close
• Set rstNothing

28
Page2.asp

• ' Open sorted paycheck recordset
• sql"SELECT FROM tblPaycheck WHERE EID'"
• SelectedEID "' ORDER BY PayDate"
• Set rstServer.CreateObject("ADODB.Recordset")
• rst.Open sql, conn

SELECT FROM tblPaycheck WHERE EID'TK999' ORDER
BY PayDate
29
Page2.asp

• ' Display paychecks (as table/grid)
• Response.Write("lth3gtPaycheckslt/h3gt")
• Response.Write("lttable border0gt")
• Response.Write("lttrgt")
• Response.Write("ltth align'left'gtDatelt/thgt")
• Response.Write("ltth align'center'gtChecklt/thgt")
• Response.Write("ltth align'right'gtAmountlt/thgt")
• Response.Write("lt/trgt")
• TotalAmount 0

30
Page2.asp

• Do Until rst.EOF
• Response.Write("lttrgt")
• Response.Write("lttd align'left'gt"
  rst("PayDate")
• "lt/tdgt")
• Response.Write("lttd align'center'gt"
• rst("CheckNumber") "lt/tdgt")
• Response.Write("lttd align'right'gt"
• FormatCurrency(rst("NetAmount"),2) "lt/tdgt")
• Response.Write("lt/trgt")
• TotalAmount TotalAmount rst("NetAmount")
• rst.MoveNext
• Loop

31
Page2.asp

• Response.Write("lttrgt")
• Response.Write("lttd align'left'gtlt/tdgt")
• Response.Write("lttd align'center'gtTotallt/tdgt")
• Response.Write("lttd align'right'gt"
• FormatCurrency(TotalAmount,2) "lt/tdgt")
• Response.Write("lt/trgt")
• Response.Write("lt/tablegt")
• ' Close paycheck recordset
• rst.Close
• Set rstNothing

32
Page2.asp

• ' Display button back to first page
• Response.Write("ltform method'post'
  action'Page1.asp'gt")
• Response.Write("ltinput type'submit'
  value'Return'gt")
• Response.Write("lt/formgt")
• ' Close connection to database
• conn.Close
• Set connNothing
• gt
• lt/bodygt
• lt/htmlgt