連線字串兩大主流選擇
| 提供者 | 連線字串範例 | 特點 |
| Microsoft Access Driver (ODBC) | Driver={Microsoft Access Driver (*.mdb)};DBQ=路徑 | 舊版相容、無需安裝 OLEDB |
| Microsoft.Jet.OLEDB.4.0 | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=路徑 | 效能較好、支援密碼參數 |
標準連線模組:conn.asp
<%@ Language=VBScript CodePage=65001 %>
<%
' ---------- 設定區 ----------
Const DB_REL_PATH = "/data/app.mdb" ' 相對網站根目錄
Const DB_PWD = "yourStrongPassword" ' 若無密碼留空 ""
' ----------------------------
Dim conn, connStr, dbPath
dbPath = Server.MapPath(DB_REL_PATH)
If DB_PWD <> "" Then
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & _
";Jet OLEDB:Database Password=" & DB_PWD
Else
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
End If
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
' 之後每頁只需 <--#include file="conn.asp"--> 即可使用 conn 物件
%>
基礎 CRUD 操作範例
1. 查詢 (SELECT) - 唯讀游標
<!--#include file="conn.asp"-->
<%
Dim rs, sql
sql = "SELECT ID, Title, CreateDate FROM News WHERE IsPub=1 ORDER BY CreateDate DESC"
Set rs = conn.Execute(sql) ' 唯讀、順向、效能最佳
If Not rs.EOF Then
Do While Not rs.EOF
Response.Write "" & rs("Title") & "
"
Response.Write "" & rs("CreateDate") & "
"
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
%>
2. 新增 (INSERT) - 參數化防注入
<!--#include file="conn.asp"-->
<%
Dim cmd, title, content
title = "測試標題"
content = "文章內容..."
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO News (Title, Content, CreateDate, IsPub) VALUES (?, ?, Now(), 1)"
cmd.CommandType = 1 ' adCmdText
cmd.Parameters.Append cmd.CreateParameter("@t", 200, 1, 255, title) ' adVarChar, adParamInput
cmd.Parameters.Append cmd.CreateParameter("@c", 201, 1, -1, content) ' adLongVarChar
cmd.Execute
Response.Write "新增完成,影響列數:" & cmd.RecordsAffected
Set cmd = Nothing
%>
3. 更新 (UPDATE)
cmd.CommandText = "UPDATE News SET Title=?, Content=? WHERE ID=?"
cmd.Parameters.Append cmd.CreateParameter("@t", 200, 1, 255, newTitle)
cmd.Parameters.Append cmd.CreateParameter("@c", 201, 1, -1, newContent)
cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , newsID) ' adInteger
cmd.Execute
4. 刪除 (DELETE)
cmd.CommandText = "DELETE FROM News WHERE ID=?"
cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , newsID)
cmd.Execute
資源釋放模式 (重要)
<%
On Error Resume Next
' ... 你的資料庫操作 ...
If Err.Number <> 0 Then
Response.Write "資料庫錯誤:" & Err.Description
Err.Clear
End If
' 統一關閉
If Not rs Is Nothing Then
If rs.State = 1 Then rs.Close ' adStateOpen = 1
Set rs = Nothing
End If
If Not cmd Is Nothing Then Set cmd = Nothing
If Not conn Is Nothing Then
If conn.State = 1 Then conn.Close
Set conn = Nothing
End If
%>
常見問題排查
- 「找不到提供者」:64 位元 IIS 需啟用「啟用 32 位元應用程式」或改用 ACE OLEDB 12.0/16.0 (支援 .accdb)。
- 「磁碟或網路錯誤」:檢查
Server.MapPath 路徑是否正確、IIS 執行身分 (IUSR/ApplicationPoolIdentity) 是否有資料夾讀寫權限。
- 並發鎖死:Access 不適合高併發寫入,建議遷移至 SQL Server / MySQL。