萬盛學電腦網

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

ASP 查詢資料庫記錄入門:WHERE 條件構建、Recordset 遍歷、搜索表單實戰

資料庫查詢是動態網站核心。本文從基本 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
%>

HTML + ASP 混合輸出列表

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

搜索表單頁 (search.html)

<form method="get" action="list.asp">
    關鍵字:<input type="text" name="kw" value="<%=Request.QueryString("kw")%>">
    <input type="submit" value="搜索">
</form>

WHERE 條件構建進階模式

多條件組合

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 / BETWEEN / 日期範圍

' 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())"

Recordset 導航控制

' 移動到第 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 需要的欄位:避免 SELECT *
  • ✅ WHERE 欄位建立索引
  • ✅ 大數據集使用分頁:避免一次性傳輸數萬行
  • ✅ GetString 批量輸出 HTML 表格:Response.Write rs.GetString(2, , "", "", " ")
  • ✅ 釋放資源儘早:讀完即 Close/Nothing

查詢是讀操作主力,寫出高效、安全、可維護的查詢代碼,是 ASP 開發者的核心基本功。

copyright © 萬盛學電腦網 all rights reserved