slides

Transcript slides

24 – Web applications:
Writing data to Databases
using ASP
Mark Dixon, SoCCE
SOFT 131
Page 1
Admin
• Module Feedback Questionnaire
in general feedback should be:
– specific & detailed enough to take action
– timely enough to take action
• Most effective feedback:
– informal comments from students
during/after tutorials/lectures
Mark Dixon, SoCCE
SOFT 131
Page 2
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 3
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 4
Database Permissions 2
• Click Security tab
• Click Add button
Mark Dixon, SoCCE
SOFT 131
Page 5
Database Permissions 3
• Select Internet Guest Account IUSR_ …
Click
Add
button
Click
OK
button
Mark Dixon, SoCCE
SOFT 131
Page 6
Database Permissions 4
• Select Internet
Guest Account
• Ensure write
access is on
Mark Dixon, SoCCE
SOFT 131
Page 7
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 8
Example: 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 9
Example: 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 10
Include files
• web pages
– mix HTML, VB Script, and SQL
• can become messy
• use include files to
– modularise (break up) code
Mark Dixon, SoCCE
SOFT 131
Page 11
Example: People.asp
<html>
<head>
<title>Personal Address Book</title>
</head>
<body>
<p><center><b><font size=+2>Personal Address Book</font></b></center>
<%
Const cs = " … … "
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open "Person", cs
Do Until rs.EOF
Response.Write rs.Fields("Surname").Value
Response.Write "<br>"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
%>
</body>
</html>
Mark Dixon, SoCCE
SOFT 131
Page 12
Example: People2.asp
People2.asp
<html>
<head><title>Personal Address Book</title></head>
<body>
<p><center><b><font size=+2>
Personal Address Book</font>
</b></center>

People.inc
</body>
</html>
<%
Const cs = " … … "
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open "Person", cs
Do Until rs.EOF
Response.Write rs.Fields("Surname").Value
Response.Write "<br>"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
%>
Mark Dixon, SoCCE
SOFT 131
Page 13
Example: Procedures
People3.asp
People.inc
<html>
<head>
<title>Personal Address Book</title>
<!-#include file ="People.inc"
-->
</head>
<body>
<p><center><b><font size=+2>
Personal Address Book
</font></b></center>
<%
ShowPeople
%>
</body>
</html>
Mark Dixon, SoCCE
<%
Const cs = " … … "
Sub ShowPeople()
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open "Person", cs
Do Until rs.EOF
Response.Write rs.Fields("Surname").Value
Response.Write "<br>"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
%>
SOFT 131
Page 14
Example: Parameters
People4.asp
<html>
<head>
<title>Personal Address Book</title>
<!-#include file ="People2.inc"
-->
</head>
<body>
<p><center><b><font size=+2>
Personal Address Book
</font></b></center>
<form name=frmPerson action=People4.asp method=post>
<input name=btnOrder value=Order type=submit>
<input name=btnNormal value=Normal type=submit>
</form>
<%
if Request.Form("btnOrder") <> "" Then
ShowPeople "SELECT * FROM Person ORDER BY Surname ASC"
Else
ShowPeople "Person"
End If
%>
</body>
</html>
Mark Dixon, SoCCE
SOFT 131
People.inc
<%
Const cs = “… …"
Sub ShowPeople(strQuery)
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open strQuery, cs
Do Until rs.EOF
Response.Write rs.Fields("Surname").Value
Response.Write "<br>"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
%>
People4.asp
Page 15

slides

Transcript slides

Directory