slides - Mark Dixon`s web site

Transcript slides - Mark Dixon`s web site

22 – Web applications:
Writing data to Databases
using ASP
Mark Dixon, SoCCE
SOFT 131
Page 1
Session Aims & Objectives
• Aims
– To introduce the fundamental ideas involved in
using server-side code to write data to
databases
• Objectives,
by end of this week’s sessions, you should be able to:
– create an ASP web page that allows the user to
store data in database
Mark Dixon, SoCCE
SOFT 131
Page 2
Database Permissions 1
• In order for ASP to
write to a database
– Need to give write
access to Internet
Guest Account for
database file
(People.mdb)
• Right-click on file in
Windows Explorer
(the following screens
are for Windows 2000)
Mark Dixon, SoCCE
SOFT 131
Page 3
Database Permissions 2
• Click Security tab
• Click Add button
Mark Dixon, SoCCE
SOFT 131
Page 4
Database Permissions 3
• Select Internet Guest Account IUSR_ …
Click
Add
button
Click
OK
button
Mark Dixon, SoCCE
SOFT 131
Page 5
Database Permissions 4
• Select Internet
Guest Account
• Ensure write
access is on
Mark Dixon, SoCCE
SOFT 131
Page 6
Writing data to a database
• create recordset
• open recordset
– dynamic cursor, pessimistic locking
• to add a record
– use to AddNew method
rs.AddNew
• to delete a record
– use the Delete method
rs.Delete
• to change existing data
– assign a new value to fields
rs.Fields("Surname").Value = "Fred"
Mark Dixon, SoCCE
SOFT 131
Page 7
Example 1: Person Edit (html)
<html>
<head>
<title>Person's Details</title>
</head>
<body>
<p><center><b><font size=+2>Person's Details</font></b></center>
<%
' ASP code will go here (next slide).
%>
PersonEdit.asp
<form name="frmPerson" action="PersonEdit.asp" method=post>
Surname:
<input name="txtSurname" type="text"
value="<%=Surname%>"><br>
<input name="btnPrev" type="submit" value="Previous">
<input name="btnSave" type="submit" value="Save">
<input name="btnNext" type="submit" value="Next">
</form>
</body>
</html>
Mark Dixon, SoCCE
SOFT 131
Page 8
Example 1: Person Edit (ASP)
<%
Const cs = "…"
Dim rs
Dim Surname
Set rs = CreateObject("ADODB.Recordset")
rs.Open "Person", cs, 3, 3
If Session("curID") <> "" Then
rs.Find "[ID] = " & Session("curID")
If Request.Form("btnPrev") <> "" Then
rs.MovePrevious
ElseIf Request.Form("btnNext") <> "" Then
rs.MoveNext
ElseIf Request.Form("btnSave") <> "" Then
rs.Fields("Surname") = Request.Form("txtSurname")
rs.Update
End If
End If
Session("curID") = rs.Fields("ID").Value
Surname = rs.Fields("Surname").Value
rs.Close
Set rs = Nothing
%>
Mark Dixon, SoCCE
SOFT 131
Page 9
People Database (with Hobbies)
Person
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]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon, SoCCE
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
SOFT 131
PersonID
1
1
1
2
2
1
Page 10
SQL & MS access queries
• MS Access
– Queries: select data from database
– really SQL select statements
– can use queries to test SQL code
MS Access: People.mdb
Mark Dixon, SoCCE
SOFT 131
Page 11
SQL: Joining tables
Two tables
SELECT *
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
Matching records
ID
Surname
Forenames
Phone
email
1
Dixon
Mark
01752 232556
[email protected]
1
Archery
1
1
Dixon
Mark
01752 232556
[email protected]
2
Herpetology
1
1
Dixon
Mark
01752 232556
[email protected]
3
Music
1
1
Dixon
Mark
01752 232556
[email protected]
6
Hitting people with swords
1
2
Smith
John
01752 111111
[email protected]
4
Football
2
2
Smith
John
01752 111111
[email protected]
5
Rugby
2
Mark Dixon, SoCCE
HobbyID
SOFT 131
Description
PersonID
Page 12
SQL: DISTINCT records
SELECT DISTINCT [ID], [Surname]
FROM [Person], [Hobby]
WHERE [Person].[ID] = [Hobby].[PersonID];
ID
Surname
1 Dixon
2 Smith
Mark Dixon, SoCCE
SOFT 131
Page 13
Query Strings
• Data can be added to end of URL:
http://localhost/page.asp?Surname=Bob
Query String
• ASP code can use this data:
– Request.QueryString("Surname")
• would return the value "Bob"
• Form method=get
– data automatically added to query string
Mark Dixon, SoCCE
SOFT 131
Page 14

slides - Mark Dixon`s web site

Transcript slides - Mark Dixon`s web site

Directory