Question

asp按条件查询数据库和输出查询结果

数据库: data.mdb, 表：test 字段：id, username, message, email

asp 代码如下（此asp的作用是读取数据库并生成xml）

现在希望增加一些功能：asp查询有字符"kiming"的数据，并以20个结果输出内容（也就是说，如果符合条件的数据少于20条，输出全部查询结果，如果大于20条，则只输出前面的20条数据）。

asp代码如下：

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("data.mdb")
Set rs = server.CreateObject("ADODB.RecordSet")
sql="select * from test"
rs.open sql,conn,1,1
'sql.rs.open "SELECT * FROM test",sql,Conn,1,1
Response.ContentType = "text/xml"
Response.Expires = -1
Response.Write("<?xml version='1.0' encoding='gb2312' standalone='yes' ?>")
Response.Write("<list>")
Response.Write("<FolderList>")
do while not rs.eof
Response.Write("<Folder USERNAME='"&rs("username")&"' MESSAGE='"&rs("message")&"' EMAIL='"&rs("email")&"' />")
rs.movenext
loop
Response.Write("</FolderList>")
Response.Write("</list>")
if Conn.state<>0 then Conn.close
set Conn=nothing
if rs.state<>0 then rs.close
Set rs = Nothing
%>

Answer 1

<!--#include file="conn.asp"-->
<%
set rs=Server.CreateObject("ADODB.RecordSet")
sql="select top 20 * from test where username like '%kiming%' order by id desc"
rs.Open sql,conn,1,3
Response.ContentType = "text/xml"
Response.Expires = -1
Response.Write("<?xml version='1.0' encoding='gb2312' standalone='yes' ?>")
Response.Write("<list>")
Response.Write("<FolderList>")
do while not rs.eof
Response.Write("<Folder USERNAME='"&rs("username")&"' MESSAGE='"&rs("message")&"' EMAIL='"&rs("email")&"' />")
rs.movenext
loop
Response.Write("</FolderList>")
Response.Write("</list>")
if Conn.state<>0 then Conn.close
set Conn=nothing
if rs.state<>0 then rs.close
Set rs = Nothing
%>

Answer 2

sql="select top 20 * from test where message like '%kiming%' order by id desc"

Answer 3

sql="select top 20 * from test"