1 / 21

11 – Persistent data storage: relational databases and ADO

11 – Persistent data storage: relational databases and ADO. Session Aims & Objectives. Aims To introduce the fundamental ideas involved in persistent data storage and relational databases Objectives, by end of this week’s sessions, you should be able to: create a relational database table

kenley
Download Presentation

11 – Persistent data storage: relational databases and ADO

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. 11 – Persistent data storage: relational databases and ADO

  2. Session Aims & Objectives • Aims • To introduce the fundamental ideas involved in persistent data storage and relational databases • Objectives,by end of this week’s sessions, you should be able to: • create a relational database table • create a web page (ASP) that displays data from a single table in a database

  3. Persistent Data Storage • So far • all programs lose data when closed • Not realistic • typically data stored to persistent storage device (e.g. hard disk, key drive, floppy disk, CD-RW) • Use either • flat files • database (relational, or object oriented)

  4. Example: People (Specification) • User requirement: • Display list of people from database online • How: • Combine our knowledge of: • ASP (active server pages) • ADO (activeX data objects)

  5. Example: People (Database) Field Record • Information organised into • tables (e.g. person) • fields (e.g. phone) • records (e.g. 1 Dixon Mark 01752 232556 …) Person

  6. Example: Music (Database) • How many fields? • How many records? Track

  7. Database Management Systems • DBMS provides facilities for: • creating and changing databases • add/remove records • add/remove fields • add/remove data • For example: • Microsoft Access • dBase • Borland Paradox • MySQL • Microsoft SQL Server • Oracle home/small business large scale

  8. MS Access Music database

  9. ActiveX Data Objects (what & why) • ActiveX Data Objects (ADO) • common database interface • allow you to write code for any DBMS MS Access VB orVB Script code MS SQL Server ADO … DB front end …

  10. ADO Record Set Object • Used to interact with tables • Properties • BOF: true if at start of record set (before first record) • EOF: true if at end of record set (after last record) • Fields: used to get and set data values • Methods • Open: used to open record set • MoveFirst: moves focus to first record • MovePrevious: moves focus to previous record • MoveNext: moves focus to next record • MoveLast: moves focus to last record • Close: closes record set

  11. Using Record Sets Connect string – identify database Open record set with table Move to next record Close record set <% Const cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Music.mdb; Persist Security Info=False" Dim rs Set rs = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF … rs.MoveNext Loop rs.Close Set rs = Nothing %>

  12. UDL files • Generate connection strings • Right click on desktop • Select New, Text Document • Rename to *.UDL (Yes to warning message) • Double click • Select provider • Click Next • Select or enter DB name • Click Test Connection button • Click OK • Open with Notepad, cut & paste text

  13. Example: People rs 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 = "Provider=…;Data Source=D:\People.mdb; " Dim rs Set rs = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value Response.Write "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html>

  14. Example: People (recordset 1) <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 = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value Response.Write "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html> rs Dixon

  15. Example: People (recordset 2) <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 = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value Response.Write "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html> rs Dixon Smith

  16. Example: People (recordset 3) <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 = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value Response.Write "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html> rs Dixon Smith Jones

  17. Example: People (recordset 4) <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 = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value Response.Write "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html> rs Dixon Smith Jones Bloggs

  18. Example: People (recordset 5) <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 = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value Response.Write "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html> rs Dixon Smith Jones Bloggs Anderson

  19. Example: People (recordset 6) <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 = CreateObject("ADODB.Recordset") rs.Open "Person", cs Do Until rs.EOF Response.Write rs.Fields("Surname").Value Response.Write "<br>" rs.MoveNext Loop rs.Close Set rs = Nothing %> </body> </html> People.asp rs Dixon Smith Jones Bloggs Anderson

  20. Tutorial Exercise: People • Task 1: Create your own People database: • Open MS Access • Create a new database file • Create a new table • Create fields • Enter data • Task 2: Create the asp page (as per the lecture) to display data from the database. • Task 3: Modify your page so that it displays phone number as well as the person's name. • Task 4: Modify your page so that the user can type a letter, and only names starting with that letter are displayed.

  21. Tutorial Exercise: Music • Task 1: Create your own Music Database. • Task 2: Create an asp page to display data from this database. • Task 3: Modify your page so that the user can type the name of an artist, and only tracks by that artist are displayed • Task 4: Make your page case in-sensitive (i.e. UPPER or lower case makes no difference)

More Related