Transcript Active Server Page and ODBC.

Active Server Page
• It is a server-side scripting environment for creating
dynamic content.
• ASP are files with .asp extension, containing HTML
tags, text, and script commands.
• Provides an “easy-to-use” alternative to CGI and
ISAPI.
• Allow content developers to embed any ActiveX script
language or server component into their HTML
pages. Tutorial available through option pack menu.
• Provide access to ODBC (Open Data Base
Connectivity) connector.
• Typical VBscript (Visual Basic Scripting Edition) is
used to provide dynamic content. MS JScript.
4/8/99
C. Edward Chow
Page 1
Create ASP with DB access
•
•
•
•
•
•
•
Create an Signup MS Access Database, signup.mdb on the NT
workstation right by Bilbo.
Transfer the signup.mdb to the c:\cs401\scripts directory on Frodo
NT server.
Setup ODBC system Data Source Name for the signup.mdb.
Create a virtual directory with aliase cs401scripts with physical
path c:\cs401\scripts using MS management console.
Create a search.htm web page using FrongPage or other editor. It
asks for the last name in a form and submit the request to retrieve
the whole record of the signup database. Put the search.htm in
c:\cs401\www.
Create a virtual directory with alias cs401www with physical path
c:\cs401\www using MS management console.
Create an Active Server Page, response.asp, which
o
o
o
4/8/99
uses VBscript to retrieve the last name from the form input,
accesses the signup database for the record
return a web page in a table format with all the record with last name
matching the input.
C. Edward Chow
Page 2
Create a signup MS Access Database
 Select Start | Programs | Microsoft Access
 Click the "Blank Database" in "Creating a New
Database Using" pane. Click OK.
 Enter "cs401signup" as the file name for the
database. Click Create.
 In the table tab (default) of the cs401signup
database window, click New.
 In new Table, click the Design View and click OK.
 Enter the field names (LastName, FirstName, and
Address) of the table. You can change the field size.
 Save the table name as signup (default is table1).
4/8/99
C. Edward Chow
Page 3
Create Database
4/8/99
C. Edward Chow
Page 4
Specify Field names
4/8/99
C. Edward Chow
Page 5
Create table for the DB
• Click x icon on the upper right corner.
Click Yes to confirm saving the table.
• Enter “signup” as the table name.
• Click No on creating primary key.
• It shows the signup table was created.
• Double click it or click open.
• Enter several entries of the table.
4/8/99
C. Edward Chow
Page 6
Setup ODBC on NT Server
• Click ODBC Data Sources on control panel.
4/8/99
C. Edward Chow
Page 7
Add MS Access DB
• Click System DSN and Add.
• Select MS Access Driver (*.mdb). Click Finish.
4/8/99
C. Edward Chow
Page 8
ODBC MS Access Setup
•
•
•
Enter Data Source Name (cs401signup)
Enter Description as (cs401 asp DB)
Click Select on Database pane and select the
e:\cs401\scripts\cs401signup.mdb
4/8/99
C. Edward Chow
Page 9
Create Script Virtual Directory
• Select Start | Programs | NT 4.0 option pack | MS IIS
| IIS manager
• Select Frodo, default web site
• Select the action menu | new | virtual server
• Enter alias as cs401scripts
• Enter/browse physical path c:\cs401\scripts
• Check only the “Allow execute access.” Click Finish.
4/8/99
C. Edward Chow
Page 10
Create Query Form Web Page
• Create a signup DB search web page with form input. It
asks for the last name in a form and submit the request to retrieve
the whole record of the signup database.
<form METHOD="post"
ACTION="/cs401scripts/response.asp“>
LastName: <input NAME="LastName" SIZE="30">
<input TYPE="submit" value="submit">
</form>
• Put the search.htm in c:\cs401\www.
4/8/99
C. Edward Chow
Page 11
cs401www Virtual Directory
• Follow the same step to create the cs401www virtual
directory with cs401www as alias and c:\cs401\www
as physical directory.
• Create an Active Server Page, response.asp, which
– uses VBscript to retrieve the last name from the
form input,
– accesses the signup database for the record
– return a web page in a table format with all the
record with last name matching the input.
4/8/99
C. Edward Chow
Page 12
Response.asp
<%@ LANGUAGE = VBScript %>
<%
' ON ERROR RESUME NEXT
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
Conn.Open “cs401signup"
key = Request.Form("LastName")
sql="select * from cs401signup
where LastName='" & key &"'"
RS.open sql,conn,1,1
%>
4/8/99
C. Edward Chow
Page 13
Response.asp (2)
<HTML>
<HEAD>
<TITLE> signup response.asp File </TITLE>
</HEAD>
<BODY BGCOLOR="WHITE">
<center>
<%
IF RS.RECORDCOUNT = 0 THEN
%>
<font size="5"><b>No match result</b></font>
<% end if %>
4/8/99
C. Edward Chow
Page 14
Response.asp (3)
<table border=3 width=410>
<%Do While Not RS.EOF %>
<tr>
<td width=170 valign=top>
<FONT SIZE=4 COLOR=RED><% =rs("LastName") %> </font>
</td>
<td width=170 valign=top>
<FONT SIZE=4 COLOR=RED><% =rs("FirstName") %> </font>
</td>
<td width=170 valign=top>
<FONT SIZE=4 COLOR=RED><% =rs("Address") %> </font>
</td>
</tr>
<%
RS.MoveNext
Loop
%>
4/8/99
C. Edward Chow
Page 15
Response.asp (4)
<%
rs.close
set conn=nothing
%>
</TABLE>
</BODY>
</HTML>
4/8/99
C. Edward Chow
Page 16