ASP-ADO

basesprocketData Management

Oct 31, 2013 (4 years and 12 days ago)

98 views

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

SQL

Oracle

ODBC

Access

SQL

Oracle

Access

SQL

Oracle

OLE DB

Providers

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”) & “<br>”


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

<html>

<body>


<%

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 & "<br />")


next


Response.Write("<br />")


rs.MoveNext

loop


rs.close

conn.close

%>


</body>

</html>

Put the data in a table with colors and titles

<html>

<body>

<%

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

%>

<table border="1" width="100%" bgcolor="#fff5ee">

<tr>

<%for each x in rs.Fields


response.write("<th align='left' bgcolor='#b0c4de'>" & 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>

Another example, make data on page sortable by user

<html>

<body>

<table border="1" width="100%">

<tr>

<td><a href="demo_sort2.asp?sort=companyname">Company</a></td>

<td><a href="demo_sort2.asp?sort=contactname">Contact Name</a></td>

</tr>

<%

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%>


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

Using the Recordset Object

<body>

<%

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("<p>This example returns the value of the first column in the first two records:</p>")

response.write(p(0,0))

response.write("<br>")

response.write(p(0,1))

response.write("<p>This example returns the value of the first three columns in the first record:</p>")

response.write(p(0,0))

response.write("<br>")

response.write(p(1,0))

response.write("<br>")

response.write(p(2,0))

rs.close

conn.close

%>

</body>

</html>

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

ADO.NET


Under .NET, ADO hasn’t changed much but the way you use it has


VB has changed extensively under .NET so we won’t address its use with
VB (developer community isn’t real happy about this)


the ADO API is pretty consistent across the MS supported .NET
languages


Consists of the namespaces and classes that manage accessing backend
databases.


five namespaces


the most common are:


Ststem.Data


System.Data.OleDB


System.Data.SqlClient


hundreds of classes


the .NET Data Provider

DataReader

SelectCommand

UpdateCommand

InmsertCommand

DeleteCommand

Transaction

Parameters

Connection

Command

DB

.NET Data Provider

.NET DataSet

ConstraintCollection

DataColumnCollection

DataRowCollection

DataTable

DataTable

DataTableCollection

DataSet

DataRelationshipCollection

System.Data Namespace


Consists mainly of classes that make up
ADO.NET


Allows a developer to build components
that manage data from data sources

System.Data.OleDb Namespace


Ole DB .Net Data Provider


Designed to be a replacement for ODBC


Used for accessing different kinds of data
stores uniformly


non
-
relational databases


Dbase, Foxpro, Access


Spreadsheets

System.Data.SqlClient Namespace


.NET data provider for MS SQL Server


classes bypass OLE DB to interact directly
with SQL Server


increase performance for SQL Server access

MySql.Data.MySqlClient


namespace containing classes for accessing
MySQL databases


provided by MySQL organization

IBM.Data.DB2 namespace


namespace for using IBM DB2 databases
with .NET


Provided by IBM with the DB2 product

Oracle.DataAccess.Client


namespace used for accessing Oracle
databases from .NET


provided by Oracle with Oracle product