ADO Overview

ActiveX™ Data Objects (ADO) enables you to write a client application to access and manipulate data in a database server through a provider using commands in that providers language (typically SQL). ADO’s primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. This help file is for ADODB, an implementation of ADO optimized for use with Microsoft OLE DB providers, including the Microsoft ODBC Provider for OLE DB.

In ADO, the Recordset object is the main interface to data. An example of the minimal Microsoft® Visual Basic® Scripting Edition code to generate a Recordset from an ODBC data source is as follows:

set rstMain = CreateObject("ADODB.Recordset")
rstMain.Open "SELECT * FROM authors", _
	"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

This generates a forward-only, read-only Recordset object. A slightly more functional Recordset can be generated as follows:

set rstMain = CreateObject("ADODB.Recordset")
rstMain.Open "SELECT * FROM authors", _
	"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers",
	adOpenKeyset, adLockBatchOptimistic

This creates a fully scrollable and batch-updatable Recordset.

To Provide continuity between record set operations, a Connection object can be opened and used as a single sesson to perform many operations.

'"Shared connection returns ID of inserted record."
'This could be done as a stored procedure with much greater effeciency
set adoConn = CreateObject("ADODB.Connection")
adoConn.Open "DRIVER={SQL Server};SERVER={server};WSID=workstation;",_
	"UID=userid;PWD=password;DATABASE=database"
set adoRS = CreateObject("ADODB.Recordset")
set adoRStemp = CreateObject("ADODB.Recordset")
sSQL = "SELECT * FROM authors"
adoRS.Open sSQL, adoConn, 0, 3, 1
if adoRS.State<>1 then err.raise 1,"not open:"&sSQL
while Not adoRS.EOF
	response.write "<BR>"&adoRS("name")
	if left(adoRS("name"),5) = "James" then
		adoConn.BeginTrans
		sSQL = "INSERT INTO namedJames (authorID, Comment)"_
			" VALUES ("&adoRS("id")&", 'new');"
		adoConn.Execute sSQL
		adoRStemp.Open "SELECT @@IDENTITY;", adoConn,0,3,1
		response.write "Insert record ID:"&adoRStemp(0).value
'the ID is sure to be the one of the last insert since everthing
'shares the same connection. Watch out for triggered prodedures 
'at the server causing @@IDENTITY to be that of a different operation
		adoRStemp.Close
'other operations can be done using adoConn at this point
' and if they fail, the entire transaction can still be 
' rolled back
		if 0=err then 
			adoConn.CommitTrans
		else
			adoConn.RollbackTrans
			response.write "<BR><B>"&err.description&"</B>"
			end if

	adoRS.MoveNext
	wEnd

Note For applications that use Visual Basic Scripting Edition (for example, Microsoft® Active Server Pages), you must include the Adovbs.inc file in your code in order to call ADO constants by name. (Use Adojavas.inc for Microsoft JScript™.) In your code, you should always refer to constants by name rather than by value since the values may change from one version to the next.

In ADO, the object hierarchy is de-emphasized. Unlike Data Access Objects (DAO) or Remote Data Objects (RDO), you no longer have to navigate through a hierarchy to create objects because most ADO objects can be independently created. This allows you to create and track only the objects you need. This model also results in fewer ADO objects and thus a smaller working set.

ADO supports key features for building client/server and web-based applications, including the following:

· Independently-created objects

· Batch updating

· Support for stored procedures with in/out parameters and return values

· Different cursor types, including the potential for support of back-end-specific cursors

· Advanced recordset cache management

· Support for limits on number of returned rows and other query goals

· Support for multiple recordsets returned from stored procedures or batch statements

· Free-threaded objects for efficient web server applications

Note While ADODB supports these features, the underlying providers and drivers called by ADODB may not. Consult the documentation for the underlying providers and drivers to determine what functionality they support.

For the latest information on ADO, visit the Microsoft ADO web page.

For an example of how to use ADO, see the AdventureWorks sample application available with Microsoft Active Server Pages. For more information on OLE DB providers including the ODBC Provider for OLE DB, see the documentation for the Microsoft® OLE DB SDK or visit the Microsoft OLE DB web page.

See Also

ADO Object Model

See also:

• http://www.kamath.com/tutorials/tut007_identity.asp different methods of returning identity after inserting a new record.+
• http://skyscraper.fortunecity.com/bit/965/asp/asparticle/aspdbch5.html
• http://www.va.gov/oirm/training/asp/aspclass.asp

  .