Download
1 / 18
180 likes | 285 Views
CIS 375—Web App Dev II. ADO I. Introduction. ADO (________ Data Objects) is a Microsoft technology for accessing data in a database. ADO is automatically installed with _____. The common way to access a database from within an ASP page is to: Create an ADO ___________ to a database
E N D
CIS 375—Web App Dev II ADO I
Introduction • ADO (________ Data Objects) is a Microsoft technology for accessing data in a database. • ADO is automatically installed with _____. • The common way to access a database from within an ASP page is to: • Create an ADO ___________ to a database • Open the database connection • Create an ADO __________ • Open the recordset • Extract the data you need from the recordset • Close the recordset • Close the connection
Database Connections • Before a DB can be accessed from a web page, a DB connection must be established. • The easiest way to connect to a DB is to use a DSN-_____ connection. • A DSN-less connection can be used against any _______________ DB on your web site.
Create a DSN for an ODBC DB • Note that this configuration has to be done on the computer where your web site is located. • Open the ODBC icon in your Control Panel. • Choose the System DSN tab. • Click on Add in the System DSN tab. • Select the Microsoft Access Driver. Click Finish. • In the next screen, click Select to locate the database. • Give the database a Data Source Name (DSN). • Click OK. • If your web site is located on a remote server, you must have _________ access to that server, or ask your web host to do this for you.
Create/Open a DB Connection • For an Access DB called “hr.mdb" in a folder called “fpdb” with your ASP file in the _____ directory, this code creates and opens a DSN-less ADO connection. <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath(“fpdb/hr.mdb")) …%> • If you have an ODBC DB with a _____ called “hr," connect to the DB with the following ASP code: <% set conn=Server.CreateObject("ADODB.Connection") conn.Open “hr" …%>
Create/Open a Recordset • To be able to read database data, the data must first be loaded into a _________. • If you opened the DB connection with the first set of code in the previous slide, you can access the “employee" table by adding the following code: set rs=Server.CreateObject("ADODB.recordset") rs.Open "employee", conn • Or you could use _____ as follows: set rs=Server.CreateObject("ADODB.recordset") rs.Open "Select * from employee", conn • This stores all table records in the recordset.
Display Data • To extract data from a recordset and then close the recordset and connection, add the following code: do until rs.EOF for each x in rs.Fields ‘ “x” refers to a field Response.Write(x.name) ‘ field name Response.Write(" = ") Response.Write(x.value & "<br />") ‘ field value next Response.Write("<br />") rs.MoveNext ‘ move to next record in table loop rs.close conn.close
File named display_employees.asp <html> <body> <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("fpdb/hr.mdb")) set rs=Server.CreateObject("ADODB.recordset") sql="SELECT * FROM employee" rs.Open sql, conn %> <table border="1" width="100%"> <tr> <% for each x in rs.Fields response.write("<th>" & x.name & "</th>") next %> </tr> <% do until rs.EOF %> <tr> <% for each x in rs.Fields %> <td> <% Response.Write(x.value) %> </td> <% next rs.MoveNext %> </tr> <% loop rs.close conn.close %> </table> </body> </html> Display Data in an HTML Table
First create a form for data input (form.htm): <html> <body> <form method="post" action="add.asp"> <table> <tr> <td>SSN:</td> <td><input name="ssn"></td> </tr> <tr> <td>Last Name:</td> <td><input name="lastname"></td> </tr> <tr> <td>First Name:</td> <td><input name="firstname"></td> </tr> </table> <br /><br /> <input type="submit" value="Add New"> <input type="reset" value="Cancel"> </form> </body> </html> Add Records I
Then create the ASP file to add records (add.asp): <html> <body> <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath(“fpdb/hr.mdb")) sql="INSERT INTO employee (ssn,lastname,firstname) VALUES " sql=sql & "('" & Request.Form("ssn") & "'," sql=sql & "'" & Request.Form(“lastname") & "'," sql=sql & "'" & Request.Form(“firstname") & "')" on error resume next conn.Execute sql,recaffected if err<>0 then Response.Write("No update permissions!") else Response.Write("<h3>" & recaffected & " record added</h3>") end if conn.close %> </body> </html> Add Records II
Update Records I: hr_listforupdate.asp • This file displays all records in a special way (continues on next slide): <html> <body> <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("../db/hr.mdb")) set rs=Server.CreateObject("ADODB.Recordset") rs.open "SELECT * FROM employee",conn %> <h2>List Database</h2> <table border="1" width="100%"> <tr> <% for each x in rs.Fields response.write("<th>" & ucase(x.name) & "</th>") next %> </tr>
hr_listforupdate.asp (continued) <% do until rs.EOF %> <tr> <form method="post" action="hr_update.asp"> <% for each x in rs.Fields if x.name="ssn" then%> <td> <input type="submit" name="ssn" value="<%=x.value%>"> </td> <%else%> <td> <%Response.Write(x.value)%> </td> <%end if next %> </form> <%rs.MoveNext%> </tr> <% loop conn.close %> </table> </body> </html>
Update Records II: hr_update.asp <html> <body> <h2>Update Record</h2> <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("fpdb/hr.mdb")) social=Request.Form("ssn") if Request.form("lastname")="" OR Request.form("firstname")="" then set rs=Server.CreateObject("ADODB.Recordset") rs.open "SELECT * FROM employee WHERE ssn ='" & social & "'", conn %> <form method="post" action="hr_update.asp"> <table> <%for each x in rs.Fields%> <tr> <td><%=x.name%></td> <td><input name="<%=x.name%>" value="<%=x.value%>" size="20"></td> <%next%> </tr> </table> <br /><br /> <input type="submit" value="Update record"> </form>
hr_update.asp (continued) <% else sql="UPDATE employee SET " sql=sql & "lastname='" & Request.Form("lastname") & "'," sql=sql & "firstname='" & Request.Form("firstname") & "'" sql=sql & " WHERE ssn='" & social & "'" on error resume next conn.Execute sql if err<>0 then response.write("No update permissions!") else response.write("Record " & social & " was updated!") end if end if conn.close %> </body> </html>
Delete Records I: hr_listfordelete.asp • This file is identical to hr_listforupdate.asp except for the following code: <form method="post“ action="hr_delete.asp"> • instead of: <form method="post“ action="hr_update.asp">
Delete Records II: hr_delete.asp • This file is identical to hr_update.asp except for the following code: sql="DELETE FROM employee" sql=sql & " WHERE ssn='" & social & "'" • instead of: sql="UPDATE employee SET " sql=sql & "lastname='" & _ Request.Form("lastname") & "'," sql=sql & "firstname='" & _ Request.Form("firstname") & "'" sql=sql & " WHERE ssn='" & _ social & "'"
ADO Demonstration • How to change the ________ rights of your Access database: • Open Windows Explorer, find the .mdb file. • Right-click on the .mdb file and select ___________. • Go to the _________ tab and set the access-rights here. • List, edit, update, and delete database records • Add a new record
Using Access & FrontPage • SMSU Computer Services has a web page describing how to create a database connection using MS Access and FrontPage software. • SMSU Computer Services also has a tutorial on how to set up a form on a web page and connect it to an Access database.
