Transcript ASP Lecture

Marrying HTML and
Databases using Active
Server Pages
JJ Cadiz
Outline
•
•
•
•
•
•
Who is this JJ guy?
What can you do with ASP?
How does ASP work?
Hardware & Software requirements
Data access using ASP
Example ASP systems
Things I won’t cover
•
•
•
•
•
Security
Visual Basic Script
Java Script
Client-side scripting
Scalability
» Concurrency
» Big web sites don’t use MS Access (I hope)
About my background...
• Masters of HCI student, class of 99
• Work full time on a CMU research team
• Have used ASP heavily for past two
years
» data collection
» experimental tools development
• Joining Microsoft Research in August
What can you do with ASP?
Example sites
• Microsoft tech support site
support.microsoft.com
• On-line stores
www.cdw.com
• On-line auction houses
www.bidnow.com
ASP jobs
• If you can marry databases and HTML,
there are companies who want you
• Search job web sites using keywords
“active server pages”
» careers.wsj.com
» www.careerbuilder.com
The Mental Model of ASP
Normal HTML
1. Client requests an html page
2. Server sends client html page
Dynamic HTML with ASP
1. Client requests an .asp file
2. Server processes .asp file, which
generates HTML
3. Server sends generated HTML to client
ASP, SQL, Databases, and
HTML
Web Server
Database(s)
VBScript &
SQL
HTML
Client Browser
- Internet Explorer
- Netscape Navigator
ASP
Hardware & Software for ASP
Server Requirements
• You can’t do ASP on Andrew
• ASP requires an MS Windows
environment
» NT Server with Internet Information Server
» NT Workstation with “Peer Web Services”
(10-user connection limit)
» Win95/98 with Personal Web Server
Developer Tools
• Your favorite HTML editor
• Your favorite script/code editor
• A database (Access, Oracle,
SQLServer)
• My favorite: MS InterDev
Data Access using ASP
Queries in ASP: The Recordset
• The mental model of a recordset
• How to create and use a recordset in
ASP
• Example system: Employee
compensation database
Creating Recordsets in ASP
‘ Establish connection to database
Set empCompConn = Server.CreateObject("ADODB.Connection")
empCompConn.Open ”empComp” ‘empComp must be a DSN
‘ Create the SQL Command and link it to our database
Set empCompCmd = Server.CreateObject("ADODB.Command")
Set empCompCmd.ActiveConnection = empCompConn
empCompCmd.CommandText = "SELECT * FROM Compensation"
‘ Create the recordset and link it to the command
Set empCompRS = Server.CreateObject("ADODB.RecordSet")
Set empCompRS.Source = empCompCmd
empCompRS.LockType = adLockPessimistic
empCompRS.Open
…
empCompRS.Close
Reusing Recordset Objects
empCompRS.Close
empCompCmd.CommandText = "SELECT * FROM Compensation ORDER BY Salary
DESC"
empCompRS.Open
Navigating Recordsets in ASP
empCompRS.Move #rows
empCompRS.MoveFirst
empCompRS.MoveLast
empCompRS.MoveNext
empCompRS.MovePrevious
empCompRS.EOF
‘ JJ’s favorite loop
empCompRS.Open
While(not empCompRS.EOF)
DoMagic
empCompRS.MoveNext
Wend
empCompRS.Close
Accessing Fields
• Moving data from the database to ASP
Dim salary
salary = empCompRS.Fields("salary").Value
• Moving data from ASP to the database
...
empCompRS.Fields("options").Value = 750
...
Creating and Editing Records
• Editing existing records
empCompRS.Edit
...
empCompRS.Update
• Creating records
‘ Doesn’t matter where the record pointer is when you do this
‘ Make sure to ensure unique keys when adding records!
empCompRS.AddNew
...
empCompRS.Update
Creating and Editing Records
• Example: For all the employees making
less than $53,000/year:
» Increase everyone’s salary by $5000/year
» Decrease everyone’s stock options by
250/year
• Add an employee who makes $54,000
and receives 100 options each year
Putting it all together:
Authoring ASP pages
ASP File Structure
• ASP commands go inside <% %>
tags
• Script code goes inside <SCRIPT>
</SCRIPT> tags
• Anything outside of these tags is HTML
• Comments inside ASP or Script code
are prefaced by a single quote
<%
‘ Good programmers write lots of comments!
%>
A simple ASP file
<%
' Simple ASP code that gives the appropriate greeting based on the time of day
' Get the time of day
Dim currentTime
currentTime = now
' Output the correct greeting based on whether it's morning,
' afternoon, or evening
if(currentTime >= #5:00am#) AND (currentTime < #12:00pm#) then
%>
<%
<%
<%
Good morning!
elseif(currentTime >= #12:00pm#) AND (currentTime < #6:00pm#) then %>
Good afternoon!
else %>
Good evening!
end if %>
The current time is <% = currentTime %>
Now let’s design a system...
•
•
•
•
•
An on-line store
The story...
What should our product be?
How much should it cost?
Orders taken via credit card and
shipped via UPS
The design
• Design the database
• Design the HTML pages
• Tell me what the ASP code needs to do