1 / 23

22 – Web applications: Writing data to Databases using ASP.Net

22 – Web applications: Writing data to Databases using ASP.Net. Questions: HTML in VB. Are these correct (assume variables and fields exist)? f = f + r(" Description ") h = h + r("<br /> Name ") a = "<p>" + a "</p>" html = html + <img src=face.gif /> h = " <table> " + h + "</table>".

tracen
Download Presentation

22 – Web applications: Writing data to Databases using ASP.Net

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. 22 – Web applications:Writing data to Databasesusing ASP.Net

  2. Questions: HTML in VB • Are these correct (assume variables and fields exist)? f = f + r("Description") h = h + r("<br />Name") 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. Advice • Don’t • put anything on desktop • Especially database

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

  7. Example: PeopleList.aspx v1 <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.ODBC" %> <script runat="server"> Sub Page_Load() Dim cs As String = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + _ "Dbq=" + Server.MapPath("People.accdb") + ";" Dim cn As New OdbcConnection(cs) Dim cmd As OdbcCommand Dim r As OdbcDataReader Dim s As String cmd = New OdbcCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & r("Surname") & "<br />" Loop cn.Close parData.InnerHtml = s End Sub </script> <html> <head><title></title></head> <body> <p id="parData" runat="server"></p> </body> </html>

  8. Example: PeopleList.aspx v2 <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.ODBC" %> <script runat="server"> Sub Page_Load() Dim cs As String = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + _ "Dbq=" + Server.MapPath("People.accdb") + ";" Dim cn As New OdbcConnection(cs) Dim cmd As OdbcCommand Dim r As OdbcDataReader Dim s As String cmd = New OdbcCommand("SELECT * FROM Person;", cn) cn.Open() r = cmd.ExecuteReader() s = "" Do While r.Read() s = s & "<a href='Person.aspx?id=" & r("ID") & "'>" s = s & r("Surname") & "</a><br />" Loop cn.Close parData.InnerHtml = s End Sub </script> <html> <head><title></title></head> <body> <p id="parData" runat="server"></p> </body> </html> now links

  9. Example: Person.aspx v2 <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.ODBC" %> <script runat="server"> Sub Page_Load() Dim cs As String = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + _ "Dbq=" + Server.MapPath("People.accdb") + ";" Dim sql As String Dim cn As New OdbcConnection(cs) Dim cmd As OdbcCommand Dim r As OdbcDataReader Dim s As String sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id") cmd = New OdbcCommand(sql, cn) cn.Open() r = cmd.ExecuteReader() s = "" If r.Read() Then txtSurname.Value = r("Surname") End If cn.Close() End Sub </script> <html> <head><title></title></head> <body> <a href="PeopleList2.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

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

  11. Changing Data • SQL • INSERT: inserts a new record INSERT INTO Person (Surname, Age) VALUES ('Smith', 21); • UPDATE: makes changes to specified record UPDATE Person Set Surname = 'Smith', Age = 21 WHERE id = 14; • DELETE: deletes specified record DELETE FROM Person WHERE id = 14

  12. WARNING!! • All changes permanent (no undo) • WHERE clause is CRITICAL DELETE FROM Person; Will delete ALL records in table

  13. Example: Person.aspx v3 (error) <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.ODBC" %> <script runat="server"> Dim cs As String = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + _ "Dbq=" + Server.MapPath("People.accdb") + ";" Dim cn As New OdbcConnection(cs) Sub Page_Load() Dim sql As String Dim cmd As OdbcCommand Dim r As OdbcDataReader sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id") cmd = New OdbcCommand(sql, cn) cn.Open() r = cmd.ExecuteReader() If r.Read() Then txtSurname.Value = r("Surname") End If cn.Close() End Sub Sub btnSave_Click(s As Object, e As EventArgs) Handles btnSave.ServerClick Dim cmd As OdbcCommand Dim sql As String sql = "UPDATE [Person] " + _ " SET [Surname] = '" + txtSurname.Value + "'" + _ " WHERE id = " & Request.QueryString("id") & ";" cmd = New OdbcCommand(sql, cn) cn.Open() cmd.ExecuteNonQuery() cn.Close End Sub </script> Save buttonexecutes SQL UPDATE PROBLEM: Page_Loadre-reads old surname first

  14. Example: Person.aspx v3b <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.ODBC" %> <script runat="server"> Dim cs As String = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + _ "Dbq=" + Server.MapPath("People.accdb") + ";" Dim cn As New OdbcConnection(cs) Sub Page_LoadComplete(s As Object, e As EventArgs) Dim sql As String Dim cmd As OdbcCommand Dim r As OdbcDataReader sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id") cmd = New OdbcCommand(sql, cn) cn.Open() r = cmd.ExecuteReader() If r.Read() Then txtSurname.Value = r("Surname") End If cn.Close() End Sub Sub btnSave_Click(s As Object, e As EventArgs) Handles btnSave.ServerClick Dim cmd As OdbcCommand Dim sql As String sql = "UPDATE [Person] " + _ " SET [Surname] = '" + txtSurname.Value + "'" + _ " WHERE id = " & Request.QueryString("id") & ";" cmd = New OdbcCommand(sql, cn) cn.Open() cmd.ExecuteNonQuery() cn.Close End Sub </script> Save buttonexecutes SQL UPDATE Fix: Use Page_LoadComplete

  15. Example: Person.aspx v3c Dim cs As String = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + _ "Dbq=" + Server.MapPath("People.accdb") + ";" Dim cn As New OdbcConnection(cs) Dim sql As String Sub Page_Load() cn.Open() End Sub Sub btnSave_Click(s As Object, e As EventArgs) Handles btnSave.ServerClick Dim cmd As OdbcCommand sql = "UPDATE [Person] " + _ " SET [Surname] = '" + txtSurname.Value + "'" + _ " WHERE id = " & Request.QueryString("id") & ";" cmd = New OdbcCommand(sql, cn) cmd.ExecuteNonQuery() End Sub Sub Page_LoadComplete(s As Object, e As EventArgs) Dim cmd As OdbcCommand Dim r As OdbcDataReader sql = "SELECT * FROM Person WHERE id=" & Request.QueryString("id") cmd = New OdbcCommand(sql, cn) r = cmd.ExecuteReader() If r.Read() Then txtSurname.Value = r("Surname") End If cn.Close() End Sub • Page_Load: first • Click events • Page_LoadComplete: last

  16. Database Permissions • Generally • Read: works by default • Write: requires permissions • Asp.Net pages run as user: • Visual Studio • Logged in user • IIS • ASP.Net Account • NETWORKSERVICE • IIS APPPOOL\DefaultAppPool

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

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

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

  20. Database Permissions 4 • Click Advanced button

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

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

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

More Related