WEB开发技术 第五章
5.6 ADO综合应用实例分析
From,吴教育
Email,wujiaoyu@21cn.com
Q Q,188128997
课程引入与回顾
? 利用 Command对象来执行各种 SQL指令
? 利用 Command对象调用 access的查询
? 利用 Command对象调用 sql server的存储过程
目标
? 实现具有分页功能的留言簿
? 实现新闻自动发布系统
? 实现在线考试系统
具有分页功能的留言簿
? 在 ASP中利用 ADO来实现一个具有分面功能的留言簿，分
页显示如图所示：
数据库设计
留言页面显示效果
留言页面代码 (input.htm)
<html>
<head>
<title>小天鹅留言簿 </title>
</head>
<body bgcolor="beige">
<table border="4" bordercolor="orange" width=100%>
<tr bgcolor="orange">
<td ALIGN="CENTER"><font color=white><b>小 天 鹅 留 言 簿 </b></font></td>
</tr>
</table>
<form Action="handle.asp" Method="POST">
<table border="4" bordercolor="orange" width="100%">
<tr>
<td>姓名,</td>
<td><input TYPE="TEXT" NAME="Name" SIZE="20"></td>
</tr>
<tr>
<td>Email:</td>
<td><input TYPE="TEXT" NAME="Email" SIZE="40"></td>
</tr>
<tr>
<td>主题,</td>
<td><input TYPE="TEXT" NAME="Subject" SIZE="60"></td>
</tr>
<tr>
<td VALIGN="TOP">留言,</td>
<td><textarea NAME="Memo" ROWS="6" COLS="60"></textarea></td>
</tr>
</table>
<div align="center"><center><table WIDTH="100%">
<tr ALIGN="CENTER">
<td><input TYPE="Submit" NAME="SEND" VALUE="提交留言 "></td>
<td><a HREF="display.asp">查看留言 </a></td>
<td><input TYPE="RESET" VALUE="清除重写 "></td>
</tr>
</table>
</center></div>
</form>
</body>
</html>
留言处理页面 (handle.asp)
<!--#include file="adovbs.inc" -->
<%
Name = Request("Name")
Email = Request("Email")
Subject = Request("Subject")
Memo = Request("Memo")
If Name = "" Or Email = "" Or Subject = "" Or Memo = "" Then
Response.Write "字段空白不接受！ "
Response.End '不再处理以下的程序
End If
Set conn = Server.CreateObject("ADODB.Connection")
DBPath = Server.MapPath("lyb.mdb")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "GuestBook",conn,adOpenDynamic,adLockPessimistic
rs.AddNew
rs("姓名 ") = Name
rs("Email") = Email
rs("主题 ") = Subject
rs("留言 ") = Memo
rs.Update
Response.Redirect "display.asp"
%>
留言分页显示页面 (display.asp)
<!--#include file="adovbs.inc" -->
<!--#include file="RsToGbk.asp" -->
<!--#include file="GbookUtl.asp" -->
<html><body bgcolor=beige>
<h2 ALIGN="CENTER">小 天 鹅 留 言 簿 </h2><hr WIDTH="100%" color=orange>
<%
sql = "Select * From GuestBook Order By 时间 DESC"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & Server.MapPath("lyb.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn,adOpenStatic
rs.PageSize=2
Page = CLng(Request("Page")) ' CLng 不可省略
If Page < 1 Then Page = 1
If Page > rs.PageCount Then Page = rs.PageCount
ShowOnePage rs,Page
%>
<form Action="display.asp" Method="GET" align="right">
<a HREF="input.htm"><p>返回留言表单 </a> <%
If Page <> 1 Then
Response.Write "<A HREF=display.asp?Page=1>第一页 </A> "
Response.Write "<A HREF=display.asp?Page=" & (Page-1) & ">上一页 </A> "
End If
If Page <> rs.PageCount Then
Response.Write "<A HREF=display.asp?Page=" & (Page+1) & ">下一页 </A> "
Response.Write "<A HREF=display.asp?Page=" & rs.PageCount & ">最后一页 </A> "
End If
%>页码,<font COLOR="Red"><%=Page%>/<%=rs.PageCount%></font> </p>
</form></body></html>
留言分页显示页面 (gbookutl.asp)
<%
Sub ShowOnePage( rs,Page )
rs.AbsolutePage = Page
For iPage = 1 To rs.PageSize
RsToGbook rs
rs.MoveNext
If rs.EOF Then Exit For
Next
End Sub
%>
留言分页显示页面 (rstogbk.asp)
<%
Sub RsToGbook( rs )
Response.Write "<table border=4 bordercolor=orange width=""100%"">"
Response.Write "<TR bgcolor=orange>"
Response.Write "<TD><font color=white><b>姓 名," & rs("姓名 ") & "</b></font></TD>"
Response.Write "<TD>Email:<A HREF=""mailto:" & rs("Email") & """>"& rs("Email") &
"</TD></TR>"
Response.Write "<TR><TD COLSPAN=2>主 题," & rs("主题 ") & "</TD></TR>"
memo=Replace(rs("留言 "),Chr(13),"<BR>" )
Response.Write "<TR><TD COLSPAN=2 bgcolor=beige><PRE><FONT Size=+1>" &
memo & "</FONT></PRE></TD></TR>"
Response.Write "<TR><TD COLSPAN=2>留言," & rs("留言 ") & "</TD></TR>"
Response.Write "</TABLE><br><HR color=orange><br>"
End Sub
%>
新闻自动发布系统
? 本系统可用来发布新闻
? 浏览新闻
? 管理新闻等
数据库设计
新闻总目录页 (首页 default.asp)
default.asp
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>新闻 </title>
<link rel="stylesheet" type="text/css" href="news.css">
<Script language="JavaScript">
function NewsWindow(id)
{
window.open('newswind.asp?id='+id,'infoWin','height=400,width=600,scrollbars=yes,resizable=yes');
}
</Script>
</head>
<body>
<%
set conn=server.createobject("ADODB.CONNECTION")
conn.open "DBQ="+server.mappath("news.mdb")+";DRIVER={Microsoft Access Driver (*.mdb)};"
set rs=server.createobject("adodb.recordset")
sql="SELECT * from news order by ID desc"
rs.open sql,conn,1,1
if rs.eof and rs.bof then
response.write "<p>还 没 有 任 何 新 闻 </p>"
else%>
<p><strong>全部新闻 </strong><table border=1 borderColorDark=#ffffec borderColorLight=#5e5e00 cellPadding=1 cellSpacing=0 width=100%>
<tr bgcolor=cccccc align=center><td>标题 </td><td>作者 </td><td>日期 </td></tr>
<%
cc=1
if not isempty(request("page")) then
pagecount=cint(request("page"))
else
pagecount=1
end if
rs.PageSize=10
rs.AbsolutePage=pagecount
default.asp(续 )
For iPage = 1 To rs.PageSize
If rs.EOF Then Exit For
if cc mod 2=1 then
Response.Write "<tr bgcolor=#E7E7E7>"
else
Response.Write "<tr BGCOLOR=#F4F4F4>"
end if
%>
<td><a href="javascript:NewsWindow(<%=rs("ID")%>)"><u><%=rs("title")%></u></a></td><td><%=rs("write")%> </td><td><%=rs("times")%>
<%
if DateDiff("d",rs("times"),date())<1 then Response.Write "<font color=ff0000>new</font>"
Response.Write "</td></tr>"
cc=cc+1
rs.movenext
Next
Response.Write "</table><p>共 "&rs.recordcount&"条新闻 "
if rs.PageCount>1 Then
If pagecount<>1 Then
Response.Write "<A HREF=default.asp?Page=1><img src=/image/first.gif border=0 alt=首页 ></A>"
Response.Write "<A HREF=default.asp?Page="&(pagecount-1)&"><img src=/image/pre.gif border=0 alt=前页 ></A>"
End If
If pagecount<>rs.PageCount Then
Response.Write "<A HREF=default.asp?Page="&(pagecount+1)&"><img src=/image/next.gif border=0 alt=后页 ></A>"
Response.Write "<A HREF=default.asp?Page="&rs.PageCount&"><img src=/image/last.gif border=0 alt=尾页 ></A>"
End If
End If
end if
rs.close
set rs=nothing
conn.close
set conn=nothing
%><p align=center><a href="newsedit.asp">编辑新闻 </a>
新闻浏览面 (newswind.asp)
newswind.asp
<html>
<head>
<title>新闻 </title>
<link rel="stylesheet" type="text/css" href="news.css">
</head>
<%
id=trim(request("id"))
set conn=server.createobject("ADODB.CONNECTION")
conn.open "DBQ="+server.mappath("news.mdb")+";DRIVER={Microsoft Access Driver (*.mdb)};"
set rs=server.createobject("adodb.recordset")
sql="SELECT * from news where ID="&id
rs.open sql,conn,1,1
if rs.eof then
response.write "错误的 ID号 "
rs.close
set rs=nothing
conn.close
set conn=nothing
response.end
else%>
<table width="100%" HEIGHT=100% border="1" cellspacing="0" cellpadding="0" bordercolorlight="#000000"
bordercolordark="#FFFFFF">
<tr bgcolor="#FAD185">
<td height=20 align=center><b><%=rs("title")%></b></td>
</tr>
<tr>
<td valign=top>
newswind.asp(续 )
<%'如果 type=0,代表是文本类型信息，若 type=1，代表是链接信息 %>
<%if rs("type")=0 then%>
<p><%=replace(rs("content"),chr(13)&chr(10),"<p>")%>
<%else%>
<iframe id=BoardTitle name=newscon style="HEIGHT:100%; VISIBILITY,inherit; WIDTH:100%; Z-INDEX,2"
scrolling=auto frameborder=0 src="<%=rs("content")%>" ></iframe></td>
<%end if%>
</tr>
</table>
<br><b>相关新闻,</b><ul>
<%
sql="SELECT * from news where ID<>"&id&" and keyw='"&rs("keyw")&"' order by ID desc"
rs.close
rs.open sql,conn,1,1
do while not rs.eof
%>
<li><a href=newswind.asp?id=<%=rs("ID")%>><u><%=rs("title")%></u></a>--<%=rs("write")%>【 <%=rs("times")%>】
<%
rs.movenext
loop
rs.close
set rs=nothing
conn.close
set conn=nothing
end if
%></ul>
</html>
新闻管理页 (newsedit.asp)
newsedit.asp
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>新闻 </title>
<link rel="stylesheet" type="text/css" href="news.css">
<script language="JavaScript">
function NewsWindow(id)
{
window.open('newswind.asp?id='+id,'infoWin','height=400,width=600,scrollbars=yes,resizable=yes');
}
</script>
</head>
<body>
<%
set conn=server.createobject("ADODB.CONNECTION")
conn.open "DBQ="+server.mappath("news.mdb")+";DRIVER={Microsoft Access Driver (*.mdb)};"
set rs=server.createobject("adodb.recordset")
if request("del")<>"" then conn.Execute("delete * from news where id="&request("del"))
sql="SELECT * from news order by ID desc"
rs.open sql,conn,1,1
if rs.eof and rs.bof then
response.write "<p>还 没 有 任 何 新 闻 </p>"
else
if not isempty(request("page")) then
pagecount=cint(request("page"))
else
pagecount=1
end if
newsedit.asp(续 )
rs.PageSize=10
rs.AbsolutePage=pagecount
%>
<p><strong>全部新闻 </strong><table width=100%>
<%For iPage = 1 To rs.PageSize
If rs.EOF Then Exit For
id=rs("ID")%>
<tr><td><a href="javascript:NewsWindow(<%=id%>)"><u><%=rs("title")%></u></a>--<%=rs("write")%>【 <%=rs("times")%>】
</td><td align=right><img src=note.gif><a href=newsadd.asp?id=<%=id%>>编辑 </a> <img src=del.gif><a href=newsedit.asp?del=<%=id%>>
删除 </a></td></tr>
<%
rs.movenext
Next
Response.Write "</table><p>共 "&rs.recordcount&"条记录 <br>"
if rs.PageCount>1 Then
If pagecount<>1 Then
Response.Write "〖 <A HREF=newsedit.asp?Page=1&boardid="&request("boardid")&">首页 </A> "
Response.Write "〖 <A HREF=newsedit.asp?Page="&(pagecount-1)&">前页 </A> "
End If
If pagecount<>rs.PageCount Then
Response.Write "〖 <A HREF=newsedit.asp?Page="&(pagecount+1)&">后页 </A> "
Response.Write "〖 <A HREF=newsedit.asp?Page="&rs.PageCount&">尾页 </A>〗 "
End If
End If
end if
rs.close
set rs=nothing
conn.close
set conn=nothing
%>
<p align=right><a href=newsadd.asp>添加新闻 </a> <a href=".">查看新闻 </a>
新闻发布页 (newsadd.asp)
newsadd.asp
<link rel="stylesheet" type="text/css" href="news.css">
<%
id=Request("id")
if Request.form("title")<>"" then
set conn=server.createobject("ADODB.CONNECTION")
conn.open "DBQ="+server.mappath("news.mdb")+";DRIVER={Microsoft Access Driver (*.mdb)};"
title=Trim(request.form("title"))
sql="select * from news where "
if id<>"" then
sql=sql&"id="&id
else
sql=sql&"title='"&title&"'"
end if
set rs=Server.CreateObject("ADODB.recordset")
rs.Open sql,conn,1,3
if rs.eof or rs.bof then
rs.addnew
end if
rs("title")=Request.form("title")
rs("content")=Request.form("body")
rs("type")=Request.form("type")
rs("keyw")=Request.form("keyw")
rs("write")=Request.form("writer")
rs.Update
rs.close
conn.close
set rs=nothing
set conn=nothing
end if
if id<>"" then
set conn=server.createobject("ADODB.CONNECTION")
conn.open "DBQ="+server.mappath("news.mdb")+";DRIVER={Microsoft Access Driver (*.mdb)};"
sql="select * from news where id="&id
newsadd.asp(续 )
set rs=Server.CreateObject("ADODB.recordset")
rs.Open sql,conn,1,1
if not rs.eof then
title1=rs("title")
content=rs("content")
keyw=rs("keyw")
write=rs("write")
end if
rs.close
conn.close
set rs=nothing
set conn=nothing
end if
%>
<form name=form1 method="post" action="newsadd.asp">
<input value="<%=id%>" type=hidden name=id>
<P align="center">标 题,<INPUT size=85 name=title value=<%=title1%>></P>
<P align="center">内 容,<TEXTAREA cols=73 name=body rows=15><%=content%></TEXTAREA></P>
<P align="center">关键字,<INPUT size=25 name=keyw value=<%=keyw%>>
作者：
<INPUT size=25 name=writer value=<%=write%>>
类型：
<select span style="font-size:10.5pt" name="type">
<option value="0">文本 </option>
<option value="1">链接 </option>
</select>
<p align="center">
<INPUT class=buttonface type=submit value=" 确 定 ">
<INPUT class=buttonface type=reset value=" 清 除 "></p>
</form>
<P align="right"><a href="newsedit.asp">编辑新闻 </a> <a href=".">查看新闻 </a>
</BODY>
</HTML>
在线考试系统
? 主要包括如下几个功能页面
– 登录,(身份验证 )
– 考试：
– 成绩处理并显示成绩
数据库设计 -成绩表
? 用户成绩表,(学号、姓名,ASP,JSP,XML)
说明：成绩初始值为 -1，学号作为密码
数据库设计 -题库表
? 考试题目表：每科一个表 (类型、题号、题目、选
项 1~4、分数、答案 )
登录页面 (login.asp)
login.asp
<%
No = Request("No")
Name = Request("Name")
Lesson = Request("Lesson")
If Request("Send") <> Empty Then
SQL = "Select * From 成绩单 "
SQL = SQL & "Where 学号 =" &"'"& No &"'"& " And 姓名 ='" & Name & "'"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & Server.MapPath("test.mdb")
Set rsScore = conn.Execute (sql)
If rsScore Is Nothing or rsScore.eof Then
Msg = "学号或姓名栏位错误 !"
ElseIf rsScore( Lesson ) <> -1 Then
Msg = "你已经考过本科目了 !"
Else
Response.Redirect "Test.asp?" & Request.QueryString
End If
End If
%>
<html>
<head></head>
login.asp(续 )
<body bgcolor=beige>
<h1 align="center">新东方学校 E01期末考试 </h1>
<hr>
<h2>选择考试科目,</h2>
<blockquote>
<form action="login.asp" method="GET">
<p>考试科目,<select name="Lesson" size="1">
<option value=asp>ASP</option>
<option value=JSP>JSP</option>
<option value=XML>XML</option>
</select></p>
<p>姓名,<input type="text" size="20" name="Name" Value="<%=Name%>"></p>
<p>密码,<input type="password" size="20" name="No" Value="<%=No%>"></p>
<p><input type="submit" Name="Send" value=" 开始考试 "> </p>
</form>
</blockquote>
<hr>
<FONT Color=Red><%=Msg%></FONT>
</body>
</html>
考试页面 (test.asp)
test.asp
<%
Lesson = Request("Lesson")
No = Request("No")
Name = Request("Name")
SQL = "Select * From " & Lesson & " Order By 题号 "
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & Server.MapPath("test.mdb")
Set rs = conn.Execute( sql )
%>
<HTML>
<BODY BgColor=beige>
<center>
<H1><font color=#6699dd>新东方学校期末考试系统,</font></h1>
</center>
<H2>考试科目 -- <%=Lesson%><HR></H2>
<FORM Action=Score.asp Method=GET>
<INPUT Type=Hidden Name=Lesson Value=<%=Lesson%>>
<INPUT Type=Hidden Name=No Value=<%=No%>>
<INPUT Type=Hidden Name=Name Value=<%=Name%>>
<%
While Not rs.EOF
%>
<B><%=rs("题号 ")%>,<%=Server.HTMLEncode(rs("题目 "))%>
test.asp(续 )
(<%=rs("类型 ")%>:<%=rs("分数 ")%>分 )</B>
<div>
<%
For I = 1 To 4
If rs("类型 ") = "单选 " Then
TestType = "Radio"
Else
TestType = "CheckBox"
End If
%>
<INPUT Type=<%=TestType%> Name=No<%=rs("题号 ")%>
Value=<%=I%>><%=Server.HTMLEncode(rs("选项 " & I))%><BR>
<%
Next
Response.Write "</div>"
rs.MoveNext
Wend
%>
<INPUT Type=Submit Value=" 交 卷 ">
</FORM>
<HR>
</BODY>
</HTML>
成绩处理页面 (score.asp)
score.asp
<!--#include file="adovbs.inc" -->
<%
Lesson = Request("Lesson")
No = Request("No")
Name = Request("Name")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & Server.MapPath("test.mdb")
Set rs = conn.Execute( lesson )
%>
<HTML>
<BODY BgColor=beige>
<H2>考试成绩 <HR></H2>
<%
Score = 0
While Not rs.EOF
Sel = Request( "No" & rs("题号 ") )
Ans = rs("解答 ")
If Ans = Sel Then
Score = Score + Cint(rs("分数 "))
End If
rs.MoveNext
Wend
SQL = "Select * From 成绩单 "
SQL = SQL & "Where 学号 =" &"'" &No &"'"& " And 姓名 ='" & Name & "'"
Set conn = Server.CreateObject("ADODB.Connection")
DBPath = Server.MapPath("test.mdb")
conn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set rsScore = Server.CreateObject("ADODB.Recordset")
rsScore.Open sql,conn,adOpenDynamic,adLockPessimistic
if rsscore(lesson)=-1 then
rsScore(Lesson) =Cint(score) 'score
rsScore.Update
score.asp(续 )
else
Response.Write "<script>{alert('你已经考过了 ')}</script>"
end if
%>
<BLOCKQUOTE>
<%=Name%>同志你好，你的考试成绩统计为,<BR>
<BLOCKQUOTE>
<center>
<TABLE Border=2 bgcolor=orange width=80%>
<TR BgColor=red><TD>你参加的科目 </TD><TD>你的分数 </TD></TR>
<TR><TD>ASP</TD><TD Align=Right><%=shizhiguo(RsScore("ASP"))%></TD></TR>
<TR><TD>JSP</TD><TD Align=Right><%=shizhiguo(rsscore("JSP"))%></TD></TR>
<TR><TD>XML</TD><TD Align=Right><%=shizhiguo(rsscore("XML"))%></TD></TR>
</TABLE></center>
</BLOCKQUOTE>
</BLOCKQUOTE>
<HR>
<A HREF="login.asp?No=<%=No%>&Name=<%=Name%>">参加其他科目考试 </A>
</BODY>
</HTML>
<%
Function Shizhiguo( Score )
If Score = -1 Then
shizhiguo = "你还没有考试 "
ELseIf Score < 60 Then
shizhiguo = "<FONT Color=Red>" & Score & "</FONT>"
Else
shizhiguo = Score
End If
End Function
%>
参考资料
? <<ASP动态网站编程 >> 石志国 清华大学出版社
? <<ASP精解案例教程 >> 石志国 清华大学出版社
? <<ASP网络编程技术与实例 >> 胡标 人民邮电出版社
? <<网络程序设计 -ASP>> 尚俊杰 清华大学出版社
总结
? 实现了具有分页功能的留言簿
? 实现了新闻自动发布系统
? 实现了在线考试系统
习题与作业
1,参考上面的例子，自己着手实现一个具有
分页功能的留言簿
2,参考上面的例子，自己着手实现一个新闻
自动发布系统