ASP 開發高頻代碼片段匯總,涵蓋 Access/SQL Server 連接、CRUD SQL 模板、Recordset 核心方法、數據類型判斷,適合打印貼在顯示器邊隨時查閱。
<%
Dim conn, mdbPath
mdbPath = Server.MapPath("db/data.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath & ";Jet OLEDB:Database Password=your_pwd"
%>
<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=192.168.1.100;Initial Catalog=MyDB;UID=sa;PWD=your_pwd"
%>
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, CursorType, LockType
' CursorType:
' 0 = adOpenForwardOnly (默認,唯讀、唯前、最快)
' 1 = adOpenKeyset (可見他人修改、不可見新增)
' 2 = adOpenDynamic (完全動態、支援書簽、最慢)
' 3 = adOpenStatic (靜態快照、支援書簽、RecordCount 可用)
' LockType:
' 1 = adLockReadOnly (默認,唯讀)
' 2 = adLockPessimistic (悲觀鎖定,編輯即鎖定)
' 3 = adLockOptimistic (樂觀鎖定,更新時鎖定)
' 4 = adLockBatchOptimistic (批量更新)
rs.Open sql, conn, 0, 1 (ForwardOnly + ReadOnly) - 最快rs.Open sql, conn, 3, 1 (Static + ReadOnly)rs.Open sql, conn, 1, 3 (Keyset + Optimistic) 或 3, 3' 基礎查詢
SELECT * FROM Table WHERE Field = 'Value' ORDER BY SortField DESC
' 模糊查詢
SELECT * FROM Table WHERE Field LIKE '%keyword%'
' 分頁 (SQL Server 2000 風格)
SELECT TOP pageSize * FROM (
SELECT TOP (pageSize * pageNum) * FROM Table ORDER BY Id DESC
) AS T ORDER BY Id ASC
' 統計函數
SELECT COUNT(*) AS Total, SUM(Amount) AS SumAmt, AVG(Price) AS AvgPrice, MAX(Date) AS MaxDate, MIN(Date) AS MinDate
FROM Orders WHERE Status = 1
' 新增
INSERT INTO Table (F1, F2, F3) VALUES ('V1', 'V2', 'V3')
' 更新
UPDATE Table SET F1='V1', F2='V2' WHERE ID = 100
' 刪除
DELETE FROM Table WHERE ID = 100
DELETE FROM Table ' 清空表 (慎用!)
' 複製表數據
INSERT INTO TargetTable SELECT * FROM SourceTable WHERE Condition
CREATE TABLE NewTable (ID INT IDENTITY, Name VARCHAR(50), CreateDate DATETIME DEFAULT GETDATE())
ALTER TABLE Table ADD NewCol VARCHAR(20) DEFAULT 'N/A'
ALTER TABLE Table DROP COLUMN OldCol
DROP TABLE OldTable
MoveNext | 指針下移 | Do While Not rs.EOF: ... rs.MoveNext: Loop |
| MovePrevious | 指針上移 | 需支援書簽的游標 |
| MoveFirst / MoveLast | 首/尾記錄 | |
| AbsolutePosition = N | 跳至第 N 筆 (1-based) | 需 Static/Keyset 游標 |
| PageSize = N | 設定頁大小 | 配合 AbsolutePage 分頁 |
| PageCount | 總頁數 | 需設定 PageSize |
| RecordCount | 總記錄數 | 需 Static/Keyset 游標,ForwardOnly 返回 -1 |
| BOF / EOF | 始/末標記 | If rs.BOF And rs.EOF Then 空記錄集 |
| AddNew | 新增記錄 | rs.AddNew: rs("F")="V": rs.Update |
| Update | 提交變更 | 配合 AddNew/Edit |
| Delete | 刪除當前記錄 | 刪除後指針不動,需 MoveNext |
| Fields(i).Name | 欄位名 | 遍歷結構 |
| GetString | 快速輸出表格 | rs.GetString(2, , "</td><td>", "</tr><tr><td>", " ") |
' 是否為數字
If Not IsNumeric(Request("age")) Then Response.Write "請輸入數字"
' 是否為日期
If Not IsDate(Request("birth")) Then Response.Write "日期格式錯誤"
' 空值處理 (資料庫 NULL)
Dim val : val = "" & rs("Field") ' NULL → ""
If IsNull(rs("Field")) Then val = "預設值"
' 字符串長度 (含中文雙字節)
Function StrWidth(str)
Dim i, c, w : w = 0
For i = 1 To Len(str)
c = AscW(Mid(str, i, 1))
If c > 255 Then w = w + 2 Else w = w + 1
Next
StrWidth = w
End Function
If rs.State = 1 Then rs.Close
Set rs = Nothing
If conn.State = 1 Then conn.Close
Set conn = Nothing
' 順序:先 Close Recordset,再 Close Connection,最後 Set Nothing
收藏此手冊,ASP 維護開發效率提升 80%!