During the network programming, Ado (ActiveX Data Object) programming is often indispensable, especially developing an e-commerce website. Since the ADO data object is told, then the ADO data object and its functions are briefly introduced. ADO data objects have seven independent objects, which are connected, recorded, recorded, command objects, parameter objects, properties, and Property, and Error Object (Error). See Appendix 1 for the function.
There are two ways to connect the database to connect to the database, which are ODBC and OLE DB mode, and the following describes an example of connecting to the database in both ways.
First: ODBC DSN-Less Connections
ODBC DRIVER FOR Access
ODBC Driver for DBASE
ODBC Driver for Excel
ODBC DRIVER for Mysql
ODBC Driver for Oracle
ODBC DRIVER for PARADOX
ODBC DRIVER for SQL Server
ODBC DRIVER for Sybase
ODBC Driver for Sybase SQL Anywhere
ODBC Driver for Text
ODBC Driver for Teradata
ODBC Driver for Visual FoxPro
Second: OLE DB DATA Provider Connections
OLE DB Provider for Active Directory Service
OLE DB Provider for DB2
Old DB Provider for Internet Publishing, INTERNET PUBLISHING
OLE DB Provider for Index Server
OLE DB Provider for Microsoft Jet
OLE DB Provider for ODBC Databases
OLE DB Provider for Oracle (from Microsoft)
OLE DB Provider for Oracle (from oracle)
OLE DB Provider for Simple Provider
OLE DB Provider for SQL Server
First, I will first use the ODBC DSN to access the database. To complete this work, you must first create a data source in the "Control Panel" - "ODBC Data Source", such as TestData_Resoure.
1)
By system data source
(SYSTEM DSN)
Connection
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "DSN = TestData_Resoure;" & _
"UID = admincount;" & _
PWD = Password;
Oconn.open straconn
%>
2)
By file data source
(File DSN)
Connection
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "fileDSN = c: /somepath/mydb.dsn;" & _ "uid = admincount;" & _
"Pwd = password;"
Oconn.open straconn
%>
3)
By connecting the pool
(DSN-Less)
Connection
(ODBC Driver for AS / 400)
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Client Access ODBC Driver (32-bit)};" & _
"System = myas400;" & _
"UID = myusername;" & _
"Pwd = mypassword;"
Oconn.open straconn
%>
two,
Use the database driver directly to access the database connection string.
1)
ODBC DRIVER FOR Access
● The standard is also a relatively common connection method
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft Access Driver (* .mdb)};" & _
"DBQ =" & Server.mappath ("Testdb.mdb"); & _
"UID = admincount;" & _
"Pwd = password;"
Oconn.open straconn
%>
● If it is a system database of a working group, then the connection string is as follows.
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft Access Driver (* .mdb)};" & _
"DBQ = C: /DataPath/testdb.mdb;" & _
"Systemdb = c: /dataPath/testdb.mdw;", _
"admin", ""
Oconn.open straconn
%>
● If the database (MDB) is shared on the network, then the connection string is as follows.
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft Access Driver (* .mdb)};" & _
"DBQ = // myserver / myshare / mypath / testdb.mdb;"
Oconn.open straconn
%>
2)
ODBC Driver for DBASE
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft DBase driver (* .dbf)};" & _ "driverid = 277;" & _
"DBQ = C: / filepath;"
Oconn.open straconn
%>
Note: One thing to note here is that in the SQL query statement, specify specified database file names, for example:
Ors.open "Select * from testdb.dbf", Oconn,, AdcmdText
3)
ODBC Driver for Excel
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft Excel Driver (* .xls)};" & _
Driverid = 790; "& _
"Dbq = c: /filepath/myexecl.xls;"
Oconn.open straconn
%>
4)
ODBC Driver for MySQL (via myodbc driver)
● Connect to the local database (LOCAL DATABASE)
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {mysql};" & _
"Server = Servername;" & _
Option = 16834; "& _
"Database = mydb;"
Oconn.open straconn
%>
● Connect the remote database (Remote Database)
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {mysql}; server = db1.database.com; port = 3306;" & _
"Option = 131072; stmt =; dataBase = mydb; uid = myusername; pwd = mypassword;"
Oconn.open straconn
%>
5)
ODBC Driver for Oracle
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {microsoft odbc for oracle};" & _
"Server = oracleserver.world;" & _
"UID = myusername;" & _
"Pwd = mypassword;"
Oconn.open straconn
%>
6) ODBC Driver for Paradox
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
Strconn = "driver = {Microsoft Paradox Driver (* .db)};" & _ "driverid = 538;" & _
"Fil = Paradox 5.x;" & _
"Defaultdir = c: / dbpath /;" & _
"DBQ = C: / dbpath /;" & _
"Collatingsequence = ascii;"
Oconn.open straconn
%>
7) ODBC Driver for SQL Server
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {SQL Server};" & _
"Server = myservername;" & _
"Database = MyDatabaseName;" & _
"UID = myusername;" & _
"Pwd = mypassword;"
Oconn.open straconn
%>
8)
ODBC Driver for Sybase (via Sybase System 11 ODBC Driver Driver)
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Sybase System 11};" & _
"SRVR = MyServerName;" & _
"UID = myusername;" & _
"Pwd = mypassword;"
Oconn.open straconn
%>
9)
ODBC Driver for Sybase SQL Anywhere
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "ODBC; driver = Sybase SQL Anywhere 5.0;" & _
"Defaultdir = c: / dbpath /;" & _
"Dbf = c: /sqlany50/mydb.db;" & _
"UID = myusername;" & _
"Pwd = mypassword;"
"DSN =" "" ";"
Oconn.open straconn
%>
10)
ODBC Driver for Teradata
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = teradata;" & _
"Dbcname = mydbcname;" & _
"Database = MyDatabaseName;" & _
"UID = myusername;" & _
"Pwd = mypassword;" Oconn.open Strconn
%>
11)
ODBC Driver for Text
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft Text driver (* .txt; * .csv)};" & _
"DBQ = C: / SomePath /;" & _
"Extensions = ASC, CSV, Tab, TXT;" & _
"Persist security info = false"
Oconn.open straconn
%>
12)
ODBC Driver for Visual FoxPro
● Use the database container connection method
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft Visual FoxPro Driver};" & _
"SourceType = DBC;" & _
"SOURCEDB = C: /SOMEPATH/Mysourcecedb.dbc;" & _
"EXclusive = NO;"
Oconn.open straconn
%>
● Do not use the database container connection mode (ie Free Table Directory mode)
<%
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {Microsoft Visual FoxPro Driver};" & _
SourceType = DBF; "& _
"SOURCEDB = C: / somepath / mysourcedbfolder;" & _
"EXclusive = NO;"
Oconn.open straconn
%>
Second, the following use OLE DB to connect to the database.
1)
OLE DB Provider for Active Directory Service
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = adsdsoObject;" & _
"User ID = myusername;" & _
"Password = mypassword;"
Oconn.open straconn
%>
2)
OLE DB Provider for DB2
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
Strconn = "provider = db2oledb;" &
"NetWork Transport Library = TCPIP;" & "NetWork Address = MyServer;" & _
Package Collection = Mypackage; &
Host CCSID = 1142
Initial Catalog = MyDB; &
"User ID = myusername;" & _
"Password = mypassword;"
Oconn.open straconn
%>
3)
OLE DB Provider for Index Server
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = msidxs;" & _
Data Source = MyCatalog; "
Oconn.open straconn
%>
4)
OLE DB Provider for Internet Publishing
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = msdaipp.dso;" & _
"Data Source = http: // mywebsite / mydir;" & _
"User ID = myusername;" & _
"Password = mypassword;"
Oconn.open straconn
%>
5)
OLE DB Provider for Microsoft Jet
● The standard is also a commonly used access method.
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = C: /SOMEPATH/Mydb.mdb;" & _
"User ID = admin;" & _
"Password =;"
Oconn.open straconn
%>
● If it is a system database of a workgroup, then the connection string is as follows.
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = C: /SOMEPATH/Mydb.mdb;" & _
"Jet OLEDB: System Database = mysystem.mdw;", _
"admin", ""
Oconn.open straconn
%>
Note: When using the 4.0 OLE DB Provider provider's driver, remember to convert MDB and MDW to the 4.0 database format. ● If the MDB is set a password, then connect the string as follows.
Oconn.open "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = C: /SOMEPATH/Mydb.mdb;" & _
"Jet OLEDB: Database Password = mydbpassword;", _
"admin", ""
● If the MDB is in the network and is shared, then the following connection string is used.
Oconn.open "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = // myserver / myshare / mypath / mydb.mdb;
● If you want to use a proprietary way to access the database, use the following connection string.
Oconn.mode = admodeshareExClusive
Oconn.open "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = C: /SOMEPATH/Mydb.mdb;" & _
"User ID = admin; password =;"
6)
Use the OLE DB Provider for Microsoft Jet to access the Excel electronic data table.
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = c: /somepath/myexcelspreadsheet.xls;" & _
"Extended Properties =" "Excel 8.0; HDR = YES;" ";"
Oconn.open straconn
%>
Description: The HDR = YES here is that the provider does not add the first row of data to the data set when the provider accesses the Excel electronic data; and when HDR = No means that the provider is just right when the provider accesses the Excel electronic data table. Contrary to the above.
7)
OLE DB Provider for ODBC Databases
● Access MS Access Database
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = msdasql;" & _
"Driver = {Microsoft Access Driver (* .mdb)};" & _
"DBQ = C: /SOMEPath/mydb.mdb;" & _
"UID = myusername;" & _
"Pwd = mypassword;"
Oconn.open straconn
%>
● Access MS SQL Server Database
<%
DIM OCONN, STRCONNSET OCONN = Server.createObject ("AdoDb.Connection")
StrConn = "provider = msdasql;" & _
Driver = {SQL Server}; "& _
"Server = myservername;" & _
"Database = MyDatabaseName;" & _
"UID = myusername;" & _
"Pwd = mypassword;"
Oconn.open straconn
%>
8)
OLE DB Provider for Oracle (from Microsoft)
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = msdara;" & _
"Data Source = Myoracledb;" & _
"User ID = myusername;" & _
"Password = mypassword;"
Oconn.open straconn
%>
9)
OLE DB Provider for Oracle (from oracle)
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = oraoledb.oracle;" & _
"Data Source = Myoracledb;" & _
"User ID = myusername;" & _
"Password = mypassword;"
Oconn.open straconn
%>
10)
OLE DB Provider for SQL Server
● Standard connection method
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = SQLOLEDB;" & _
"Data Source = MyServerName;" & _
"Initial Catalog = MyDatabaseName;" & _
"User ID = myusername;" & _
"Password = mypassword;"
Oconn.open straconn
%>
● Access to the IP address of the machine
<%
DIM OCONN, STRCONNNN
Set Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "provider = SQLOLEDB;" & _
"Data Source = xxx.xxx.xxx.xxx, 1433;" & _
"NetWork Library = DBMSSOCN;" & _
"Initial Catalog = MyDatabaseName;" & _ "User ID = myusername;" & _
"Password = mypassword;"
Oconn.open straconn
%>
Description: IP: xxx.xxx.xxx.xxx
SQLServer default port: 1433
Appendix 1:
ADO
Introduction to data objects and their function
ADO
Data object
Features
.Connection
Represents a unique conversation with a data source
.Command
With Command objects, you can perform stored procedures, SQL queries, and SQL statements with parameters. You can receive the Recordset object using the Command object.
.Recordset
Used to represent a database table.
.Error
This object contains all errors and warning messages. This object only has attribute values.
.Field
The Field object represents a column in the data set.
.Parameter
Parameter objects are used to provide the parameters required for the SQL query or stored procedure to be parameters, or return values from the stored procedure.
.Property
Represents the specific properties of the data provider.
(Full text)
