24 Web applications Writing data to Databases using

24 – Web applications: Writing data to Databases using ASP Mark Dixon, So. CCE 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, So. CCE 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, So. CCE 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, So. CCE SOFT 131 Page 4

Database Permissions 2 • Click Security tab • Click Add button Mark Dixon, So. CCE SOFT 131 Page 5

Database Permissions 3 • Select Internet Guest Account IUSR_ … Click Add button Click OK button Mark Dixon, So. CCE SOFT 131 Page 6

Database Permissions 4 • Select Internet Guest Account • Ensure write access is on Mark Dixon, So. CCE SOFT 131 Page 7

Writing data to a database • create recordset • open recordset – dynamic cursor, pessimistic locking • to add a record – use to Add. New method rs. Add. New • 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, So. CCE SOFT 131 Page 8

Example: Person Edit (html) <html> <head> Person. Edit. asp <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="frm. Person" action="Person. Edit. asp" method=post> Surname: <input name="txt. Surname" type="text" value="<%=Surname%>"> <input name="btn. Prev" type="submit" value="Previous"> <input name="btn. Save" type="submit" value="Save"> <input name="btn. Next" type="submit" value="Next"> </form> </body> </html> Mark Dixon, So. CCE SOFT 131 Page 9

Example: Person Edit (ASP) <% Const cs = "…" Dim rs Dim Surname Set rs = Create. Object("ADODB. Recordset") rs. Open "Person", cs, 3, 3 If Session("cur. ID") <> "" Then rs. Find "[ID] = " & Session("cur. ID") If Request. Form("btn. Prev") <> "" Then rs. Move. Previous Else. If Request. Form("btn. Next") <> "" Then rs. Move. Next Else. If Request. Form("btn. Save") <> "" Then rs. Fields("Surname") = Request. Form("txt. Surname") rs. Update End If Session("cur. ID") = rs. Fields("ID"). Value Surname = rs. Fields("Surname"). Value rs. Close Set rs = Nothing %> Mark Dixon, So. CCE 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, So. CCE 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 = Create. Object("ADODB. Recordset") rs. Open "Person", cs Do Until rs. EOF Response. Write rs. Fields("Surname"). Value Response. Write " " rs. Move. Next Loop rs. Close Set rs = Nothing %> </body> </html> Mark Dixon, So. CCE SOFT 131 Page 12

Example: People 2. asp <html> <head><title>Personal Address Book</title></head> <body> <p><center><b><font size=+2> Personal Address Book</font> </b></center>  </body> </html> People. inc <% Const cs = " … … " Dim rs Set rs = Create. Object("ADODB. Recordset") rs. Open "Person", cs Do Until rs. EOF Response. Write rs. Fields("Surname"). Valu Response. Write " " rs. Move. Next Loop rs. Close Set rs = Nothing %> Mark Dixon, So. CCE SOFT 131 Page 13

Example: Procedures People 3. 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> <% Show. People %> </body> </html> Mark Dixon, So. CCE <% Const cs = " … … " Sub Show. People() Dim rs Set rs = Create. Object("ADODB. Record rs. Open "Person", cs Do Until rs. EOF Response. Write rs. Fields("Surname") Response. Write " " rs. Move. Next Loop rs. Close Set rs = Nothing End Sub %> SOFT 131 Page 14

Example: Parameters People 4. asp People. inc <html> <head> <% <title>Personal Address Book</title> Const cs = “… …" <!-#include file ="People 2. inc" Sub Show. People(str. Query) --> Dim rs </head> Set rs = Create. Object("ADODB. Recordset") <body> rs. Open str. Query, cs <p><center><b><font size=+2> Personal Address Book Do Until rs. EOF </font></b></center> Response. Write rs. Fields("Surname"). Value <form name=frm. Person action=People 4. asp method=post> Response. Write " " <input name=btn. Order value=Order type=submit> rs. Move. Next <input name=btn. Normal value=Normal type=submit> Loop </form> <% rs. Close if Request. Form("btn. Order") <> "" Then Set rs = Nothing Show. People "SELECT * FROM Person ORDER BY Surname End. ASC" Sub Else %> Show. People "Person" End If %> </body> </html> People 4. asp Mark Dixon, So. CCE SOFT 131 Page 15