Download
1 / 14
140 likes | 279 Views
22 – Web applications: Writing data to Databases using ASP. 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:
E N D
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
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)
Database Permissions 2 • Click Security tab • Click Add button
Database Permissions 3 • Select Internet Guest Account IUSR_ … ClickAddbuttonClickOKbutton
Database Permissions 4 • Select InternetGuest Account • Ensure writeaccess is on
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"
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). %> <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> PersonEdit.asp
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 %>
People Database (with Hobbies) Person Hobby
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
SQL: Joining tables ID Surname Forenames Phone email HobbyID Description PersonID 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 1 Archery 1 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 2 Herpetology 1 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 3 Music 1 1 Dixon Mark 01752 232556 mark.dixon@plymouth.ac.uk 6 Hitting people with swords 1 2 Smith John 01752 111111 john.smith@john.smith.ac.uk 4 Football 2 2 Smith John 01752 111111 john.smith@john.smith.ac.uk 5 Rugby 2 SELECT *FROM [Person], [Hobby]WHERE [Person].[ID] = [Hobby].[PersonID]; Two tables Matching records
SQL: DISTINCT records ID Surname 1 Dixon 2 Smith SELECT DISTINCT [ID], [Surname]FROM [Person], [Hobby]WHERE [Person].[ID] = [Hobby].[PersonID];
Query Strings Query String • Data can be added to end of URL:http://localhost/page.asp?Surname=Bob • ASP code can use this data: • Request.QueryString("Surname") • would return the value "Bob" • Form method=get • data automatically added to query string
