database connectivity what is ado. what is ado? ado is a microsoft technology ado stands for activex...

Database Connectivity

What is ADO

What is ADO?

• ADO is a Microsoft technology

• ADO stands for ActiveX Data Objects

• ADO is a Microsoft Active-X component

• ADO is automatically installed with Microsoft IIS

• ADO is a programming interface to access data in a database

Accessing a Database from an ASP Page

1. Create an ADO connection to a database 2. Open the database connection 3. Create an ADO recordset 4. Open the recordset 5. Extract the data you need from the

recordset 6. Close the recordset 7. Close the connection

1&2. ADO Database Connection

• Create a DSN-less Database Connection

• <%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/webdata/northwind.mdb"

%>

• Create an ODBC Database Connection• <%

set conn=Server.CreateObject("ADODB.Connection")conn.Open "northwind.mdb"%>

• An ODBC Connection to an MS Access Database

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

3&4. ADO Recordset

• To be able to read database data, the data must first be loaded into a recordset.

• Create an ADO Table Recordset• <%

set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/webdata/northwind.mdb"

set rs=Server.CreateObject("ADODB.recordset")rs.Open "Customers", conn%>

Create an ADO SQL Recordset


set rs=Server.CreateObject("ADODB.recordset")rs.Open "Select * from Customers“ , conn%>

Extract Data from the Recordset


set rs=Server.CreateObject("ADODB.recordset")rs.Open "Select * from Customers", conn

for each x in rs.fields response.write(x.name) response.write(" = ") response.write(x.value)next%>

Definitions

• The ADO Connection Object: The ADO Connection object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.

• The ADO Recordset Object :The ADO Recordset object is used to hold a set of records from a database table.

Operations

• List, Add, Update, and Delete Database Records

Display the Field Names and Field Values

• <html><body>

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/webdata/northwind.mdb"

set rs = Server.CreateObject("ADODB.recordset")rs.Open "SELECT * FROM Customers", conn

• do until rs.EOF for each x in rs.Fields Response.Write(x.name) Response.Write(" = ") Response.Write(x.value & "<br />") next Response.Write("<br />") rs.MoveNextloop

rs.closeconn.close%>

</body></html>

Add a Record to a Table in a Database

• <html><body>


sql="INSERT INTO customers (customerID,"sql=sql & "name,address)"sql=sql & " VALUES "sql=sql & "('" & Request.Form("custid") & "',"sql=sql & "'" & Request.Form("name") & "',"sql=sql & "'" & Request.Form("address") & "')"

on error resume nextconn.Execute sql, recaffectedif err<>0 then Response.Write("No update permissions!")else Response.Write("<h3>" & recaffected & " record added</h3>")end ifconn.close%>

</body></html>

Update a Record in a Table• <h2>Update Record</h2>


cid=Request.Form("customerID") • sql="UPDATE customers SET "

sql=sql & "name='" & Request.Form("name") & "'," sql=sql & "address='" & Request.Form("address") & "'," sql=sql & " WHERE customerID='" & cid & "'" on error resume next conn.Execute sql if err<>0 then response.write("No update permissions!") else response.write("Record " & cid & " was updated!") end ifend ifconn.close%>

</body></html>

Delete a Record in a Table• <html>

<body>

<h2>Delete Record</h2><%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/webdata/northwind.mdb"

cid=Request.Form("customerID")sql="DELETE FROM customers" sql=sql & " WHERE customerID='" & cid & "'" on error resume next conn.Execute sql if err<>0 then response.write("No update permissions!") else response.write("Record " & cid & " was deleted!") end ifend ifconn.close%>

</body></html>

Multiple Response.Write's• <html>

<body>


set rs = Server.CreateObject("ADODB.recordset")rs.Open "SELECT name, address FROM Customers", conn%>

• <table border="1" width="100%"><%do until rs.EOF%> <tr> <td><%Response.Write(rs.fields( “name"))%></td> <td><%Response.Write(rs.fields( “address"))%></td> </tr><%rs.MoveNextloop%></table>

<%rs.closeconn.closeset rs = Nothingset conn = Nothing%>

</body></html> For a large query, this can slow down the script processing time,

since many Response.Write commands must be processed by the server.

Alternative speed up way

• The solution is to have the entire string created, from <table> to </table>, and then output it - using Response.Write just once.

• str = rs.GetString(format,rows,coldel,rowdel,nullexpr)

• coldel - the HTML to use as a column-separator

• rowdel - the HTML to use as a row-separator

• nullexpr - the HTML to use if a column is NULL

• <html><body>


set rs = Server.CreateObject("ADODB.recordset")rs.Open "SELECT Companyname, Contactname FROM Customers", conn

str=rs.GetString(,,"</td><td>","</td></tr><tr><td>"," ")%>

• <table border="1" width="100%"> <tr> <td><%Response.Write(str)%></td> </tr></table>

<%rs.closeconn.closeset rs = Nothingset conn = Nothing%></body></html>

database connectivity what is ado. what is ado? ado is a microsoft technology ado stands for activex...

Documents