23 – Web applications: Writing data to Databases using ASP

23 – Web applications:Writing data to Databasesusing ASP

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>"     

Questions: Databases 3 • How many primary keys? • How many foreign keys? 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

Example: Person v1 (Specification) • User requirement: • Display people's details from database online • need 2 pages: jones sally smith jones dixon person's details list of people

Example: PeopleList.aspx v1 <script language="VB" runat="server"> Sub Page_Load() Const cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\People.mdb;" Dim rs As Object Dim s As String rs = CreateObject("ADODB.Recordset") rs.Open("Person", cs) s = "" Do Until rs.EOF() s = s & rs.Fields("Surname").Value & "<br />" rs.MoveNext() Loop parData.InnerHtml = s rs.Close() rs = Nothing End Sub </script> <html> <head><title></title></head> <body> <p id="parData" runat="server"></p> </body> </html>

Example: PeopleList.aspx v2 <script language="VB" runat="server"> Sub Page_Load() Const cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\People.mdb;" Dim rs As Object Dim s As String rs = CreateObject("ADODB.Recordset") rs.Open("Person", cs) s = "" Do Until rs.EOF() s = s & "<a href='Person.aspx?id=" & rs.Fields("ID").Value & "'>" s = s & rs.Fields("Surname").Value & "</a><br />" rs.MoveNext() Loop parData.InnerHtml = s rs.Close() rs = Nothing End Sub </script> <html> <head><title></title></head> <body> <p id="parData" runat="server"></p> </body> </html> now links

Example: Person.aspx v2 <script language="VB" runat="server"> Sub Page_Load() Const cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\People.mdb;" Dim sql As String Dim rs As Object Dim s As String sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id") rs = CreateObject("ADODB.Recordset") rs.Open(sql, cs) s = "" If Not rs.EOF() Then txtSurname.Value = rs.Fields("Surname").Value End If rs.Close() rs = Nothing End Sub </script> <html> <head><title></title></head> <body> <a href="PeopleList.aspx">Back to People List</a><br /> <form runat="server"> Surname: <input id="txtSurname" runat="server" /><br /> <input id="btnSave" type="submit" value="Save" runat="server" /> </form> </body> </html> reads querystring (from previous page) displays data for selected record only

Example: Person v2 (Specification) • User requirement: • Display person’s details from database online • Change surname and save to database

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)

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)

Database Permissions 3 • Click Security tab • Click Add button

Database Permissions 4 • Click Advanced button

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

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

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

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"

Example: Person.aspx v3 <script language="VB" runat="server"> Sub Page_Load() Const cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\People.mdb;" Dim sql As String Dim rs As Object Dim s As String sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id") rs = CreateObject("ADODB.Recordset") rs.Open(sql, cs, 3, 3) s = "" If Not rs.EOF() Then If Request.Form("btnSave") > "" Then rs.Fields("Surname").Value = txtSurname.Value rs.Update() End If txtSurname.Value = rs.Fields("Surname").Value End If rs.Close() rs = Nothing End Sub </script> <html> <head><title></title></head> <body> <a href="PeopleList.aspx">Back to People List</a><br /> <form runat="server"> Surname: <input id="txtSurname" runat="server" /><br /> <input id="btnSave" type="submit" value="Save" runat="server" /> </form> </body> </html> Save button works now

Tutorial Exercise: Person • Task 1: Get the Person (v1) example from the lecture working. • Task 2: Modify your code, so that forename is displayed as well as surname (use a table). • Task 3: Get the Person (v2 and v3) example from the lecture working. • Task 3: Modify your code, so that a line of text is displayed confirming that data has been saved. • Task 4: Modify your code, so that an add button is included, which allows a new record to be added. • Task 5: Modify your code, so that a delete button is included, which allows the current record to be deleted.

23 – Web applications: Writing data to Databases using ASP