ADO - Activex Data Object

ADO is a Microsoft technology, and stands for ActiveX Data Objects

ADO can be used to access databases from your ASP web pages.



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


*********************************************

The common way to access a database from inside an ASP page is to:

    Create an ADO connection to a database
    Open the database connection
    Create an ADO recordset
    Open the recordset
    Extract the data you need from the recordset
    Close the recordset
    Close the connection


********************************************


ADO Database Connection

1) Create a DSN-less Database Connection

The easiest way to connect to a database is to use a DSN-less connection. A DSN-less connection can be used against any Microsoft Access database on your web site.

2) If you have a database called "northwind.mdb" located in a web directory like "c:/webdata/", you can connect to the database with the following ASP code:

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

Note, from the example above, that you have to specify the Microsoft Access database driver (Provider) and the physical path to the database on your computer.


********************************************************


ADO Recordset

To be able to read database data, the data must first be loaded into a recordset.
Create an ADO Table Recordset

After an ADO Database Connection has been created, as demonstrated in the previous chapter, it is possible to create an ADO Recordset. 

Suppose we have a database named "Northwind", we can get access to the "Customers" table inside the database with the following lines:
<%
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
%>



*******************************************************************


objCS.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source ="&varPath&"\Reports.xls;Extended Properties =""Excel 8.0;HDR = YES;"";"



Provider = Microsoft.Jet.OLEDB.4.0; - Connect the database using DSN-less connection. It can be used any microsoft Access database.

Data Source ="&varPath&"\Reports.xls; = This is the path of Excel(Database)

Extended Properties =""Excel 8.0;HDR = YES;"";

1) HDR - yes mean The first column in excel treated as Column name. Not data . if HDR - No mean The first column in excel treated as data.

2) specify Excel 8.0 for XLS and Excel 12.0 for XLSX files, which means you would need to build connection string dynamically. You also need to specify correct extension for you files - if it is xlsx than you should put it instead of xlsx.

The connection string for XLSX files would be something like

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xlsx;Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES;""

and for XLS files

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xls;Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""


    
**********************************************************************

Recordset.Open Source, ActiveConnection, CursorType, LockType, Options


 CursorType

    Optional. A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. The default value is adOpenForwardOnly.
LockType

    Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. The default value is adLockReadOnly.
Options

    Optional. A Long value that indicates how the provider should evaluate the Source argument if it represents something other than a Command object, or that the Recordset should be restored from a file where it was previously saved. Can be one or more CommandTypeEnum or ExecuteOptionEnum values, which can be combined with a bitwise OR operator.