Characters connected to various databases via ADO [Collection]
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
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) Connection through the System Data Source (System DSN)
<%
DIM? Oconn, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "DSN = TestData_Resoure;"? &? _?
? "Uid = admincount;"? &? _?
? "Pwd = password;
Oconn.open?strConn
%>
2) Connection to File DSN
<%
DIM? Oconn, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "filedsn = c: /somepath/mydb.dsn;"? &? _ ?? "uid = admincount;"? &? _
? "Pwd = password;"
Oconn.open?strConn
%>
3) Connection to 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 accesses the connection string of the database.
1) ODBC DRIVER FOR Access
● The standard is also a relatively common connection method
<%
DIM? Oconn, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
STRCONN =? "Driver = {microsoft? Access? Driver? (*. Mdb)};"? &? _?
"DBQ =" & server.mappath ("testdb.mdb");? &? _
"Uid = admincount;"? &? _
? "Pwd = password;"
Oconn.open?strConn
%>
● If it is a system database of a working group, 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, then 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: 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, 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 the local database (LOCAL DATABASE)
<%
DIM? Oconn, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
StrConn = "driver = {mysql};"? &? _?
"Server = servername;"? &? _
"OPTION = 16834;"? &? _
"Database = mydb;"
Oconn.open?strConn
%>
● Connect the remote database (Remote Database)
<%
DIM? Oconn, Strconn
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?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 = myserName;"? &? _
?????? "Database = myDatabaseName;"? &? _
?????? "uid = myusername;"? &? _
? ????? "pwd = mypassword;"
Oconn.open?strConn
%>
8) ODBC Driver for Sybase (via Sybase System 11 ODBC Driver Driver)
<%
DIM? Oconn, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
STRCONN =? "Driver = {sybase? System? 11};"? &? _
? "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
%>
10) ODBC Driver for Teradata
<%
DIM? Oconn, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
STRCONN =? "Provider = teradata;"? &? _
"DBCNAME = mydbcname;"? &? _?
"Database = mydatabaseename;"? &? _ ?? "uid = myusername;"? &? _
? "Pwd = mypassword;"
Oconn.open?strConn
%>
11) 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
%>
12) ODBC Driver for Visual FoxPro
● Use the database 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 the database container connection mode (ie Free Table Directory mode)
<%
???? <%
???? 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 use OLE DB to connect to the 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 = ????????????? "proviker = 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
● The standard is also a commonly used access method.
<%
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 system database of a workgroup, 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'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, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
StrConn = ??????????? "provider = microsoft.jet.OleDb.4.0;"? &? _
? "DATA? Source = C: /SOMEPATH/Myxcelspreadsheet.xls;"? &? _
? "Extended? Properties =" "Excel? 8.0; HDR = yes;" ";"
Oconn.open?strConn
%>
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, 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
%>
● Access 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
%>
10) OLE DB Provider for SQL Server
● Standard connection method
<%
DIM? Oconn, Strconn
Set? Oconn = Server.createObject ("AdoDb.Connection")
StrConn = ??????????? "provider = sqloledb;"? &? _?
? "Data? Source = myservername;"? &? _
? "Initial? Catalog = mydatabaseename;"? &? _
? "User? Id = myusername;"? &? _
"Password = mypassword;"
Oconn.open?strConn
%>
● Access to the IP address of the machine
<%
DIM? Oconn, Strconn
SET? Oconn = Server.createObject ("AdoDb.Connection") strconn = ??????????? "proviker = sqloledb;"? &? _
? "DATA? Source = xxx.xxx.xxx.xxx, 1433;"? &? _
"NetWork? Library = dbmssocn;"? &? _
? "Initial? Catalog = mydatabaseename;"? &? _
? "User? Id = myusername;"? &? _
"Password = mypassword;"
Oconn.open?strConn
%>
Description: IP: xxx.xxx.xxx.xxx
SQLServer default port: 1433
