萬盛學電腦網

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

ASP 連接 Access 資料庫:DSN-less 連線字串與基礎 CRUD 範例

連線字串兩大主流選擇

提供者連線字串範例特點
Microsoft Access Driver (ODBC)Driver={Microsoft Access Driver (*.mdb)};DBQ=路徑舊版相容、無需安裝 OLEDB
Microsoft.Jet.OLEDB.4.0Provider=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。
copyright © 萬盛學電腦網 all rights reserved