The string cui set connecting various databases through ADO

ado| Data | database | string

In the process of network programming, ADO (Activex Data Object) programming is often indispensable work, especially the development of e-commerce sites. Now that you're talking about ADO data objects, you can simply introduce ADO data objects and their functions. There are seven separate objects for ADO data objects, namely, connection objects (Connection), Recordset objects (recordsets), Domain objects (field), Command objects (commands), Parameter objects (Parameter), Attribute objects (property) and Error objects (errors). features see Appendix 1.

ADO Data objects are used to connect to a database in two ways, ODBC and OLE DB, respectively, and the following are examples of connecting to a database in both ways.

The first type: 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

The second type: OLE DB Data Provider connections

OLE DB Provider for Active Directory Service

OLE DB Provider for DB2

Old DB Provider for 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'll start with ODBC DSN to access the database, to complete this work, you must first through the Control Panel-Administrative Tools-ODBC data source to establish a data source name, for example, called Testdata_resoure.

1 connection through System data source (systems DSN)

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Dsn=testdata_resoure" & _

"Uid=adminaccount;" & _

"Pwd=password;

oConn.Open strconn

%>

2 connection via file data source (Files DSN)

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Filedsn=c:\somepath\mydb.dsn" & _

"Uid=adminaccount;" & _

"Pwd=password;"

oConn.Open strconn

%>

3 Connection through Connection pool (dsn-less) (ODBC Driver for as/400)

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={client Access ODBC Driver (32-bit)};" & _

"SYSTEM=MYAS400;" & _

"Uid=myusername;" & _

"Pwd=mypassword;"

oConn.Open strconn

%>

Second, using the database driver directly access the database connection string.

1) ODBC Driver for Access

The standard is also the more common connection method

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={microsoft Access Driver (*.mdb)};" & _

"Dbq=" &server.mappath ("Testdb.mdb"); & _

"Uid=adminaccount;" & _

"Pwd=password;"

oConn.Open strconn

%>

If it is a workgroup system database, then the connection string is as follows

<%

Dim Oconn,strconn

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 strconn

%>

If the database (MDB) is shared on the network, the connection string is as follows

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={microsoft Access Driver (*.mdb)};" & _

"Dbq=\\myserver\myshare\mypath\testdb.mdb;"

oConn.Open strconn

%>

2) ODBC Driver for DBASE

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={microsoft DBASE Driver (*.dbf)};" & _

"DRIVERID=277;" & _

"Dbq=c:\filepath;"

oConn.Open strconn

%>

Note: The point to be noted here is to specifically specify the database file name in the SQL query statement, for example:

oRS.Open "SELECT * from testdb.dbf", oconn,,, adCmdText

3 ODBC Driver for Excel

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={microsoft Excel Driver (*.xls)};" & _

"DRIVERID=790;" & _

"Dbq=c:\filepath\myexecl.xls;"

oConn.Open strconn

%>

4 ODBC Driver for MySQL (via MYODBC driver)

Connect to local Database

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Driver={mysql};" & _

"Server=servername;" & _

"option=16834;" & _

"DATABASE=MYDB;"

oConn.Open strconn

%>

Connecting to remote databases (databas)

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Driver={mysql}; server=db1.database.com; port=3306; "& _

"OPTION=131072; stmt=;D Atabase=mydb; Uid=myusername; Pwd=mypassword; "

oConn.Open strconn

%>

5 ODBC Driver for Oracle

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={microsoft ODBC for Oracle};" & _

"Server=oracleserver.world;" & _

"Uid=myusername;" & _

"Pwd=mypassword;"

oConn.Open strconn

%>

6) ODBC Driver for Paradox

<%

Dim Oconn,strconn

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 strconn

%>

7 ODBC Driver for SQL Server

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Driver={sql Server};" & _

"Server=myservername;" & _

"Database=mydatabasename;" & _

"Uid=myusername;" & _

"Pwd=mypassword;"

oConn.Open strconn

%>

8 ODBC Driver for Sybase (through Sybase System-one ODBC Driver driver)

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={sybase SYSTEM One};" & _

"Srvr=myservername;" & _

"Uid=myusername;" & _

"Pwd=mypassword;"

oConn.Open strconn

%>

9 ODBC Driver for Sybase SQL Anywhere

<%

Dim Oconn,strconn

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 strconn

%>

) ODBC Driver for Teradata

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=teradata" & _

"Dbcname=mydbcname;" & _

"Database=mydatabasename;" & _

"Uid=myusername;" & _

