1 / 20

21 – Web applications: Writing data to Databases using ASP

21 – 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:

huey
Download Presentation

21 – Web applications: Writing data to Databases using ASP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 21 – Web applications:Writing data to Databasesusing ASP

  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

  3. Searching for Data • Recordset methods • Find: searches for the next record to match given criteria string: • e.g. "Name = 'Smith' "( " are for VB string)( ' are for database string)

  4. Example: Person v1 (Specification) • User requirement: • Display person’s details from database online, and be able to move to next and previous person (record) • Problem: • Can’t use .MoveNext and .MovePrev recordset methods:record set does not persist between pages

  5. Example: Person v1 (html) Self post <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 action="Person.aspx" method=post> <input name="btnPrev" type="submit" value="Previous"> <input name="btnNext" type="submit" value="Next"> </form> </body> </html>

  6. Example: Person v1 (ASP) Button value empty unless button was pressed Use session variable to record current position in db <% Const adOpenDynamic = 3 Const cs = "…" Dim rs rs = CreateObject("ADODB.Recordset") rs.Open("Person", cs, adOpenDynamic) If Session("curID") <> "" Then rs.Find("[ID] = " & Session("curID")) If Request.Form("btnPrev") <> "" Then rs.MovePrevious() ElseIf Request.Form("btnNext") <> "" Then rs.MoveNext() End If End If Session("curID") = rs.Fields("ID").Value Response.Write(rs.Fields("Surname").Value & "<br>") Response.Write(rs.Fields("Forenames").Value) rs.Close() rs = Nothing %>

  7. Example: Person v2 (Specification) • User requirement: • Display person’s details from database online, and be able to move to next and previous person (record) • Change surname and save to database

  8. 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)

  9. Database Permissions 2 • Click Security tab • Click Add button

  10. Database Permissions 3 • Select Internet Guest Account IUSR_ … ClickAddbuttonClickOKbutton

  11. Database Permissions 4 • Select InternetGuest Account • Ensure writeaccess is on

  12. Writing data to a database • create recordset • open recordset • dynamic cursor (3), pessimistic locking (3) • 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 fieldsrs.Fields("Surname").Value = "Fred"

  13. Changing Data • Recordset methods • AddNew: inserts a new record and makes it current • Update: sends changes back to DB • Delete: deletes currently selected record

  14. Example: Person v2 (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.aspx" 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.aspx

  15. Example: Person v2 (ASP) <% Const cs = "…" Dim rs Dim Surname 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() rs = Nothing %>

  16. Example: Phone Book (database) Person

  17. Example: Phone Book (screen)

  18. Tutorial Exercise: Person v1 • Task 1: Get the Person (v1) example from the lecture working. • Task 2: Modify your code, so that all fields are displayed. • Task 3: Modify your code, so that it does not generate an error when the user goes past the end or beginning of the recordset.

  19. Tutorial Exercise: Person v2 • Task 1: Get the Person (v2) example from the lecture working. • Task 2: Modify your code, so that a line of text is displayed confirming that data has been saved.

  20. Tutorial Example: Phone Book • Task 1: Get the Phone Book example from the lecture working. • Task 2: Add a button on your page that jumps to another page, which allows a new record to be added. • Task 3: Modify your code, so that each line (record) has a link leading to another page that allows the data to be edited. • Task 4: Modify your code, so that each line (record) has a link leading to another page that allows the record to be deleted.

More Related