萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP 連接 11 種資料庫連線字串大全:Access、SQL Server、Oracle、MySQL、FoxPro、Text/CSV 等

連線字串速查表

所有範例均採用 DSN-less (無需系統 DSN) 方式,直接在程式碼中指定,部署最簡單。

#資料庫提供者/驅動程式連線字串範本
1Access (.mdb)Jet OLEDB 4.0Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\db.mdb;Jet OLEDB:Database Password=pwd;
2Access (.accdb)ACE OLEDB 12.0/16.0Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\path\db.accdb;
3Access (ODBC)MS Access DriverDriver={Microsoft Access Driver (*.mdb)};DBQ=C:\path\db.mdb;
4SQL Server (OLEDB)SQLOLEDB / MSOLEDBSQLProvider=MSOLEDBSQL;Server=192.168.1.100;Database=MyDB;UID=sa;PWD=pwd;Encrypt=yes;TrustServerCertificate=yes;
5SQL Server (ODBC)ODBC Driver 17/18Driver={ODBC Driver 17 for SQL Server};Server=192.168.1.100;Database=MyDB;UID=sa;PWD=pwd;
6Oracle (OLEDB)OraOLEDB.OracleProvider=OraOLEDB.Oracle;Data Source=ORCL;User Id=scott;Password=tiger;
7Oracle (ODBC)Oracle in OraClientDriver={Oracle in OraClient12Home1};DBQ=ORCL;UID=scott;PWD=tiger;
8MySQL (ODBC)MySQL ODBC 8.0 UnicodeDriver={MySQL ODBC 8.0 Unicode Driver};Server=127.0.0.1;Port=3306;Database=my_db;UID=root;PWD=pwd;Charset=utf8mb4;
9Visual FoxPro (.dbc)VFP OLEDB / ODBCProvider=VFPOLEDB.1;Data Source=C:\path\db.dbc; 或 Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=C:\path\db.dbc;
10Text/CSV (ODBC)Text DriverDriver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\path\folder\;Extensions=txt,csv;Persist Security Info=False;
11Text/CSV (OLEDB)Jet OLEDB + Text ISAMProvider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\folder\;Extended Properties="text;HDR=Yes;FMT=Delimited";

ASP 通用連線模組:conn.asp

<%@ 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
%>

64 位元 IIS 部署關鍵

  • Access (.mdb) 需啟用「啟用 32 位元應用程式」或改用 ACE 16.0 (支援 64 位元)
  • SQL Server 建議用 MSOLEDBSQL (最新) 或 SQLNCLI11,勿用舊 SQLOLEDB
  • ODBC 驅動程式必須安裝 64 位元版本
  • 連線池:ASP 經典預設啟用 OLEDB/ODBC 連線池,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 特殊用法

' 讀取 CSV 如同查詢資料表
' 假設檔案 users.csv 在 /import/ 目錄,第一行為標題
sql = "SELECT * FROM users.csv WHERE City = 'Taipei'"
Set rs = conn.Execute(sql)
' 欄位名即為 CSV 第一行標題
copyright © 萬盛學電腦網 all rights reserved