ASP访问数据库的实例例26：与数据库建立连接，文件名为9-26.asp。
<% @ LANGUAGE=VBScript %>
<%
Option Explicit
Dim Cnn,StrCnn
'创建Connection 对象
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("bbs.mdb;")
'使用 Connection 对象的 Open 方法打开数据库bbs.mdb,该数据库用户自己建立
Cnn.Open StrCnn
Response.Write Server.MapPath("bbs.mdb") & "<br>"
Response.Write "数据库连接成功," & Cnn.State & "<br>"
'使用 Connection 对象的 Close 方法关闭连接
Cnn.close
Response.Write "数据库已经关闭," & Cnn.State
'将Connection 对象从内存中删除，以释放资源
Set Cnn = Nothing
%>
运行结果如图
例9.27 循环检索记录，文件名为9-27a.asp和9-27b.asp。先注册文件数据源为Myaa.dsn，使用的数据库为northwind.mdb。
9-27a.asp的内容如下：
<%
strDSN = "FILEDSN=Myaa.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
//初始化Recordset对象
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT 联系人姓名,公司名称 FROM 客户 WHERE 客户ID= 'FAMIA' "
rsCustomers.Open strSQL,cn
//循环查询，并显示结果
Set objFirstName = rsCustomers("联系人姓名")
Set objLastName = rsCustomers("公司名称")
Do Until rsCustomers.EOF
Response.Write objFirstName & "-------- " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
9-27b.asp的内容如下：
<%
’与9-27a.asp的区别是未建立Connection对象，但结果相同
strDSN = "FILEDSN=Myaa.dsn"
strSQL = "SELECT 联系人姓名,公司名称 FROM 客户 WHERE 客户ID= 'FAMIA' "
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
rsCustomers.Open strSQL,strDSN
Set objFirstName = rsCustomers("联系人姓名")
Set objLastName = rsCustomers("公司名称")
Do Until rsCustomers.EOF
Response.Write objFirstName & "-------- " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
9-27a.asp和9-27a.asp的运行结果如图：
例9．30本例共有两个文件：test9-30.html和9-30.asp。test9-30.html用来产生向用户收集信息的表单，用户可以指定要查看“图书信息”或“作者信息”。 9-30.asp用来响应用户的请求。
test9-30.html的内容如下：
<html>
<body>
<form method=post action=9-30.asp>
<p>查看客户信息请点击这里
<input type=submit name=button value="客户信息">
</p>
<p>查看产品信息请点击这里
<input type=submit name=button value="产品信息">
</p>
</form>
</body>
</html>
9-30.asp的内容如下：
<%
const adOpenStatic=3
const adLockReadOnly=1
'建立Recordset对象
Set rs=Server.CreateObject("ADODB.Recordset")
'提取表单域“button”的值
button=Request.Form("button")
'设定光标类型为静态
CursorType=adOpenStatic
'设定锁定类型为只读
LockType=adLockReadOnly
'如果用户选择“客户信息”则执行下面的代码
If button="客户信息" Then
source="select * from 客户"
activeConnection="dsn=pubs"
rs.open source,activeConnection,CursorType,LockType
end if
'如果用户选择“产品信息”则执行下面的代码
If button="产品信息" Then
source="select * from 产品"
ActiveConnection="dsn=pubs"
rs.Open source,ActiveConnection,CursorType,LockType
End if
'查询结果以表格的形式输
Response.Write "<table border=3>"
Response.Write "<tr>"
For i=0 to rs.Fields.Count-1
Response.Write "<td>"&Ucase(rs(i).Name)&"</td>"
Next
Response.Write "</tr>"
'输出表格内容
While NOT rs.EOF
Response.Write "<tr>"
For i=0 to rs.Fields.Count-1
Response.Write "<td>"&rs(i).Value&"</td>"
Next
rs.MoveNext
Wend
Response.Write "</table>"
'取消连接
Set rs=nothing
%>
运行结果如图：
例9．31 Recordset访问数据库，文件名为9-31.asp，数据库为ntopsamp.mdb。
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT Avg(价格) As 平均 From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Avg," & rs2("平均")
rs2.Close
SqlStr = "SELECT Count(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Count(价格)," & rs2(0)
rs2.Close
SqlStr = "SELECT Count(*) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Count(*)," & rs2(0)
rs2.Close
SqlStr = "SELECT Sum(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Sum," & rs2(0)
rs2.Close
SqlStr = "SELECT Min(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Min," & rs2(0)
rs2.Close
SqlStr = "SELECT Max(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Max," & rs2(0)
rs2.Close
SqlStr = "SELECT First(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>First(价格)," & rs2(0)
rs2.Close
SqlStr = "SELECT Last(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Last(价格)," & rs2(0)
rs2.Close
SqlStr = "SELECT First(代号) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>First(代号)," & rs2(0)
rs2.Close
SqlStr = "SELECT Last(代号) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Last(代号)," & rs2(0)
rs2.Close
SqlStr = "SELECT StDev(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>StDev," & rs2(0)
rs2.Close
SqlStr = "SELECT Var(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Var," & rs2(0)
rs2.Close
conn1.Close
%>
运行结果如图：
例9．32 用户登录验证，文件名为9-32.asp和login.asp。
使用的数据库为info.mdb，其中表：userinfo包含userid、psw、logins、pswnotice、pswanswer、email、qq、name、age、education、occupation、city、address、postnum字段。
login.asp的内容如下：
<html>
<body>
<form name="form1" method="post" action="9-32.asp">
<p>用户名
<input name="userid" type="text" id="userid">
</p>
<p> 口令
<input name="psw" type="password" id="psw">
</p>
<p>
<input type="submit" name="Submit" value="提交">
<input type="reset" name="Submit2" value="重置">
</p>
</form>
</body>
</html>
9-32.asp的内容如下：
<html>
<body>
<%
username=trim(request.Form("userid"))
password=trim(request.Form("psw"))
dim conn
Set conn = Server.CreateObject("ADODB.Connection")
set TmpRs=server.createobject("adodb.recordset")
conn.Open "DBQ=" & Server.MapPath("info.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FILe=MS Access;"
Set TmpRs = Server.CreateObject("ADODB.Recordset")
TmpRs.open "select * from userinfo Where userid='"&username&"';",conn,3,3
%>
<% if tmprs.EOF then %>
<script language="vbscript">
MsgBox "该用户不存在，请先注册!!!!"
location.href="javascript:history.back()"
</script>
<%else if password<>tmprs("psw") then %>
<script language="vbscript">
MsgBox "口令错误,请重新输入密码!!!!!"
location.href="javascript:history.back()"
</script>
<% else
session("username")=username
tmprs("logins")=tmprs("logins")+1
tmprs.update
response.Write tmprs("logins")
tmprs.close
set tmprs=nothing
conn.close
set conn=nothing
end if
end if
%>
</body>
</html>
4．数据库的添加及修改
例9.33 添加及修改数据库记录,文件名为9-33.asp和publish_tiezi.asp。
使用的数据库是info.mdb,其中表userinfo、composition、bbs，字段如下：
composition(topic,text,userid,length,time,owner)
bbs(owner,total,numnew,numtopic)
publish_tiezi.asp的内容：
<html>
<body>
<form name="form1" method="post" action="9-33.asp">
<p>topic
<input name="topic" type="text" id="topic">
</p>
<p>text
<textarea name="text" id="text"></textarea>
</p>
<p>owner
<input name="owner" type="text" id="owner">
</p>
<p>
<input type="submit" name="Submit" value="提交">
<input type="reset" name="Submit2" value="重置">
</p>
</form>
</body>
</html>
9-33.asp的内容：
<% if request("topic")="" then %>
<script language="vbscript">
MsgBox "主题不能为空！！！！！"
location.href="javascript:history.back()"
</script>
<%elseif request("text")="" then %>
<script language="vbscript">
MsgBox "论坛不允许灌水行为！！！！！"
location.href="javascript:history.back()"
</script>
<% else
owner=request("owner")
set cn=server.CreateObject("ADODB.connection")
DBPath=server.MapPath("info.mdb")
cn.open "driver={Microsoft Access Driver (*.mdb)};dbq="& DBPath
set com_rs=server.CreateObject("ADODB.recordset")
set bbs_rs=server.CreateObject("ADODB.recordset")
com_rs.open "Select * from composition;",cn,2,3
'表composition中添加新记录
com_rs.AddNew
com_rs("topic")=request("topic")
com_rs("text")=request("text")
‘com_rs("userid")=session.contents("username")
com_rs("length")=len(trim(request("text")))
com_rs("time")=now()
com_rs("owner")=request("owner")
com_rs.update
com_rs.close
'表bbs中修改记录内容
bbs_rs.open "Select * from bbs where owner='"&owner&"'",cn,3,3
if not bbs_rs then
bbs_rs("total")=bbs_rs("total")+1
bbs_rs("numnew")=bbs_rs("numnew")+1
bbs_rs("numtopic")=bbs_rs("numtopic")+1
bbs_rs.update
endif
response.Write,帖子已发表成功"
bbs_rs.close
end if
%>
此例中的com_rs("userid")=session.contents("username")通过在例9.32的login.asp中session("username")=username获得"username"。
例：不分页显示数据库内容（简单应用）
文件display_db_dsn.asp如下：
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT * FROM message order by ID DESC"
Conn.Open "bbs" '用DSN数据源
RS.open sql,Conn,3,2
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head>
<body>
<table width="75%" border="1">
<tr>
<td><div align="center"> 主题</div></td>
<td><div align="center">留言者</div></td>
<td><div align="center">时间 </div></td>
</tr>
<% i=1
while not rs.eof
%>
<tr>
<td> <%= rs("subject") %> </td>
<td><%= rs("name") %> </td>
<td><%= rs("postdate") %> </td>
</tr>
<% rs.movenext
i=i+1
wend
%>
</table>
<p> </p></body>
</html>
文件display_db_lessdsn.asp如下：
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT * FROM message order by ID DESC"
Conn.Open="DRIVER={Microsoft Access Driver (*.mdb)};DBQ="+server.MapPath("/bbs.mdb")
'用非DSN数据源
RS.open sql,Conn,3,2
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head>
<body>
<table width="75%" border="1">
<tr>
<td><div align="center"> 主题</div></td>
<td><div align="center">留言者</div></td>
<td><div align="center">时间 </div></td>
</tr>
<% i=1
while not rs.eof
%>
<tr>
<td><%= rs("subject") %> </td>
<td><%= rs("name") %> </td>
<td><%= rs("postdate") %> </td>
</tr>
<% rs.movenext
i=i+1
wend
%>
</table>
<p> </p></body>
</html>
例9．34 一个简单的BBS应用程序，文件名为bbs.asp。
它的数据库bbs.mdb中的表“meggase”分别有以下五个字段：
“ID”，每个帖子的自动编号；
“subject”，每个帖子的主题；
“name”，加帖用户的姓名；
“email”，用户的电子邮件地址；
“postdate”，加帖的时间。
数据库的DSN 为“bbs”。
程序如下,
<html>
<head>
<title>bbs</title>
<body>
<% 'Sub ShowList() %>
<%
PgSz=3 '设定开关，指定每一页显示的帖子数目，默认为3
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT * FROM message order by ID DESC"
'查询所有帖子，并按帖子的ID倒序排列
Conn.Open "bbs"
RS.open sql,Conn,1,1
If RS.RecordCount=0 then
response.write "<P><center>对不起，数据库中没有相关信息！</center></P>"
else
RS.PageSize = Cint(PgSz) '设定PageSize属性的值
Total=INT(RS.recordcount / PgSz * -1)*-1 '计算可显示的页面的总数
PageNo=Request("pageno")
if PageNo="" Then
PageNo = 1
else
PageNo=PageNo+1
PageNo=PageNo-1
end if
ScrollAction = Request("ScrollAction")
if ScrollAction = "上一页" Then
PageNo=PageNo-1
end if
if ScrollAction = "下一页" Then
PageNo=PageNo+1
end if
if PageNo < 1 Then
PageNo = 1
end if
n=1
RS.AbsolutePage = PageNo
Response.Write "<CENTER>"
position=RS.PageSize*PageNo
pagebegin=position-RS.PageSize+1
if position < RS.RecordCount then
pagend=position
else
pagend= RS.RecordCount
end if
Response.Write "<P><font color='Navy'><B>数据库查询结果：</B>"
Response.Write "(共有” & RS.RecordCount & "条符合条件的信息，显示" & pagebegin&"-" & pagend&")</font></p>"
Response.Write "<TABLE WIDTH=600 BORDER=1 CELLPADDING=4 CELLSPACING=0 BGCOLOR=#FFFFFF>"
Response.Write "<TR BGCOLOR=#5FB5E2><FONT SIZE=2><TD><B>主题</B></TD><TD><B>用户</B></TD><TD><B>Email</B></TD><TD><B>发布日期</B></TD></FONT><TR BGCOLOR=#FFFFFF>"
'Do while not (RS is nothing)
RowCount = RS.PageSize
Do While Not RS.EOF and rowcount > 0
If n=1 then
Response.Write "<TR BGCOLOR=#FFFFFF>"
ELSE
Response.Write "<TR BGCOLOR=#EEEEEE>"
End If
n=1-n %>
<TD><span style="font-size:9pt"><A href='view.asp?key=<% =RS("ID")%>'><% =RS("subject")%></A></span></td>
<TD><span style="font-size:9pt"><% =RS("name")%></A></span></td>
<TD><span style="font-size:9pt"><a href="mailto:<% =RS("email")%>"><% =RS("email")%>
</a></span> </TD>
<TD><span style="font-size:9pt"><% =RS("postdate")%>
</span> </td>
</TR>
<%
RowCount = RowCount - 1
RS.MoveNext
Loop
'set RS = RS.NextRecordSet
'Loop
Conn.Close
set rs = nothing
set Conn = nothing
%>
</TABLE>
<FORM METHOD=GET ACTION="bbs.asp">
<INPUT TYPE="HIDDEN" NAME="pageno" VALUE="<% =PageNo %>">
<%
if PageNo > 1 Then
response.write "<INPUT TYPE=SUBMIT NAME='ScrollAction' VALUE='上一页'>"
end if
if RowCount = 0 and PageNo <>Total then
response.write "<INPUT TYPE=SUBMIT NAME='ScrollAction' VALUE='下一页'>"
end if
response.write "</FORM>"
End if
%>
<% 'End Sub %>
</body>
</html>
运行结果如图：
<% @ LANGUAGE=VBScript %>
<%
Option Explicit
Dim Cnn,StrCnn
'创建Connection 对象
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("bbs.mdb;")
'使用 Connection 对象的 Open 方法打开数据库bbs.mdb,该数据库用户自己建立
Cnn.Open StrCnn
Response.Write Server.MapPath("bbs.mdb") & "<br>"
Response.Write "数据库连接成功," & Cnn.State & "<br>"
'使用 Connection 对象的 Close 方法关闭连接
Cnn.close
Response.Write "数据库已经关闭," & Cnn.State
'将Connection 对象从内存中删除，以释放资源
Set Cnn = Nothing
%>
运行结果如图
例9.27 循环检索记录，文件名为9-27a.asp和9-27b.asp。先注册文件数据源为Myaa.dsn，使用的数据库为northwind.mdb。
9-27a.asp的内容如下：
<%
strDSN = "FILEDSN=Myaa.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN
//初始化Recordset对象
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT 联系人姓名,公司名称 FROM 客户 WHERE 客户ID= 'FAMIA' "
rsCustomers.Open strSQL,cn
//循环查询，并显示结果
Set objFirstName = rsCustomers("联系人姓名")
Set objLastName = rsCustomers("公司名称")
Do Until rsCustomers.EOF
Response.Write objFirstName & "-------- " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
9-27b.asp的内容如下：
<%
’与9-27a.asp的区别是未建立Connection对象，但结果相同
strDSN = "FILEDSN=Myaa.dsn"
strSQL = "SELECT 联系人姓名,公司名称 FROM 客户 WHERE 客户ID= 'FAMIA' "
Set rsCustomers = Server.CreateObject("ADODB.Recordset")
rsCustomers.Open strSQL,strDSN
Set objFirstName = rsCustomers("联系人姓名")
Set objLastName = rsCustomers("公司名称")
Do Until rsCustomers.EOF
Response.Write objFirstName & "-------- " & objLastName & "<BR>"
rsCustomers.MoveNext
Loop
%>
9-27a.asp和9-27a.asp的运行结果如图：
例9．30本例共有两个文件：test9-30.html和9-30.asp。test9-30.html用来产生向用户收集信息的表单，用户可以指定要查看“图书信息”或“作者信息”。 9-30.asp用来响应用户的请求。
test9-30.html的内容如下：
<html>
<body>
<form method=post action=9-30.asp>
<p>查看客户信息请点击这里
<input type=submit name=button value="客户信息">
</p>
<p>查看产品信息请点击这里
<input type=submit name=button value="产品信息">
</p>
</form>
</body>
</html>
9-30.asp的内容如下：
<%
const adOpenStatic=3
const adLockReadOnly=1
'建立Recordset对象
Set rs=Server.CreateObject("ADODB.Recordset")
'提取表单域“button”的值
button=Request.Form("button")
'设定光标类型为静态
CursorType=adOpenStatic
'设定锁定类型为只读
LockType=adLockReadOnly
'如果用户选择“客户信息”则执行下面的代码
If button="客户信息" Then
source="select * from 客户"
activeConnection="dsn=pubs"
rs.open source,activeConnection,CursorType,LockType
end if
'如果用户选择“产品信息”则执行下面的代码
If button="产品信息" Then
source="select * from 产品"
ActiveConnection="dsn=pubs"
rs.Open source,ActiveConnection,CursorType,LockType
End if
'查询结果以表格的形式输
Response.Write "<table border=3>"
Response.Write "<tr>"
For i=0 to rs.Fields.Count-1
Response.Write "<td>"&Ucase(rs(i).Name)&"</td>"
Next
Response.Write "</tr>"
'输出表格内容
While NOT rs.EOF
Response.Write "<tr>"
For i=0 to rs.Fields.Count-1
Response.Write "<td>"&rs(i).Value&"</td>"
Next
rs.MoveNext
Wend
Response.Write "</table>"
'取消连接
Set rs=nothing
%>
运行结果如图：
例9．31 Recordset访问数据库，文件名为9-31.asp，数据库为ntopsamp.mdb。
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ=" & Server.MapPath("ntopsamp.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs2 = Server.CreateObject("ADODB.Recordset")
SqlStr = "SELECT Avg(价格) As 平均 From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Avg," & rs2("平均")
rs2.Close
SqlStr = "SELECT Count(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Count(价格)," & rs2(0)
rs2.Close
SqlStr = "SELECT Count(*) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Count(*)," & rs2(0)
rs2.Close
SqlStr = "SELECT Sum(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Sum," & rs2(0)
rs2.Close
SqlStr = "SELECT Min(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Min," & rs2(0)
rs2.Close
SqlStr = "SELECT Max(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Max," & rs2(0)
rs2.Close
SqlStr = "SELECT First(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>First(价格)," & rs2(0)
rs2.Close
SqlStr = "SELECT Last(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Last(价格)," & rs2(0)
rs2.Close
SqlStr = "SELECT First(代号) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>First(代号)," & rs2(0)
rs2.Close
SqlStr = "SELECT Last(代号) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Last(代号)," & rs2(0)
rs2.Close
SqlStr = "SELECT StDev(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>StDev," & rs2(0)
rs2.Close
SqlStr = "SELECT Var(价格) From 产品"
rs2.Open SqlStr,conn1,1,1
Response.Write "<BR>Var," & rs2(0)
rs2.Close
conn1.Close
%>
运行结果如图：
例9．32 用户登录验证，文件名为9-32.asp和login.asp。
使用的数据库为info.mdb，其中表：userinfo包含userid、psw、logins、pswnotice、pswanswer、email、qq、name、age、education、occupation、city、address、postnum字段。
login.asp的内容如下：
<html>
<body>
<form name="form1" method="post" action="9-32.asp">
<p>用户名
<input name="userid" type="text" id="userid">
</p>
<p> 口令
<input name="psw" type="password" id="psw">
</p>
<p>
<input type="submit" name="Submit" value="提交">
<input type="reset" name="Submit2" value="重置">
</p>
</form>
</body>
</html>
9-32.asp的内容如下：
<html>
<body>
<%
username=trim(request.Form("userid"))
password=trim(request.Form("psw"))
dim conn
Set conn = Server.CreateObject("ADODB.Connection")
set TmpRs=server.createobject("adodb.recordset")
conn.Open "DBQ=" & Server.MapPath("info.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FILe=MS Access;"
Set TmpRs = Server.CreateObject("ADODB.Recordset")
TmpRs.open "select * from userinfo Where userid='"&username&"';",conn,3,3
%>
<% if tmprs.EOF then %>
<script language="vbscript">
MsgBox "该用户不存在，请先注册!!!!"
location.href="javascript:history.back()"
</script>
<%else if password<>tmprs("psw") then %>
<script language="vbscript">
MsgBox "口令错误,请重新输入密码!!!!!"
location.href="javascript:history.back()"
</script>
<% else
session("username")=username
tmprs("logins")=tmprs("logins")+1
tmprs.update
response.Write tmprs("logins")
tmprs.close
set tmprs=nothing
conn.close
set conn=nothing
end if
end if
%>
</body>
</html>
4．数据库的添加及修改
例9.33 添加及修改数据库记录,文件名为9-33.asp和publish_tiezi.asp。
使用的数据库是info.mdb,其中表userinfo、composition、bbs，字段如下：
composition(topic,text,userid,length,time,owner)
bbs(owner,total,numnew,numtopic)
publish_tiezi.asp的内容：
<html>
<body>
<form name="form1" method="post" action="9-33.asp">
<p>topic
<input name="topic" type="text" id="topic">
</p>
<p>text
<textarea name="text" id="text"></textarea>
</p>
<p>owner
<input name="owner" type="text" id="owner">
</p>
<p>
<input type="submit" name="Submit" value="提交">
<input type="reset" name="Submit2" value="重置">
</p>
</form>
</body>
</html>
9-33.asp的内容：
<% if request("topic")="" then %>
<script language="vbscript">
MsgBox "主题不能为空！！！！！"
location.href="javascript:history.back()"
</script>
<%elseif request("text")="" then %>
<script language="vbscript">
MsgBox "论坛不允许灌水行为！！！！！"
location.href="javascript:history.back()"
</script>
<% else
owner=request("owner")
set cn=server.CreateObject("ADODB.connection")
DBPath=server.MapPath("info.mdb")
cn.open "driver={Microsoft Access Driver (*.mdb)};dbq="& DBPath
set com_rs=server.CreateObject("ADODB.recordset")
set bbs_rs=server.CreateObject("ADODB.recordset")
com_rs.open "Select * from composition;",cn,2,3
'表composition中添加新记录
com_rs.AddNew
com_rs("topic")=request("topic")
com_rs("text")=request("text")
‘com_rs("userid")=session.contents("username")
com_rs("length")=len(trim(request("text")))
com_rs("time")=now()
com_rs("owner")=request("owner")
com_rs.update
com_rs.close
'表bbs中修改记录内容
bbs_rs.open "Select * from bbs where owner='"&owner&"'",cn,3,3
if not bbs_rs then
bbs_rs("total")=bbs_rs("total")+1
bbs_rs("numnew")=bbs_rs("numnew")+1
bbs_rs("numtopic")=bbs_rs("numtopic")+1
bbs_rs.update
endif
response.Write,帖子已发表成功"
bbs_rs.close
end if
%>
此例中的com_rs("userid")=session.contents("username")通过在例9.32的login.asp中session("username")=username获得"username"。
例：不分页显示数据库内容（简单应用）
文件display_db_dsn.asp如下：
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT * FROM message order by ID DESC"
Conn.Open "bbs" '用DSN数据源
RS.open sql,Conn,3,2
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head>
<body>
<table width="75%" border="1">
<tr>
<td><div align="center"> 主题</div></td>
<td><div align="center">留言者</div></td>
<td><div align="center">时间 </div></td>
</tr>
<% i=1
while not rs.eof
%>
<tr>
<td> <%= rs("subject") %> </td>
<td><%= rs("name") %> </td>
<td><%= rs("postdate") %> </td>
</tr>
<% rs.movenext
i=i+1
wend
%>
</table>
<p> </p></body>
</html>
文件display_db_lessdsn.asp如下：
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT * FROM message order by ID DESC"
Conn.Open="DRIVER={Microsoft Access Driver (*.mdb)};DBQ="+server.MapPath("/bbs.mdb")
'用非DSN数据源
RS.open sql,Conn,3,2
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head>
<body>
<table width="75%" border="1">
<tr>
<td><div align="center"> 主题</div></td>
<td><div align="center">留言者</div></td>
<td><div align="center">时间 </div></td>
</tr>
<% i=1
while not rs.eof
%>
<tr>
<td><%= rs("subject") %> </td>
<td><%= rs("name") %> </td>
<td><%= rs("postdate") %> </td>
</tr>
<% rs.movenext
i=i+1
wend
%>
</table>
<p> </p></body>
</html>
例9．34 一个简单的BBS应用程序，文件名为bbs.asp。
它的数据库bbs.mdb中的表“meggase”分别有以下五个字段：
“ID”，每个帖子的自动编号；
“subject”，每个帖子的主题；
“name”，加帖用户的姓名；
“email”，用户的电子邮件地址；
“postdate”，加帖的时间。
数据库的DSN 为“bbs”。
程序如下,
<html>
<head>
<title>bbs</title>
<body>
<% 'Sub ShowList() %>
<%
PgSz=3 '设定开关，指定每一页显示的帖子数目，默认为3
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT * FROM message order by ID DESC"
'查询所有帖子，并按帖子的ID倒序排列
Conn.Open "bbs"
RS.open sql,Conn,1,1
If RS.RecordCount=0 then
response.write "<P><center>对不起，数据库中没有相关信息！</center></P>"
else
RS.PageSize = Cint(PgSz) '设定PageSize属性的值
Total=INT(RS.recordcount / PgSz * -1)*-1 '计算可显示的页面的总数
PageNo=Request("pageno")
if PageNo="" Then
PageNo = 1
else
PageNo=PageNo+1
PageNo=PageNo-1
end if
ScrollAction = Request("ScrollAction")
if ScrollAction = "上一页" Then
PageNo=PageNo-1
end if
if ScrollAction = "下一页" Then
PageNo=PageNo+1
end if
if PageNo < 1 Then
PageNo = 1
end if
n=1
RS.AbsolutePage = PageNo
Response.Write "<CENTER>"
position=RS.PageSize*PageNo
pagebegin=position-RS.PageSize+1
if position < RS.RecordCount then
pagend=position
else
pagend= RS.RecordCount
end if
Response.Write "<P><font color='Navy'><B>数据库查询结果：</B>"
Response.Write "(共有” & RS.RecordCount & "条符合条件的信息，显示" & pagebegin&"-" & pagend&")</font></p>"
Response.Write "<TABLE WIDTH=600 BORDER=1 CELLPADDING=4 CELLSPACING=0 BGCOLOR=#FFFFFF>"
Response.Write "<TR BGCOLOR=#5FB5E2><FONT SIZE=2><TD><B>主题</B></TD><TD><B>用户</B></TD><TD><B>Email</B></TD><TD><B>发布日期</B></TD></FONT><TR BGCOLOR=#FFFFFF>"
'Do while not (RS is nothing)
RowCount = RS.PageSize
Do While Not RS.EOF and rowcount > 0
If n=1 then
Response.Write "<TR BGCOLOR=#FFFFFF>"
ELSE
Response.Write "<TR BGCOLOR=#EEEEEE>"
End If
n=1-n %>
<TD><span style="font-size:9pt"><A href='view.asp?key=<% =RS("ID")%>'><% =RS("subject")%></A></span></td>
<TD><span style="font-size:9pt"><% =RS("name")%></A></span></td>
<TD><span style="font-size:9pt"><a href="mailto:<% =RS("email")%>"><% =RS("email")%>
</a></span> </TD>
<TD><span style="font-size:9pt"><% =RS("postdate")%>
</span> </td>
</TR>
<%
RowCount = RowCount - 1
RS.MoveNext
Loop
'set RS = RS.NextRecordSet
'Loop
Conn.Close
set rs = nothing
set Conn = nothing
%>
</TABLE>
<FORM METHOD=GET ACTION="bbs.asp">
<INPUT TYPE="HIDDEN" NAME="pageno" VALUE="<% =PageNo %>">
<%
if PageNo > 1 Then
response.write "<INPUT TYPE=SUBMIT NAME='ScrollAction' VALUE='上一页'>"
end if
if RowCount = 0 and PageNo <>Total then
response.write "<INPUT TYPE=SUBMIT NAME='ScrollAction' VALUE='下一页'>"
end if
response.write "</FORM>"
End if
%>
<% 'End Sub %>
</body>
</html>
运行结果如图：
