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.
