Download
1 / 18
180 likes | 294 Vues
ADO (ActiveX Data Objects) is a Microsoft technology for accessing data in databases. This guide covers the essentials of ADO, including establishing database connections, creating recordsets, and extracting data on ASP web pages. You will learn how to set up DSN and DSN-less connections, open recordsets, and display data in HTML tables. Additionally, we provide a simple form for data input and demonstrate how to add records to a database using ASP code. Gain practical skills to enhance your web applications with database interactions.
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.
