ASP Databases - Mark Dixon`s web site

Transcript ASP Databases - Mark Dixon`s web site

17 – Persistent data storage:
relational databases and ADO
Mark Dixon
Page 1
Session Aims & Objectives
• Aims
– To introduce the fundamental ideas involved in
persistent data storage and relational databases
• Objectives,
by end of this week’s sessions, you should be able to:
– create a relational database table
– create a web page (ASP) that displays data
from a single table in a database
• using ActiveX Data Objects (ADO)
Mark Dixon
Page 2
Persistent Data Storage
• So far
– all programs lose data when closed
• Not realistic
– typically data stored to persistent storage device
(e.g. hard disk, key drive, floppy disk, CD-RW)
• Use either
– flat files
– database (relational, or object oriented)
Mark Dixon
Page 3
Example: People (Specification)
• User requirement:
– Display list of people from database online
• How:
– Combine our knowledge of:
• ASP (active server pages)
• ADO (activeX data objects)
Mark Dixon
Page 4
Example: People (Database)
• Information organised into
– tables (e.g. person)
– fields (e.g. phone)
– records (e.g. 1 Dixon Mark 01752 232556 …)
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Johnson
Mark Dixon
Field
Record
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Page 5
Example: Music (Database)
• How many fields?
• How many records?
Track
Mark Dixon
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm
US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness
Disturbed
US
Page 6
Database Management Systems
• DBMS provides facilities for:
– creating and changing databases
• add/remove records
• add/remove fields
• add/remove data
– For example:
•
•
•
•
•
•
Mark Dixon
Microsoft Access
dBase
Borland Paradox
MySQL
Microsoft SQL Server
Oracle
home/small business
large scale
Page 7
MS Access
Music database
Mark Dixon
Page 8
ActiveX Data Objects (what & why)
• ActiveX Data Objects (ADO)
– common database interface
• allow you to write code for any DBMS
MS Access
VB or
VB Script
code
DB front end
Mark Dixon
ADO
MS SQL Server
…
…
Page 9
ADO Record Set Object
• Used to interact with tables
• Properties
– BOF: true if at start of record set (before first record)
– EOF: true if at end of record set (after last record)
– Fields: used to get and set data values
• Methods
–
–
–
–
–
–
Mark Dixon
Open: used to open record set
MoveFirst: moves focus to first record
MovePrevious: moves focus to previous record
MoveNext: moves focus to next record
MoveLast: moves focus to last record
Close: closes record set
Page 10
Using Record Sets
Connection string – identify database
<%
Const cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Music.mdb; Persist Security Info=False"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
…
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
Mark Dixon
Open record set with table
Move to next record
Close record set
Page 11
Connection Strings: UDL files
• Generate connection strings
– Right click on desktop
– Select New, Text Document
– Rename to *.UDL (Yes to warning message)
– Double click
– Select provider
– Click Next
– Select or enter DB name
– Click Test Connection button
– Click OK
– Open with Notepad, cut & paste text
Mark Dixon
Page 12
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
rs
<%
Const cs = "Provider=…;Data Source=D:\People.mdb; "
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
</body>
</html>
Mark Dixon
Page 13
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
rs
<%
Const cs = "Provider=…;Data Source=D:\People.mdb; "
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
</body>
</html>
Mark Dixon
Page 14
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
Const cs = "Provider=…;Data Source=D:\People.mdb; "
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
rs
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
</body>
</html>
Mark Dixon
Page 15
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
Const cs = "Provider=…;Data Source=D:\People.mdb;"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
rs
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Dixon
</body>
</html>
Mark Dixon
Page 16
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
Const cs = "Provider=…;Data Source=D:\People.mdb;"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
rs
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Dixon
Smith
</body>
</html>
Mark Dixon
Page 17
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
Const cs = "Provider=…;Data Source=D:\People.mdb;"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
rs
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Dixon
Smith
Jones
</body>
</html>
Mark Dixon
Page 18
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
Const cs = "Provider=…;Data Source=D:\People.mdb;"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
rs
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Dixon
Smith
Jones
Bloggs
</body>
</html>
Mark Dixon
Page 19
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
Const cs = "Provider=…;Data Source=D:\People.mdb;"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
rs
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Dixon
Smith
Jones
Bloggs
Anderson
</body>
</html>
Mark Dixon
Page 20
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
Const cs = "Provider=…;Data Source=D:\People.mdb;"
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
rs
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Dixon
Smith
Jones
Bloggs
Anderson
</body>
</html>
Mark Dixon
Page 21
People.aspx
Example: People
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
rs
<%
Const cs = "Provider=…;Data Source=D:\People.mdb; "
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
%>
</body>
</html>
Mark Dixon
Page 22
Example: People v2
<script runat="server">
Const cs = "Provider=…;Data Source=D:\People.mdb;"
Sub DisplayPeople()
Dim rs
rs = CreateObject("ADODB.Recordset")
rs.Open("Person", cs)
Do Until rs.EOF()
Response.Write(rs.Fields("Surname").Value)
Response.Write(" ")
rs.MoveNext()
Loop
rs.Close()
rs = Nothing
End Sub
</script>
• Use procedure to
separate:
– code detail
– html
<html>
<head><title>Personal Address Book</title></head>
<body>
<center>
Personal Address Book</center>
<%
DisplayPeople()
%>
</body>
</html>
Mark Dixon
Page 23
Tutorial Exercise: People
• Task 1: Create your own People database:
–
–
–
–
–
Open MS Access
Create a new database file
Create a new table
Create fields
Enter data
• Task 2: Create the People v2 asp page (as per the lecture)
to display data from the database.
• Task 3: Modify your page so that it displays phone number
as well as the person's name.
• Task 4: Modify your page so that the user can type a letter,
and only names starting with that letter are displayed.
• Task 5: Modify your page so that the user can type a
series of numerical digits and only phone numbers
containing those digits are displayed.
• Task 6: Modify your page so that it displays the data in an
html table.
Hint: use Response.Write to insert the appropriate tags.
Mark Dixon
Page 24
Tutorial Exercise: Music
• Task 1: Create your own Music Database.
• Task 2: Create an asp page to display data
from this database.
• Task 3: Modify your page so that the user
can type the name of an artist, and only
tracks by that artist are displayed
• Task 4: Make your page case in-sensitive
(i.e. UPPER or lower case makes no
difference)
Mark Dixon
Page 25

ASP Databases - Mark Dixon`s web site

Transcript ASP Databases - Mark Dixon`s web site

Directory