在經典 ASP 開發中,資料庫連接與操作是最基礎也最關鍵的技能。以下整理了從連線、查詢、新增、搜尋、刪除到修改的完整程式碼範例,供開發者快速參考。
使用 ADO 連接 Access 資料庫(.mdb),透過 Server.MapPath 取得實體路徑:
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("資料庫名.mdb")
%>
開啟 Recordset,參數 1, 1 代表 adOpenKeyset, adLockOptimistic,適合讀取與更新:
<%
sql = "SELECT * FROM 資料表"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 1
%>
<!-- 顯示欄位值 -->
<%= rs("欄位名") %>
接收表單資料,使用 INSERT 陳述式寫入:
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("資料庫名.mdb")
name = Request.Form("name")
tel = Request.Form("tel")
msg = Request.Form("message")
sql = "INSERT INTO 表名 (name, tel, message) VALUES ('" & name & "','" & tel & "','" & msg & "')"
conn.Execute sql
conn.Close
Set conn = Nothing
%>
依條件篩選資料:
<%
name = Request.Form("name")
tel = Request.Form("tel")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("資料庫名.mdb")
sql = "SELECT * FROM 表 WHERE name='" & name & "' AND tel='" & tel & "'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 1
%>
<!-- 迴圈輸出結果 -->
<%
Do While Not rs.EOF
%>
<tr>
<td><%= rs("name") %></td>
<td><%= rs("tel") %></td>
<td><%= rs("time") %></td>
</tr>
<%
rs.MoveNext
Loop
%>
依 ID 刪除指定筆資料:
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("資料庫名.mdb")
sql = "DELETE * FROM 表名 WHERE 編號=" & Request.Form("id")
conn.Execute sql
%>
先查詢再更新,參數 1, 3 代表 adOpenKeyset, adLockOptimistic 可更新模式:
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("資料庫名.mdb")
sql = "SELECT * FROM 表名 WHERE 編號=" & Request.Form("id")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 3
rs("name") = Request.Form("name")
rs("tel") = Request.Form("tel")
rs("message") = Request.Form("message")
rs.Update
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
使用 Session 控管登入狀態,每頁頂端加入驗證碼:
<!-- 登入處理 -->
<%
Dim name, password, sql, conn, rs
name = Request.Form("name")
password = Request.Form("password")
sql = "SELECT * FROM 管理員表 WHERE name='" & name & "' AND password='" & password & "'"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("資料庫名.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
If Not rs.EOF Then
Session("checked") = "yes"
Response.Redirect "index.asp"
Else
Session("checked") = "no"
Response.Redirect "login.asp"
End If
%>
<!-- 每頁防護 -->
<%
If Session("checked") <> "yes" Then
Response.Redirect "login.asp"
End If
%>
安全提醒: 以上範例為教學用途,實際專案請務必使用參數化查詢防止 SQL Injection,並對密碼進行雜湊加密儲存。