本文提供一個完整、可直接執行的 ASP 頁面範例,展示從資料庫連線、查詢、分頁顯示到資源釋放的標準流程。採用 ADO 經典架構,適合初學者建立正確開發習慣。
<!--#include file="conn.asp"-->
<!DOCTYPE html>
<html xmlns="https://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>文章列表</title>
<style>
.article-list { max-width: 800px; margin: 0 auto; font-family: "Microsoft JhengHei", sans-serif; }
.article-item { border-bottom: 1px solid #eee; padding: 15px 0; }
.article-title { font-size: 1.1em; color: #2c3e50; text-decoration: none; }
.article-title:hover { color: #3498db; }
.article-meta { color: #7f8c8d; font-size: 0.9em; margin-top: 5px; }
.pager { text-align: center; margin: 20px 0; }
.pager a { padding: 5px 10px; margin: 0 3px; background: #ecf0f1; border-radius: 3px; text-decoration: none; }
.pager .current { background: #3498db; color: white; }
</style>
</head>
<body>
<div class="article-list">
<h1>最新文章</h1>
<%
' ===== 參數處理 =====
Dim pageSize, currentPage, offset
pageSize = 10
currentPage = CLng(Request.QueryString("page"))
If currentPage < 1 Then currentPage = 1
offset = (currentPage - 1) * pageSize
' ===== 資料庫查詢 =====
Dim conn, rs, sql, totalRecords, totalPages
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("ConnStr")
' 取得總筆數(用於分頁)
sql = "SELECT COUNT(*) FROM Articles WHERE IsPublished = True"
Set rs = conn.Execute(sql)
totalRecords = rs(0)
rs.Close
totalPages = Int((totalRecords + pageSize - 1) / pageSize)
' 分頁查詢(Access 使用 TOP + 子查詢模擬 LIMIT OFFSET)
sql = "SELECT TOP " & pageSize & " ArticleID, Title, PubDate, Summary " & _
"FROM Articles " & _
"WHERE IsPublished = True " & _
"AND ArticleID NOT IN (SELECT TOP " & offset & " ArticleID FROM Articles WHERE IsPublished = True ORDER BY PubDate DESC) " & _
"ORDER BY PubDate DESC"
Set rs = conn.Execute(sql)
' ===== 輸出結果 =====
If rs.EOF Then
Response.Write "目前沒有文章
"
Else
Do While Not rs.EOF
%>
<div class="article-item">
<a href="article.asp?id=<%=rs("ArticleID")%>" class="article-title"><%=rs("Title")%></a>
<div class="article-meta">
發布時間:<%=FormatDateTime(rs("PubDate"), 2)%> |
摘要:<%=Left(rs("Summary"), 100)%>...
</div>
</div>
<%
rs.MoveNext
Loop
End If
' ===== 分頁導覽 =====
If totalPages > 1 Then
%>
<div class="pager">
<%
For i = 1 To totalPages
If i = currentPage Then
Response.Write "" & i & ""
Else
Response.Write "" & i & ""
End If
Next
%>
</div>
<%
End If
' ===== 資源釋放 =====
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing
%>
</div>
</body>
</html>
<%
' conn.asp - 集中管理連線字串
Application.Lock
If IsEmpty(Application("ConnStr")) Then
' Access 2007+ 格式
Application("ConnStr") = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
Server.MapPath("/data/mydb.accdb") & ";Persist Security Info=False;"
' Access 2003 格式備選
' Application("ConnStr") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/data/mydb.mdb")
End If
Application.UnLock
%>
Command 物件配合 Parameters 防 SQL 注入On Error Resume Next 與錯誤記錄#yyyy/mm/dd# 格式rs.Open sql, conn, 1, 1 才支援 MoveFirst/RecordCount