Lecture 8- ActiveX Data Objects
Download
Report
Transcript Lecture 8- ActiveX Data Objects
ADO (Active Data Objects)
1
Using ASP with VBScript to
access database
Display
http://buscom.mcmaster.ca/users/yuanyuf/adoexample/
artist.asp (on buscom server)
http://facbusad1.mcmaster.ca/users/ap1/yuanyuf/artist.a
sp (on FACBUSAD1 server)
Query
http://facbusad1.mcmaster.ca/users/ap1/yuanyuf/search
_artist.asp (search_artist)
Data Entry
http://facbusad1.mcmaster.ca/users/ap1/yuanyuf/add_ar
tist.asp (add_artist)
Design ASP for web database access
Web designer
DB Server
Microsoft
Access
ODBC
DB
ASP
Local
Web site
LAN
Microsoft
SQL
DB
Dream
waver
Remote
Web site
ODBC
Web user
Web
Browser
ASP
Internet
Web Server
Hands on Experience 1:
using the ARTIST table created by Dr.Yuan
Download ADO example zip file from the course
web site. Save it in your Q drive on the
facbusad1 server
Unzip the files using Easyzip.
Save the ASP and picture files in your folder in the
facbusad1 server
You can now access the ASP pages
http://facbusad1.mcmaster.ca/users/ap1/yourusername/artist.asp
(the same for search_artist.asp, add_artist.asp)
Major steps
Create a database on SQL server
Create ODBC DSN on Web server
Create a HTML page to invoke ASP
Create an ASP page and store it on the
Web server root directory
Use browser to access ASP
Open Your Artist database
Open your artist database downloaded
from the ADO example zip file.
Export it to the SQL server using your
ODBC (external- export-moreLink the Artist database to your access
2007.
Create or link to ARTIST table
You can create and link to ARTIST table
on SQL server from Microsoft Access
Specify ODBC in your Windows XP
System DSN: ODBCyuanyuf (use yours)
SQL server: facbusad1.mcmaster.ca
Login: yuanyuf (use your email name)
Password: yuank723 (use your student
id #)
ARTIST Table in Access
Design ASP for web database access
Web designer
DB Server
Microsoft
Access
ODBC
DB
ASP
Local
Web site
LAN
Microsoft
SQL
DB
Dream
waver
Remote
Web site
ODBC
Web user
Web
Browser
ASP
Internet
Web Server
ASP Pages
artist.asp
search_artist.asp
get_artist.asp
add_artist.asp
update_artist.asp
Using Dreamwaver you can look at and edit these pages.
Introduction to Dreamweaver
Access Dreamwaver
All programs – Micromedia - Dreamwaver
Create a New Site
Click on “Manage Sites” (use one of
two options)
In the popup window select “New
Site”
Option 1
Option 2
Local Site Definition
In the Local Info Section
Site Name: <Your Choice, e.g. 4kd3>
Local Folder: <Your Choice in your Q drive
or in your own PC>
HTTP Address:
http://facbusad1.business.mcmaster.ca/user
s/ap1/<your MACID>
This is where your web site is (your Q drive)
Remote Site Definition
In the Remote Info Section
Access: FTP
FTP Host: facbusad1.business.mcmaster.ca
Login: < your MACID>
Password: <your MACID PASSWORD>
Note: this is used when you connect the web
site from home.
When complete, click OK.
At the sites window selete the new Site
and click DONE.
View se
Create a new ASP VBScript
View folder
User Interface
Menu
Select File View to be displayed in Area 1 dropdown
(Local View/Remote View)
Select Up Arrow to upload files from Local View to
Remote View
Select Down Arrow to download files from Remote View
to Local View
Up and Down Arrows to synchronize files between
Local and Remote Views
Folder
Files located in the current selected location
HTML Objects
Code Editing Area
Preview Editing Area
Basic Structure of HTML
<HTML>
<HEAD>
<TITLE> web page title text </TITLE>
</HEAD>
<BODY>
Web page content text and tags
</BODY>
</HTML>
Display data: artist.asp
http://buscom.mcmaster.ca/users/yuanyuf/adoexample/artist.asp
http://facbus.mcmaster.ca/users/ap1/yuanyuf/artist.asp
Create a HTML page
Artist
Artist HTML
<html><head><title>Artist</title></head>
<body>
<p align="left">ARTIST</p>
<table border="1" width="55%">
<tr><td>Name</td> <td>Nationality</td>
<td>Picture</td></tr>
<tr><td>Nancy</td><td>American</td>
<td><img src="pic1.jpg" width="61"
height="72"></td></tr>
</table></body></html>
Relative path
Root(/)
home.html
David
service.html
Asp-bin
login.asp
Working From:
service.html
prod.html
login.asp
Nancy
prod.html
Images
logo.jpg
Relative Path:
Images/logo.jpg
../David/Images/logo.jpg
../Images/logo.jpg
Add Database connection
mySql: a string variable that stores SQL
statement
myConn: an connection object that specifies
the database connection
myRS: a record set object that stores
database query results
Set Connection
Open Connection
Execute query
Add Database connection
<%
Dim myConn, myRS, mySql
mySql="select * from ARTIST"
set myConn =
Server.CreateObject("ADODB.Connection")
myConn.Open
"dsn=ODBCyuanyuf;uid=yuanyuf;pwd=yuank723"
Set myRs = myConn.Execute(mySql)
%>
Display data to HTML
Using record set to access query result
<%=RecordSet (“FieldName”)%>
Using a do while loop to display multiple
records in a table or free style line
<%do while not RecordSet.EOF%>
using HTML tags to display one record result
<%RecordSet.MoveNext%>
<%loop%>
Display data to HTML
<table border="1" width="55%">
...
<tr>
<%do while not myRs.EOF%>
<tr><td><%=myRs("Name")%></td>
<td><%=myRs("Nationality")%></td>
<td><img src="<%=myRs("Picture")%>"
width="61" height="72"></td> </tr>
<%myRs.MoveNext%>
<%loop%>
</table>
<%myRs.close%>
Query a Database
Using HTML form to submit a query
criteria
Assemble a database query using the
input from the form
Execute a query
Display query results in HTML
Search Artist
Basics of a Form
<form methods = “post” action=“…asp”>
<input type=“text”…>
<input type=“password”…>
<select>…
<option>…</option>
</select>
<input type=“submit”..>
<input type=“reset”…>
</form>
Form for Search artist
<form method="POST" action="get_artist.asp">
<p><select name="Nationality" size="1">
<option selected
value="American">American</option>
<option value="Canadian">Canadian</option>
<option value="Spanish">Spanish</option>
</select>
<input type="submit" value=“Get it" name="B1">
<input type="reset" value="Reset” name="B2">
</p>
</form>
Assemble a query in
get_artist.asp
<%Dim myConn, myRS, mySql,strNationality
strNationality =
Request.Form("Nationality")
mySql="select * from ARTIST where
Nationality = '" & strNationality & "'"
%>
Data Entry Form
Data Entry Form
add_artist.asp
<form method="POST" action="update_artist.asp">
<p>Name: <input type="text" name="Name" size="20"></p>
<p>Nationality:<select name="Nation" size="1">
<option selected value="American">American</option>
<option value="Canadian">Canadian</option>
<option value="Spanish">Spanish</option>
</select></p>
<p>Picture File Name: <input type="text" name=“Picture"
size="20"></p>
<p><input type="submit" value="Submit" name="B1">
<input type="reset" value="Reset"
name="B2"></p> </form>
Enter or modify data in
Database
Using form to collect input data
Assemble a query for database
updating
Connect the database and lock the
table
Update database using input from the
form
Display data to confirm the updating
ADD Data to Database
update_artist.asp
<%
Const adoCursorType = 2 'dynamic cursor
Const adoLockType = 3 'optimistic locking
Const adoIsolationLevel = 4096 'means cursor
stability
Dim myConn, myRS, mySql
set myConn =
Server.CreateObject("ADODB.Connection")
myConn.IsolationLevel = adoIsolationLevel
myConn.Open
"dsn=ODBCyuanyuf;uid=yuanyuf;pwd=yuank723“
Update Database
update_artist.asp
mySql="select * from ARTIST"
Set myRs =
Server.CreateObject("ADODB.Recordset")
myRs.Open mySql, myConn, adoCursorType,
adoLockType
myRs.AddNew
myRs("Name")= Request.Form("Name")
myRs("Nationality")=Request.Form("Nation")
myRs(“Picture")= Request.Form(“Picture")
myRs.Update
myRs.Close
myConn.Close
http://buscom.mcmaster.ca/users/yuanyuf/adoexample/drop_table.asp
http://facbusad1.mcmaster.ca/users/ap1/yuanyuf/drop_table.asp
Drop Table
<%' Get the password and Table name from the form %>
<p><% strTable=Request.Form("droptb")
strPassword=Request.Form("password") %>
<%' Check for username and password, if empty, show a warning
message and try again%>
<%
IF (Len(strUsername) > 0 and Len(strPassword) > 0) THEN
'Connect to the database
set myConn = Server.CreateObject("ADODB.Connection")
myConn.Open
"dsn=ODBC"&strUsername&";uid="&strUsername&pwd="&strPas
sword
'Drop the table if table name is provided by user
IF (Len(strTable) > 0) THEN
mySql = "DROP TABLE " & strTable
set myRs = myConn.Execute(mySql) %>
<p>Table "<%=strTable%>" was successfully dropped.</p>
<% END IF %>
List all the tables
<%
'List all the table names in the
database for the user
mySql = "select table_name from
information_schema.tables"
set myRs = myConn.Execute(mySql)
%>
ADO Object Model review
Page 352
Figure 13-14
© 2000 Prentice Hall
ADO Object Model review
Connection object
RecordSet object
Fields Collection
Errors Collection
Command object
Hands on Experience 2: create
your own ARTIST database
Create a ARTIST table (ID, Name, Nationality, Picture),
enter your own data, and upload your database to
BUSCOM SQL server
Connect to your own SQL database by modifying the line
in ASP files:
myConn.Open "dsn=ODBCyouremailname;
uid=youremailname;pwd=yourstudentid”
You can now access the ASP pages
http://facbusad1.mcmaster.ca/users/ap1/yourname/artist.asp
as well as search_artist.asp, add_artist.asp
You can also using Access to check and modify your SQL
database through dynamic link.
Access SQL server and Web
server Q drive from home
You can link to SQL Server through Internet
from home. Please see this
site: (http://www.mcmaster.ca/uts/network/v
pn/). It details how you can connect to the
university's network from off-campus so that
your computer can access the SQL server.
You can use ftp://facbusad1.mcmaster.ca/ to
access the Q driver on the facbusad1 server.
The useid/password is your MacID and its
password.