ASP-ADO CS-422 Dick Steflik
ActiveX Data Objects (ADO) •
Does for Microsoft data sources what ODBC did for databases – similar in concept to the combination JDBC and JNDI (and then some)
•
The process of using ADO involves the four step process: – – – –
Connect to the database Define the data you want Manipulate the data Display the data
ADO Software Architecture VC++
VB
Script
Java
ADO
OLE DB
RDBMS
E-mail
Directory Services
ADO, ODBC and OLE DB • • •
ADO is the strategic way for using ODBC data sources on the Windows platform ODBC was the MS way of connecting to and using databases on the Windows platform. OLE DB extends ODBC by using the concept of service providers to add the ability to connect to other non-database data sources (MS Active Directory (LDAP), e-mail servers (Exchange)) – – – – – –
ODBC Data Sources; provider = MSDASQL MS Index Server; provider = MSIDXS MS Active Directory Server - provider = ADSD300Object MS Jet databases - provider = Microsoft.Jet.OLEDB.3.51 MS SQL Server - provider = SQLOLEDB Oracle Databases - provider = MSDAORA
Providers and Drivers ADO
JET
SQL
Oracle
JET
Access
SQL
Oracle
OLE DB
ODBC
Access
SQL
SQL
Providers
Oracle
Oracle
ODBC Drivers
All About ADO • To learn more about goto: – http://www.w3schools.com/ado/default.asp
Creating a CreateObject •
Connection objects are created using the CreateObject() function. – The only parameter is a string indicating the object to create • object_library_name.object
Dim cn ‘create a variable to hold a reference to the object Set cn = CreateObject(“ADODB.Connection”)
ConnectionString • •
Once the ConnectionObject is created we need to give it a ConnectionString to logon to the datasource ConnectionString specifies the : – – – –
provider datasource name userid (optional) password (optional)
Dim cnn Dim str str = “Provider=MSDASQL; Data Source=mydatasource; User Id=;Password=“ cnn.ConnectionString = str
Open() and Close() • •
Once the connection has been established the Connection must be opened before using it Likewise it should be closed when done. Dim cnn Dim str Set cnn = CreateObject(‘ADODB.Connection’) str = “Provider=MSDASQL.1,Data Source=mydatasource;” str = str & User TD=myuserid;Password=mypass” cnn.ConnectionString = str cnn.Open ….. cnn.Close
Recordset • •
Record set objects are the recipients of the result of a SQL query create the Connection object the create a recordset object and associate them Dim cnn Dim rs set cnn = CreateObject(“ADODB.Connection”) set rs = CreateObject(“ADODB.Recordset”) ‘ assocoate the record set with the connection rs.ActiveConnection = cnn
To use the Recordset, Open it
‘from previous code... Dim SQL SQL = “SELECT…..FROM…..” rs.Open SQL …you can abbreviate this by doing the association at the same time as the Open
rs.Open SQL, cnn … this will eliminate the need for the rs.ActiveConnection = cnn statement
Getting the data out •
•
The record set is a collection of rows, each row containing the data in the order that it was asked for in the SQL statement, each column in the row can be accessed by its column name for: SELECT CourseName from CourseTable
Dim string While Not rs.EOF string = string & rs(“CourseName”) & “
” rs.MoveNext Wend
Getting data in • •
To get data into an ADO data source use the ADO command object. Use this for the SQL – insert – update – and delete statements
Dim SQL Dim Cmd Set cmd = CreateObject(“ADODB.Command”) ‘create a command object SQL = “INSERT into …..” cmd.CommandText = SQL ‘set the CommandTexy property of the CommandObject cmd.ActiveConnection = cnn cmd.Execute
Display records from a database in an HTML Page <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("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 & "
") next Response.Write("
") rs.MoveNext loop rs.close conn.close %>
Put the data in a table with colors and titles <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("northwind.mdb")) set rs = Server.CreateObject("ADODB.recordset") sql="SELECT Companyname, Contactname FROM Customers" rs.Open sql, conn %>
<%for each x in rs.Fields response.write("| " & x.name & " | ") next%>
<%do until rs.EOF%> <%for each x in rs.Fields%> | <%Response.Write(x.value)%> | <%next rs.MoveNext%>
<%loop rs.close conn.close %>
Another example, make data on page sortable by user
| Company | Contact Name |
<% dim conn,rs,sort sort="companyname" if Request.QueryString("sort")<>"" then sort=Request.QueryString("sort") end if set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("northwind.mdb")) set rs=Server.CreateObject("ADODB.recordset") rs.Open "SELECT Companyname,Contactname FROM Customers ORDER BY " & sort,conn do until rs.EOF%> <%for each x in rs.Fields%> | <%Response.Write(x.value)%> | <%next rs.MoveNext%>
<%loop rs.close conn.close %>
Using the Recordset Object
This example returns the value of the first column in the first two records: ALFKI ANTON This example returns the value of the first three columns in the first record: ALFKI Alfreds Futterkiste Maria Anders
<% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open(Server.Mappath("northwind.mdb")) set rs = Server.CreateObject("ADODB.recordset") rs.Open "Select * from Customers", conn 'The first number indicates how many records to copy 'The second number indicates what recordnumber to start on p=rs.GetRows(2,0) response.write("
This example returns the value of the first column in the first two records:
") response.write(p(0,0)) response.write("
") response.write(p(0,1)) response.write("
This example returns the value of the first three columns in the first record:
") response.write(p(0,0)) response.write("
") response.write(p(1,0)) response.write("
") response.write(p(2,0)) rs.close conn.close %>