20 – Web applications: Writing data to Databases using ASP

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

2. Questions: HTML in VB • Are these correct (assume variables and fields exist)? f = f + rs.Fields("Description").value h = h + rs.Fields("<br />Name").value a = "<p>" + a"</p>" html = html + <img src=face.gif /> h = "<table>" + h + "</table>"     

3. Questions: Databases 3 • How many primary keys? • How many foreign keys? 2

4. 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

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

6. 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: • record set does not persist between pages • Difficult to use .MoveNext and .MovePrev recordset methods:

7. Example: Person v1 Sub Page_Load() Const cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\People.mdb;" Dim rs As Object Dim s As String Dim id As String rs = CreateObject("ADODB.Recordset") rs.Open("Person", cs, 3) id = CStr(Session("curID")) If id > "" Then rs.Find("[ID] = " + id) If Request.Form("btnPrev") > "" Then rs.MovePrevious() ElseIf Request.Form("btnNext") > "" Then rs.MoveNext() End If End If Session("curID") = rs.Fields("ID").Value s = rs.Fields("Surname").Value + "<br>" parData.InnerHtml = s rs.Close() rs = Nothing End Sub Find last position in db (using id from session variable) Use session variable to record current position in db

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

9. Database Permissions 1 • Windows Explorer • Tools • Folder Options • View Tab • Need to turn'simple file sharing' off(as this disables the security tab in file properties)

10. Database Permissions 2 • 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 XP)

11. Database Permissions 3 • Click Security tab • Click Add button

12. Database Permissions 4 • Click Advanced button

13. Database Permissions 5 • Select Internet Guest Account IUSR_ … ClickFind buttonClickuserClickOK button

14. Database Permissions 6 • Select InternetGuest Account • Ensure writeaccess is on • Repeat forASPNET account

15. Changing Data • Recordset methods • AddNew: inserts a new record and makes it current • rd.Fields("FieldName").value = "Data" • Update: sends changes back to DB • Delete: deletes currently selected record

16. 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"

17. Example: Person v2 Sub Page_Load() Const cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\People.mdb;" Dim rs As Object Dim id As String rs = CreateObject("ADODB.Recordset") rs.Open("Person", cs, 3, 3) id = CStr(Session("curID")) If id > "" Then rs.Find("[ID] = " + id) If Request.Form("btnPrev") > "" Then rs.MovePrevious() ElseIf Request.Form("btnNext") > "" Then rs.MoveNext() ElseIf Request.Form("btnSave") > "" Then rs.Fields("Surname").Value = txtSurname.Value rs.Update() End If End If Session("curID") = rs.Fields("ID").Value txtSurname.Value = rs.Fields("Surname").Value rs.Close() rs = Nothing End Sub

18. Example: Person v3

19. Tutorial Exercise: Person v1 • LEARNING OBJECTIVE:use session variable to store idpractice problem solving skills • Task 1: Get the Person (v1) example from the lecture working. • Task 2: Modify your code, so that all fields are displayed (use a table). • 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. Hint: You can't stop it 'falling off' the end of the recordset. But you can detect it using EOF and move back.

20. Tutorial Exercise: Person v2 & v3 • 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. • Task 3: Modify your code, so that an add button is included, which allows a new record to be added. • Task 4: Modify your code, so that a delete button is included, which allows the current record to be deleted.