问题描述

查询SQL数据只显示第一条不能全部显示

现住客人房费一表

<%
DataServer = "lx"

DataUser = "sa"

DataBaseName = "hy2006qt"
DataBasePsw = ""

Set conn = Server.CreateObject("ADODB.Connection")
ConnStr="driver={SQL Server};server="&dataserver&";UID="&datauser&";PWD="&databasepsw&";Database="&databasename
conn.open ConnStr
Set rs=Server.CreateObject("ADODB.RecordSet")

dim rs,sql,scount
//sql="select * from Trans WHERE (YEAR([Date]) = YEAR({ fn NOW() })) AND (MONTH([Date]) = MONTH({ fn NOW() })) AND (DAY([Date]) = DAY({ fn NOW() }))"
sql="select * from Trans WHERE (Room > 301) AND (Room < 711)"
//sql="select * from Trans WHERE (TransCode <= 2)"
//sql="select * from Trans WHERE (Amount < 80)"
//sql="select * from Trans WHERE (Amount > 500)"
//sql="SELECT m.* FROM Trans m INNER JOIN (SELECT date, room, amount, transcode, COUNT(*) cnt FROM trans WHERE date = CONVERT(varchar(100), GETDATE(), 23) AND transcode <= 2 GROUP BY date, room, amount, transcode) t ON m.Room = t.room AND m.Amount = t.amount AND m.TransCode = t.transcode AND m.[Date] = t.[date] WHERE (t.cnt > 1)"
//BOF 或 EOF 中有一个是“真”，或者当前的记录已被删除，所需的操作要求一个当前的记录。/hycom/hyquery/foom.asp，行 0
//sql="select distinct(trans),date,room,transcode,amount feom indent where room=0 order by id desc"

rs.Open sql,conn,1,1
scount = rs.recordcount

set aa=conn.execute(sql)
set bb=conn.execute(sql)
set cc=conn.execute(sql)
set dd=conn.execute(sql)
set ee=conn.execute(sql)
set ff=conn.execute(sql)

response.write "

"
response.write "
"
response.write "

"
response.write "

"
response.write "

"
response.write "

"
response.write "

"
response.write "

"

%>

//<%do while not rs.eof%>

<%

response.write "

"
response.write "

"
response.write "

"
response.write "

"
response.write "

"
response.write "

"
response.write "

"

rs.movenext
loop
rs.close
set rs=nothing
%>

<%
response.write "

日期	房号	账项	房费小于80	房费大于500
"&aa("date")&"	"&bb("room")&"	"&cc("transcode")&"	"&dd("amount")&"	"&scount&"

"
%>

注：sql="select * from Trans WHERE (Room > 301) AND (Room < 711)"
这个查询的结果只显示第一条，怎么修改才能全部显示。

解决方案

把where条件去掉，估计只有一天满足

解决方案二：

你的scount值是多少？还有，你的aa,bb,cc,dd,ee,ff都是同一个SQL,为什么要查询那么多遍?

解决方案三：

目测asp中根本不能使用双杠的注释，你的代码到底是什么。你的sql是否正确。
sql="select * from Trans WHERE (Room > 301) AND (Room < 711)"
你的数据库中trans中room在301~711的记录到底有没有