Download
1 / 19
190 likes | 297 Vues
This guide introduces fundamental concepts for using server-side code with ASP and VB to store and manipulate data in databases. By the end of the session, you'll be able to create an ASP web page that allows users to view and manage a database of people. The content includes examples of connecting to an Access database, querying data, dynamically generating HTML content, and implementing CRUD operations (Create, Read, Update, Delete) using ADO. Ensure to grant the necessary database permissions for write access.
E N D
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.
