Transcript Document
CIS 375—Web App Dev II
ADO I
Introduction
ADO (________ Data Objects) is a Microsoft
technology for accessing data in a database.
ADO is automatically installed with _____.
The common way to access a database from within
an ASP page is to:
Create an ADO ___________ to a database
Open the database connection
Create an ADO __________
Open the recordset
Extract the data you need from the recordset
Close the recordset
Close the connection
2
Database Connections
Before a DB can be accessed from a web page, a DB
connection must be established.
The easiest way to connect to a DB is to use a DSN_____ connection.
A DSN-less connection can be used against any
_______________ DB on your web site.
3
Create a DSN for an ODBC DB
Note that this configuration has to be done on the
computer where your web site is located.
1.
2.
3.
4.
5.
6.
7.
Open the ODBC icon in your Control Panel.
Choose the System DSN tab.
Click on Add in the System DSN tab.
Select the Microsoft Access Driver. Click Finish.
In the next screen, click Select to locate the database.
Give the database a Data Source Name (DSN).
Click OK.
If your web site is located on a remote server, you
must have _________ access to that server, or ask
your web host to do this for you.
4
Create/Open a DB Connection
For an Access DB called “hr.mdb" in a folder called
“fpdb” with your ASP file in the _____ directory, this
code creates and opens a DSN-less ADO connection.
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath(“fpdb/hr.mdb"))
…%>
If you have an ODBC DB with a _____ called “hr,"
connect to the DB with the following ASP code:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open “hr"
…%>
5
Create/Open a Recordset
To be able to read database data, the data must
first be loaded into a _________.
If you opened the DB connection with the first set of
code in the previous slide, you can access the
“employee" table by adding the following code:
set rs=Server.CreateObject("ADODB.recordset")
rs.Open "employee", conn
Or you could use _____ as follows:
set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select * from employee", conn
This stores all table records in the recordset.
6
Display Data
To extract data from a recordset and then close the
recordset and connection, add the following code:
do until rs.EOF
for each x in rs.Fields ‘ “x” refers to a field
Response.Write(x.name) ‘ field name
Response.Write(" = ")
Response.Write(x.value & "<br />") ‘ field value
next
Response.Write("<br />")
rs.MoveNext ‘ move to next record in table
loop
rs.close
conn.close
7
Display Data in an HTML Table
File named display_employees.asp
<html>
<body>
<% set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
<% do until rs.EOF %>
conn.Open(Server.Mappath("fpdb/hr.mdb"))
set rs=Server.CreateObject("ADODB.recordset") <tr>
<%
sql="SELECT * FROM employee"
for each x in rs.Fields
rs.Open sql, conn %>
%>
<table border="1" width="100%">
<td>
<tr>
<%
<%
Response.Write(x.value)
for each x in rs.Fields
%>
response.write("<th>" & x.name & "</th>")
</td>
next %>
<% next
</tr>
rs.MoveNext %>
</tr>
<% loop
rs.close
conn.close %>
</table>
</body>
</html>
Add Records I
First create a form for
data input (form.htm):
<html>
<body>
<form method="post"
action="add.asp">
<table>
<tr>
<td>SSN:</td>
<td><input name="ssn"></td>
</tr>
<tr>
<td>Last Name:</td>
<td><input name="lastname"></td>
</tr>
<tr>
<td>First Name:</td>
<td><input name="firstname"></td>
</tr>
</table>
<br /><br />
<input type="submit" value="Add
New">
<input type="reset" value="Cancel">
</form>
</body>
</html>
9
Add Records II
Then create the ASP file to add records (add.asp):
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath(“fpdb/hr.mdb"))
sql="INSERT INTO employee (ssn,lastname,firstname) VALUES "
sql=sql & "('" & Request.Form("ssn") & "',"
sql=sql & "'" & Request.Form(“lastname") & "',"
sql=sql & "'" & Request.Form(“firstname") & "')"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>
</body>
</html>
10
Update Records I: hr_listforupdate.asp
This file displays all records in a special way
(continues on next slide):
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("../db/hr.mdb"))
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM employee",conn
%>
<h2>List Database</h2>
<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
response.write("<th>" & ucase(x.name) & "</th>")
next
%>
</tr>
hr_listforupdate.asp (continued)
<% do until rs.EOF %>
<tr>
<form method="post" action="hr_update.asp">
<%
for each x in rs.Fields
if x.name="ssn" then%>
<td>
<input type="submit" name="ssn" value="<%=x.value%>">
</td>
<%else%>
<td> <%Response.Write(x.value)%> </td>
<%end if
next
%>
</form>
<%rs.MoveNext%>
</tr>
<%
loop
conn.close
%>
</table>
</body>
</html>
Update Records II: hr_update.asp
<html>
<body>
<h2>Update Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("fpdb/hr.mdb"))
social=Request.Form("ssn")
if Request.form("lastname")="" OR Request.form("firstname")="" then
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM employee WHERE ssn ='" & social & "'", conn
%>
<form method="post" action="hr_update.asp">
<table>
<%for each x in rs.Fields%>
<tr>
<td><%=x.name%></td>
<td><input name="<%=x.name%>" value="<%=x.value%>" size="20"></td>
<%next%>
</tr>
</table>
<br /><br />
<input type="submit" value="Update record">
</form>
hr_update.asp (continued)
<%
else
sql="UPDATE employee SET "
sql=sql & "lastname='" & Request.Form("lastname") & "',"
sql=sql & "firstname='" & Request.Form("firstname") & "'"
sql=sql & " WHERE ssn='" & social & "'"
on error resume next
conn.Execute sql
if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & social & " was updated!")
end if
end if
conn.close
%>
</body>
</html>
Delete Records I:
hr_listfordelete.asp
This file is identical to
hr_listforupdate.asp
except for the following
code:
<form method="post“
action="hr_delete.asp">
instead of:
<form method="post“
action="hr_update.asp">
15
Delete Records II: hr_delete.asp
This file is identical to
hr_update.asp except
for the following code:
sql="DELETE FROM employee"
sql=sql & " WHERE ssn='" & social
& "'"
instead of:
sql="UPDATE employee SET "
sql=sql & "lastname='" & _
Request.Form("lastname") & "',"
sql=sql & "firstname='" & _
Request.Form("firstname") & "'"
sql=sql & " WHERE ssn='" & _
social & "'"
16
ADO Demonstration
How to change the ________ rights of your
Access database:
Open Windows Explorer, find the .mdb file.
Right-click on the .mdb file and select ___________.
Go to the _________ tab and set the access-rights here.
List, edit, update, and delete database records
Add a new record
17
Using Access & FrontPage
SMSU Computer Services has a web page describing
how to create a database connection using MS
Access and FrontPage software.
SMSU Computer Services also has a tutorial on how
to set up a form on a web page and connect it to an
Access database.
18