經典 ASP 與 SQL 開發常用語法大全：查詢、異動、分頁與連線

前言

雖然經典 ASP (Active Server Pages) 已非主流，但許多舊系統仍在運作。本文彙整 SQL 常用指令、ADO 連線字串、資料異動、統計函數與分頁邏輯，供維護舊專案時快速查閱。

一、SQL 基礎查詢語法

-- 精確比對
SELECT * FROM Table WHERE Col = 'Value' ORDER BY Col [DESC];

-- 模糊查詢
SELECT * FROM Table WHERE Col LIKE '%Keyword%' ORDER BY Col [DESC];

-- 取前 N 筆 (SQL Server / Access)
SELECT TOP 10 * FROM Table ORDER BY Col [DESC];

-- IN 多值比對
SELECT * FROM Table WHERE Col IN ('V1','V2','V3');

-- 區間查詢
SELECT * FROM Table WHERE Col BETWEEN Val1 AND Val2;

二、資料異動 (DML)

-- 單欄更新
UPDATE Table SET Col = 'NewVal' WHERE Condition;

-- 多欄更新
UPDATE Table SET Col1='V1', Col2='V2' WHERE Condition;

-- 刪除特定筆
DELETE FROM Table WHERE Condition;

-- 刪除全表 (謹慎使用)
DELETE FROM Table;

-- 新增單筆
INSERT INTO Table (Col1, Col2) VALUES ('V1', 'V2');

-- 跨表複製
INSERT INTO TargetTable SELECT * FROM SourceTable;

三、統計彙總函數

SELECT SUM(Amount) AS Total FROM Orders WHERE Year=2023;
-- 取值: rs("Total")

四、資料表結構 (DDL)

CREATE TABLE Tab01 (
    Name VARCHAR(50),
    Created DATETIME DEFAULT GETDATE()
);
DROP TABLE Tab01; -- 永久刪除

五、ADO 連線字串

SQL Server (OLE DB)

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=ServerIP;UID=User;PWD=Pwd;DATABASE=DBName;"

Access (Jet / ACE)

Dim conn, connStr
connStr = "DBQ=" & Server.MapPath("db.mdb") & ";DRIVER={Microsoft Access Driver (*.mdb)};"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr

六、經典分頁邏輯 (ASP + ADO)

<%
PageSize = 10
If Request("page") = "" Then page = 1 Else page = CInt(Request("page"))

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Products ORDER BY ID DESC"
rs.Open sql, conn, 3, 3 ' adOpenStatic, adLockOptimistic
rs.PageSize = PageSize
TotalPages = rs.PageCount
If page > TotalPages Then page = TotalPages
rs.AbsolutePage = page

Dim i, startRec
startRec = (page - 1) * PageSize
For i = 1 To PageSize
    If rs.EOF Then Exit For
    ' 輸出 rs("Field") ...
    rs.MoveNext
Next
%>
<!-- 分頁導覽列 -->
<a href="?page=1">首頁</a> <a href="?page=<%=page-1%>">上一頁</a> ...

七、檔案上傳與刪除實務

Set fs = Server.CreateObject("Scripting.FileSystemObject")
picPath = Server.MapPath(rs("PicUrl"))
If fs.FileExists(picPath) Then fs.DeleteFile picPath, True

總結

經典 ASP 雖老舊，但掌握上述 SQL 與 ADO 模式，仍能高效維護既有系統。新專案建議改用 ASP.NET Core + EF Core 或 Dapper。