所有範例均採用 DSN-less (無需系統 DSN) 方式,直接在程式碼中指定,部署最簡單。
| # | 資料庫 | 提供者/驅動程式 | 連線字串範本 |
|---|---|---|---|
| 1 | Access (.mdb) | Jet OLEDB 4.0 | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\db.mdb;Jet OLEDB:Database Password=pwd; |
| 2 | Access (.accdb) | ACE OLEDB 12.0/16.0 | Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\path\db.accdb; |
| 3 | Access (ODBC) | MS Access Driver | Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\path\db.mdb; |
| 4 | SQL Server (OLEDB) | SQLOLEDB / MSOLEDBSQL | Provider=MSOLEDBSQL;Server=192.168.1.100;Database=MyDB;UID=sa;PWD=pwd;Encrypt=yes;TrustServerCertificate=yes; |
| 5 | SQL Server (ODBC) | ODBC Driver 17/18 | Driver={ODBC Driver 17 for SQL Server};Server=192.168.1.100;Database=MyDB;UID=sa;PWD=pwd; |
| 6 | Oracle (OLEDB) | OraOLEDB.Oracle | Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=scott;Password=tiger; |
| 7 | Oracle (ODBC) | Oracle in OraClient | Driver={Oracle in OraClient12Home1};DBQ=ORCL;UID=scott;PWD=tiger; |
| 8 | MySQL (ODBC) | MySQL ODBC 8.0 Unicode | Driver={MySQL ODBC 8.0 Unicode Driver};Server=127.0.0.1;Port=3306;Database=my_db;UID=root;PWD=pwd;Charset=utf8mb4; |
| 9 | Visual FoxPro (.dbc) | VFP OLEDB / ODBC | Provider=VFPOLEDB.1;Data Source=C:\path\db.dbc; 或 Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=C:\path\db.dbc; |
| 10 | Text/CSV (ODBC) | Text Driver | Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\path\folder\;Extensions=txt,csv;Persist Security Info=False; |
| 11 | Text/CSV (OLEDB) | Jet OLEDB + Text ISAM | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\folder\;Extended Properties="text;HDR=Yes;FMT=Delimited"; |
<%@ Language=VBScript CodePage=65001 %>
<%
' ---------- 修改此處切換資料庫 ----------
Const DB_TYPE = "SQLSERVER" ' ACCESS | SQLSERVER | MYSQL | ORACLE | FOXPRO | TEXT
' ----------------------------------------
Dim conn, connStr
Set conn = Server.CreateObject("ADODB.Connection")
Select Case UCase(DB_TYPE)
Case "ACCESS"
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/data/app.mdb") & ";Jet OLEDB:Database Password=secret;"
Case "SQLSERVER"
connStr = "Provider=MSOLEDBSQL;Server=192.168.1.50;Database=CMS;UID=web_user;PWD=ComplexP@ssw0rd;Encrypt=yes;TrustServerCertificate=yes;"
Case "MYSQL"
connStr = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=127.0.0.1;Database=cms;UID=web;PWD=pwd;Charset=utf8mb4;"
Case "ORACLE"
connStr = "Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=web;Password=pwd;"
Case "FOXPRO"
connStr = "Provider=VFPOLEDB.1;Data Source=" & Server.MapPath("/data/app.dbc") & ";"
Case "TEXT"
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/import/") & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
Case Else
Err.Raise vbObjectError, "conn.asp", "未知 DB_TYPE: " & DB_TYPE
End Select
conn.Open connStr
' 之後每頁 即可使用 conn
%>
conn.Close 即歸還池中Dim cmd, sql
sql = "SELECT * FROM Users WHERE Email = ? AND Status = ?"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.CommandType = 1
cmd.Parameters.Append cmd.CreateParameter("@email", 200, 1, 255, "test@example.com")
cmd.Parameters.Append cmd.CreateParameter("@status", 3, 1, , 1)
Set rs = cmd.Execute
' ... 處理 rs ...
rs.Close: Set rs = Nothing
Set cmd = Nothing
' 讀取 CSV 如同查詢資料表
' 假設檔案 users.csv 在 /import/ 目錄,第一行為標題
sql = "SELECT * FROM users.csv WHERE City = 'Taipei'"
Set rs = conn.Execute(sql)
' 欄位名即為 CSV 第一行標題