資料庫查詢是動態網站核心。本文從基本 SELECT 到動態條件構建、Recordset 遍歷輸出、搜索表單整合,構建完整查詢技能樹。
<%
Dim conn, rs, sql, keyword
keyword = Request.QueryString("kw") ' GET 方式搜索
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("data/guestbook.mdb")
' 動態構建 WHERE
sql = "SELECT * FROM Guestbook"
If Trim(keyword) <> "" Then
' 防注入轉義
keyword = Replace(keyword, "'", "''")
sql = sql & " WHERE Name LIKE '%" & keyword & "%' OR Message LIKE '%" & keyword & "%'"
End If
sql = sql & " ORDER BY AddTime DESC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 1 ' Static, ReadOnly - 支援 RecordCount
%>
<table border="1" cellpadding="5" cellspacing="0">
<tr><th>姓名</th><th>電話</th><th>留言</th><th>時間</th></tr>
<%
If rs.BOF And rs.EOF Then
Response.Write "暫無記錄 "
Else
Do While Not rs.EOF
%>
<tr>
<td><%=rs("Name")%></td>
<td><%=rs("Tel")%></td>
<td><%=rs("Message")%></td>
<td><%=FormatDateTime(rs("AddTime"), 2)%></td>
</tr>
<%
rs.MoveNext
Loop
End If
%>
</table>
<p>共 <%=rs.RecordCount%> 筆記錄</p>
<%
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
%>
<form method="get" action="list.asp">
關鍵字:<input type="text" name="kw" value="<%=Request.QueryString("kw")%>">
<input type="submit" value="搜索">
</form>
Dim whereClause, conditions, condCount
conditions = Array()
condCount = 0
If Trim(Request("name")) <> "" Then
ReDim Preserve conditions(condCount)
conditions(condCount) = "Name LIKE '%" & Replace(Request("name"), "'", "''") & "%'"
condCount = condCount + 1
End If
If IsNumeric(Request("minAge")) Then
ReDim Preserve conditions(condCount)
conditions(condCount) = "Age >= " & Request("minAge")
condCount = condCount + 1
End If
' ... 更多條件 ...
If condCount > 0 Then
whereClause = " WHERE " & Join(conditions, " AND ")
Else
whereClause = ""
End If
sql = "SELECT * FROM Users" & whereClause & " ORDER BY RegDate DESC"
' IN 子查詢/列表
sql = "SELECT * FROM Orders WHERE Status IN ('PAID','SHIPPED','COMPLETED')"
' BETWEEN 日期範圍 (Access 用 #...#)
sql = "SELECT * FROM Logs WHERE LogDate BETWEEN #2023-01-01# AND #2023-12-31#"
' 相對日期:最近 7 天
sql = "SELECT * FROM Articles WHERE PubDate >= DATEADD('d', -7, NOW())"
' 移動到第 5 筆 (需 Static/Keyset 游標)
rs.AbsolutePosition = 5
' 分頁:每頁 10 筆,顯示第 3 頁
rs.PageSize = 10
rs.AbsolutePage = 3
Dim i, pageCount
pageCount = rs.PageCount
For i = 1 To rs.PageSize
If rs.EOF Then Exit For
' 輸出 rs.Fields...
rs.MoveNext
Next
SELECT *GetString 批量輸出 HTML 表格:Response.Write rs.GetString(2, , "", " ", " ")
- ✅ 釋放資源儘早:讀完即
Close/Nothing
查詢是讀操作主力,寫出高效、安全、可維護的查詢代碼,是 ASP 開發者的核心基本功。
- 上一篇程序編程: ASP 寫入資料庫記錄入門:從表單提交到 INSERT 執行完整流程
- 下一篇程序編程: ASP 修改資料庫記錄完整流程:查詢回顯 → 表單編輯 → UPDATE 執行
ASP 程式設計排行
推薦文章
- ASP 生成靜態 HTML:用 ADODB.Stream + ServerXML…
- 網頁導向技術全解析:HTTP 狀態碼 200/301/302/307/404 與…
- ASP 判斷字串是否為整數函數 isInteger
- ASP 結合 JavaScript/VBScript 實現動態報表瀏覽器直接列印
- ASP Do While / Do Until 迴圈:條件控制、至少執行一次與實…
- XMLHTTP Open 方法完整指南:參數詳解、同步異步模式與實戰示例
- ASP 分頁程式:上一頁、下一頁、首頁、末頁完整範例
- ASP 輸出輔助函數 WriteLn / PrintLn:控制台與網頁雙模式換行…
- ASP 簡易 UBB 轉 HTML 函數
- HTML 線上編輯器整合指南:Object/Iframe 兩種嵌入模式、資料取值…
copyright © 萬盛學電腦網 all rights reserved