Connect the character servings of various databases via ADO

zhaozj2021-02-16  169

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

转载请注明原文地址:https://www.9cbs.com/read-7926.html

New Post(0)