"Pwd=mypassword;"

oConn.Open strconn

%>

ODBC Driver for Text

<%

Dim Oconn,strconn

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 strconn

%>

ODBC Driver for Visual FoxPro

Using the Database Container (DB container) connection method

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={microsoft Visual FoxPro Driver};" & _

"SOURCETYPE=DBC;" & _

"SOURCEDB=C:\SOMEPATH\MYSOURCEDB.DBC;" & _

"Exclusive=no;"

oConn.Open strconn

%>

Do not use database Container (container) connection (that is, free Table directory method)

<%

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

strconn= "Driver={microsoft Visual FoxPro Driver};" & _

"SOURCETYPE=DBF;" & _

"Sourcedb=c:\somepath\mysourcedbfolder;" & _

"Exclusive=no;"

oConn.Open strconn

%>

Second, the following example of using OLE DB to connect to a database.

1 OLE DB Provider for Active Directory Service

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=adsdsoobject" & _

"User id=myusername;" & _

"Password=mypassword;"

oConn.Open strconn

%>

2 OLE DB Provider for DB2

<%

Dim Oconn,strconn

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 strconn

%>

3 OLE DB Provider for Index Server

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=msidxs" & _

"Data Source=mycatalog;"

oConn.Open strconn

%>

4 OLE DB Provider for Internet Publishing

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=msdaipp. DSO; "& _

"Data Source=http://mywebsite/mydir;" & _

"User id=myusername;" & _

"Password=mypassword;"

oConn.Open strconn

%>

5 OLE DB Provider for Microsoft Jet

Standard is also the common way of access.

<%

Dim Oconn,strconn

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 strconn

%>

If it is a workgroup system database, then the connection string is as follows.

<%

Dim Oconn,strconn

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 strconn

%>

Note: When using the 4.0 OLE DB Provider provider driver, remember to convert the MDB and MDW to the 4.0 database format.

If the MDB is setting a password, use the following connection string.

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0" & _

"Data Source=c:\somepath\mydb.mdb;" & _

"Jet oledb:database Password=mydbpassword;", _

"Admin", "" "

If the MDB is located on the network and is shared, use the following connection string.

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 OLE DB Provider for Microsoft jet to access the Excel spreadsheet.

<%

Dim Oconn,strconn

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 strconn

%>

Description: Here the Hdr=yes means that the provider accesses the Excel spreadsheet without adding the first row of data to the dataset, and when Hdr=no means that the provider accesses the Excel spreadsheet when the action is exactly the opposite of what is described above.

7 OLE DB Provider for ODBC Databases

Accessing the MS Access database

<%

Dim Oconn,strconn

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 strconn

%>

Accessing the MS SQL Server database

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=msdasql" & _

"Driver={sql Server};" & _

"Server=myservername;" & _

"Database=mydatabasename;" & _

"Uid=myusername;" & _

"Pwd=mypassword;"

oConn.Open strconn

%>

8 OLE DB Provider for Oracle (from Microsoft)

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=msdaora" & _

"Data source=myoracledb;" & _

"User id=myusername;" & _

"Password=mypassword;"

oConn.Open strconn

%>

9 OLE DB Provider for Oracle (from Oracle)

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=oraoledb.oracle" & _

"Data source=myoracledb;" & _

"User id=myusername;" & _

"Password=mypassword;"

oConn.Open strconn

%>

OLE DB Provider for SQL Server

Standard connection mode

<%

Dim Oconn,strconn

Set oconn=server.createobject ("ADODB. Connection ")

Strconn= "Provider=sqloledb" & _

"Data source=myservername;" & _

"Initial catalog=mydatabasename;" & _

"User id=myusername;" & _

"Password=mypassword;"

oConn.Open strconn

%>

Access through the machine's IP address

<%

Dim Oconn,strconn

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 strconn

%>

Description: IP:xxx.xxx.xxx.xxx

SQL Server default port: 1433

Appendix 1:

An introduction to ADO data Objects and their functions

ADO Data Objects	Function
. Connection	Represents the only conversation with a data source
. Command	With the command object, you can execute stored procedures with parameters, SQL queries, SQL statements. You can use the command object to receive a Recordset object.
. RecordSet	Used to represent a database table.
. Error	The object contains all the error and warning information. The object has only property values.
. Field	A Field object represents a column in a dataset.
. Parameter	The parameter object is used to provide parameters required by the SQL query or stored procedure to be parameterized, or to return a value from a stored procedure.
. Property	Represents the specific attributes of a data provider.