Transcript Database Tutorial

Using a Database in ASP
January 27, 2001
Database Tutorial
1
Working with Databases
• Many ways to skin a cat
• We'll show basics
• Retrieve records
• Add records
• Modify records
• Delete records
• Use ActiveX Data Objects (ADO)
January 27, 2001
Database Tutorial
2
What Is A Relational Database?
• One or more tables of related data
Record
January 27, 2001
Student
Abe
Barbara
Collete
DeeDee
Freddy
Gertrude
Hecliffe
Address
19704 SE 281st
231 45th Ave S.
12397 22nd. Ave N.
18209 NE 172nd
411 Main St.
202 S. Madison
912 E. Wood St.
School
Delta
Delta
Wauseon
Delta
Swanton
Wauseon
Delta
Database Tutorial
School
Delta
Swanton
Wauseon
Field
Address
401 Elm St
721 Adrian Ave
13211 SE 92nd
3
Your Old Friend
- A Web-Based Application -
default.asp
Your Home
mynewform.html
Your Form
mydatabase.asp
Your ASP
Your Database
January 27, 2001
Database Tutorial
4
General Logic Flow
mydatabase.asp
Your ASP
• Open your database
• Get the rows you need
• Add, revise, delete rows
• Send data back to the user
Your Database
January 27, 2001
Database Tutorial
5
ASP Database Objects
Connection Object
Recordset
Objects
Collete 12397 22nd. Ave N. Wauseon
Gertrude 202 S. Madison
Wauseon
• Created by createobject
• Open method opens database
• Execute method creates recordset
Freddy 411 Main St Swanton
Abe
Barbara
DeeDee
Hecliffe
19704 SE 281st Delta
231 45th Ave S. Delta
18209 NE 172nd Delta
912 E. Wood St. Delta
• Created by:
• Connection's execute method
• Createobject method
• Open method creates subset of table
• Has add, update, and delete methods
mydatabase
January 27, 2001
Database Tutorial
6
Opening Your Database
oConnection
• Create a connection object
dim oConnection 'Connection object
Set oConnection = server.createobject("ADODB.Connection")
• Set the connection string
dim sConnString 'Connection string
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _
& Server.MapPath("\millieneorr\db\rsFeedback.mdb")
rsFeedback
• Open the database
oConnection.open(sConnString)
January 27, 2001
Database Tutorial
7
• Open your database
• Get the rows you need
• Add, revise, delete rows
• Send data back to the user
January 27, 2001
Database Tutorial
8
Getting the Rows You Need: The SELECT Command
• Syntax:
SELECT MyField1, MyField2, MyFieldx FROM MyTable _
ORDER BY MyField2, Myfield3 _
WHERE MyField1="SomeValue" AND Myfieldx > 3
• You can use "wild cards"
SELECT * FROM MyTable ORDER BY MyField2
Legend:
Red = Required Syntax
Green = Optional Syntax
Brown = Values you provide
January 27, 2001
Database Tutorial
9
Getting the Rows You Need: The RecordSet Object
• For reading only:
– Create and populate with Connection object's execute method
dim oFeedback_rs 'RecordSet object
set oFeedback_rs = oConnection.execute("Select Name from tblFeedback")
• For reading and updating:
– Create with server objects's CreateObject method
– Populate with RecordSet's open method
January 27, 2001
Database Tutorial
10
Showing the Data You Retrieved
dim str
Do until oFeedback_rs.EOF
str = oFeedback_rs("Name")
response.write(str & "<BR>")
oFeedback_rs.MoveNext
loop
January 27, 2001
Database Tutorial
11
Finding Stuff in Your RecordSet
• "Cursor" points to current record
• Methods for moving cursor:
– MoveFirst
MoveLast
– MoveNext
MovePrevious
Move
• BOF and EOF Boolean properties indicate beginning and end
of RecordSet
• Use "do until" to loop through records
• Field value is my_recordset_name("myfieldname")
January 27, 2001
Database Tutorial
12
Revising, Deleting, Adding Records
• Special parameters sent to RecordSet open method
• Locks record while you update
• Keeps fields in sync with other user updates
January 27, 2001
Database Tutorial
13
Revising Records
• Open RecordSet
• Modify fields in record
• Invoke RecordSet update method
January 27, 2001
Database Tutorial
14
Deleting Records
• Open RecordSet
• Navigate to appropriate record
• Invoke RecordSet delete method
January 27, 2001
Database Tutorial
15
Adding Records
• Open RecordSet
• Invoke RecordSet's AddNew method
• Set field values
• Invoke RecordSet's update method
January 27, 2001
Database Tutorial
16
Alphabet Soup
• Structured Query Language
• ActiveX Data Objects
• Open DataBase Connectivity
• Object Linking and Embedding
• OLE DB
• Universal Data Access
January 27, 2001
Database Tutorial
17
mydatabase.asp (Part 1)
<% Option Explicit %>
<HTML>
<HEAD>
<TITLE>My First Database ASP Program</TITLE>
</HEAD>
<BODY>
<%
On Error Resume Next 'Continue if error encountered
dim
dim
dim
dim
oConnection
sConnString
oFeedback_rs
str
'Connection object
'Connection string
'RecordSet object
'String variable
'***** Example 1: Selecting records from a table
response.write("*** Example 1: Selecting records from a table ***<BR>")
'Create connection object, define connection string, open database
Set oConnection = server.createobject("ADODB.Connection")
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _
& Server.MapPath("\millieneorr\db\rsFeedback.mdb")
oConnection.open(sConnString)
January 27, 2001
Database Tutorial
18
Mydatabase.asp (part 2)
'Create recordset of names from all records in the feedback table
set oFeedback_rs = oConnection.execute("Select Name from tblFeedback")
'Display the names
Do until oFeedback_rs.EOF
str = oFeedback_rs("Name")
response.write(str & "<BR>")
oFeedback_rs.MoveNext
loop
oConnection.close
'close database
'You're done!!
%>
</BODY>
</HTML>
January 27, 2001
Database Tutorial
19