萬盛學電腦網

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

ASP 經典編程速查手冊:數據庫連接、SQL 命令、Recordset 操作、常用判斷

ASP 開發高頻代碼片段匯總,涵蓋 Access/SQL Server 連接、CRUD SQL 模板、Recordset 核心方法、數據類型判斷,適合打印貼在顯示器邊隨時查閱。

1. 資料庫連接字串

Access (Jet OLEDB)

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

SQL Server (SQLOLEDB)

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

2. Recordset 開啟模式速查

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) - 最快
  • 需 RecordCount:rs.Open sql, conn, 3, 1 (Static + ReadOnly)
  • 編輯/新增:rs.Open sql, conn, 1, 3 (Keyset + Optimistic) 或 3, 3

3. SQL 命令模板庫

查詢與篩選

' 基礎查詢
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

DDL 結構操作

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

4. Recordset 核心方法/屬性

| 方法/屬性 | 作用 | 典型用法 | |-----------|------|----------| | 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>", " ") |

5. 常用判斷函數

' 是否為數字
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

6. 資源釋放黃金法則 (必須執行!)

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

copyright © 萬盛學電腦網 all rights reserved