28,406
社区成员
发帖
与我相关
我的任务
分享SQL查询语句按时间排列出错,order by addtime desc这一句话后就出错
SQL查询语句按时间排列出错,为什么我加上order by addtime desc这一句话后就出错,不加很正常但是排列时候更新的文章排在了后面不能够降序排列。从新的日期往后列表。
这样一个查询语句sql="Select id,title,addtime from [Inemd_Info] where checked=1 order by addtime desc"
为什么我加上order by addtime desc这一句话后就出错Microsoft OLE DB Provider for ODBC Drivers '80040e21'
ODBC 驱动程序不支持所需的属性。
\InfoList.asp, line 276
源码如下是存储过程
<!--#include file="common/conn.asp"-->
<!--#include file="common/NoSqlHack.asp"-->
<%
dim CurrentPage
if isInteger(Request("page")) then
CurrentPage=cint(Request("page"))
else
CurrentPage=1
end if
if isInteger(Request("Classid")) then
Classid=cint(Request("Classid"))
else
Classid=0
end if
if isInteger(Request("CityId")) then
CityId=cint(Request("CityId"))
else
CityId=0
end if
if Classid>0 then
Title=getClassName(Classid)
else
Title="分类检索"
end if
if Cityid>0 then
CityName=getCityName(Cityid)
end if
if Cityname<>"" then Title=Title&" ("&CityName&")"
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<link href="css/css.css" rel="stylesheet" type="text/css" />
<title><%=title%> - </title>
<style type="text/css">
<!--
.STYLE17 {color: #55A0FF}
.ddate{ float:right}
-->
</style>
</head>
<body>
<!--#include file="Head.asp"-->
<div id="body_all">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="8"></td>
</tr>
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="18%" valign="top"><table width="203" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top"></td>
</tr>
</table></td>
<td colspan="2" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0" style="margin-top:3px;">
<tr>
<td colspan="2"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="2%"> </td>
<td width="98%" height="25" bgcolor="#CCCCCC" align="left"> 当前位置：<a class="d"
href="Index.asp" target="_self">网站首页</a> >> <span
class="b"><a href="InfoList.asp?Classid=<%=Classid%>" class="d"><%=title%></a></span></td>
</tr>
</table></td>
</tr>
<tr>
<td colspan="2" valign="top"><table width="98%" border="0" align="right" cellpadding="0" cellspacing="0" bgcolor="#F9F9F9">
<tr>
<td><table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="85%" align="left" style="line-height:190%">
<ul>
<%
sql="Select id,title,addtime from [Inemd_Info] where checked=1 order by addtime desc"
if classid>0 then
sql=sql &" and SmallClass="&ClassId
end if
if Cityid>0 then
sql=sql &" and CityId="&Cityid
end if
set rs=server.CreateObject("Adodb.recordset")
rs.open sql,conn,1,1
if rs.eof then
total=0
echo "<tr><td><span class=red>暂无相关信息...</span></td></tr>"
else
maxperpage=35
rs.pagesize=maxperpage
total=rs.recordcount
if CurrentPage<1 then CurrentPage=1
if CurrentPage>rs.pagecount then CurrentPage=rs.pagecount
rs.absolutepage=CurrentPage
for i=1 to maxperpage
%>
<li><span class="ddate">(<%=formatdatetime(rs("addtime"),2)%>)</span>· <a href="InfoShow.asp?id=<%=rs("id")%>" class="b" target="_blank" ><%=cutstr(rs("title"),50)%></a></li>
<%
rs.movenext
if rs.eof then exit for
next
end if
rs.close
set rs=nothing
%>
</ul></td>
<td width="14%"> </td>
<td width="1%"> </td>
</tr>
</table>
<p> </p></td>
</tr>
<tr>
<td align="left" valign="middle"><table width="86%" height="25" border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="left" valign="middle" bgcolor="#E6E6E6">
<%
if total>0 then
Call showpage(total, maxperpage,CurrentPage,true,"条信息")
end if
%>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr>
</tr>
</table></td>
</tr>
</table>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table>
</div>
<!--#include file="Foot.asp"-->
</body>
</html>
这样一个查询语句sql="Select id,title,addtime from [Inemd_Info] where checked=1 order by addtime desc"
为什么我加上order by addtime desc这一句话后就出错Microsoft OLE DB Provider for ODBC Drivers '80040e21'
ODBC 驱动程序不支持所需的属性。
\InfoList.asp, line 276
源码如下是存储过程
<!--#include file="common/conn.asp"-->
<!--#include file="common/NoSqlHack.asp"-->
<%
dim CurrentPage
if isInteger(Request("page")) then
CurrentPage=cint(Request("page"))
else
CurrentPage=1
end if
if isInteger(Request("Classid")) then
Classid=cint(Request("Classid"))
else
Classid=0
end if
if isInteger(Request("CityId")) then
CityId=cint(Request("CityId"))
else
CityId=0
end if
if Classid>0 then
Title=getClassName(Classid)
else
Title="分类检索"
end if
if Cityid>0 then
CityName=getCityName(Cityid)
end if
if Cityname<>"" then Title=Title&" ("&CityName&")"
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<link href="css/css.css" rel="stylesheet" type="text/css" />
<title><%=title%> - </title>
<style type="text/css">
<!--
.STYLE17 {color: #55A0FF}
.ddate{ float:right}
-->
</style>
</head>
<body>
<!--#include file="Head.asp"-->
<div id="body_all">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="8"></td>
</tr>
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="18%" valign="top"><table width="203" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top"></td>
</tr>
</table></td>
<td colspan="2" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0" style="margin-top:3px;">
<tr>
<td colspan="2"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="2%"> </td>
<td width="98%" height="25" bgcolor="#CCCCCC" align="left"> 当前位置：<a class="d"
href="Index.asp" target="_self">网站首页</a> >> <span
class="b"><a href="InfoList.asp?Classid=<%=Classid%>" class="d"><%=title%></a></span></td>
</tr>
</table></td>
</tr>
<tr>
<td colspan="2" valign="top"><table width="98%" border="0" align="right" cellpadding="0" cellspacing="0" bgcolor="#F9F9F9">
<tr>
<td><table width="96%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="85%" align="left" style="line-height:190%">
<ul>
<%
sql="Select id,title,addtime from [Inemd_Info] where checked=1 order by addtime desc"
if classid>0 then
sql=sql &" and SmallClass="&ClassId
end if
if Cityid>0 then
sql=sql &" and CityId="&Cityid
end if
set rs=server.CreateObject("Adodb.recordset")
rs.open sql,conn,1,1
if rs.eof then
total=0
echo "<tr><td><span class=red>暂无相关信息...</span></td></tr>"
else
maxperpage=35
rs.pagesize=maxperpage
total=rs.recordcount
if CurrentPage<1 then CurrentPage=1
if CurrentPage>rs.pagecount then CurrentPage=rs.pagecount
rs.absolutepage=CurrentPage
for i=1 to maxperpage
%>
<li><span class="ddate">(<%=formatdatetime(rs("addtime"),2)%>)</span>· <a href="InfoShow.asp?id=<%=rs("id")%>" class="b" target="_blank" ><%=cutstr(rs("title"),50)%></a></li>
<%
rs.movenext
if rs.eof then exit for
next
end if
rs.close
set rs=nothing
%>
</ul></td>
<td width="14%"> </td>
<td width="1%"> </td>
</tr>
</table>
<p> </p></td>
</tr>
<tr>
<td align="left" valign="middle"><table width="86%" height="25" border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="left" valign="middle" bgcolor="#E6E6E6">
<%
if total>0 then
Call showpage(total, maxperpage,CurrentPage,true,"条信息")
end if
%>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr>
</tr>
</table></td>
</tr>
</table>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table>
</div>
<!--#include file="Foot.asp"-->
</body>
</html>
...全文
请发表友善的回复…
发表回复
ayouori 2009-10-28
- 打赏
- 举报
You are welcome!
xf1927chy 2009-10-28
- 打赏
- 举报
谢谢朋友ayouori，你的答案是正确的，问题解决了，实在没有分，给不了你的，再这里万分感谢你的帮助。我的注册的200分不知道怎么就送不了你，好像只能下载分。
ayouori 2009-10-28
- 打赏
- 举报
把开始sql 里的order by addtime desc去掉,
在
rs.open sql,conn,1,1 这一行前加上一行
sql =sql & " order by addtime desc"
在
rs.open sql,conn,1,1 这一行前加上一行
sql =sql & " order by addtime desc"
xf1927chy 2009-10-28
- 打赏
- 举报
试了一下把你order by的拼接写在 set rs=server之前应该就没有问题了，是没有问题，但是不能分类检索，一下列表了所有的值。
xf1927chy 2009-10-28
- 打赏
- 举报
没有明白，能不能说清楚一点啊
gingerkang 2009-10-28
- 打赏
- 举报
你不能在open之前检查一下完整的sql的值是什么吗？
order by都是放在最后的，你有条件的话还会拼接，这样能正确吗？
把你order by的拼接写在 set rs=server之前应该就没有问题了
order by都是放在最后的，你有条件的话还会拼接，这样能正确吗？
把你order by的拼接写在 set rs=server之前应该就没有问题了
