开发者

SQL Server paging error

In my recent question, I asked for paging methods in SQL Server. Reading an article someone posted there, I came up with the following code. Everything seems correct to me but I receive the following error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

 PAGEID = Request.QueryString("PAGEID")

 If PAGEID = "" or PAGEID = 0 Then
  PAGEID = 1
 End If

 RecordsPerPage = 1
 RecordsPageSize = PAGEID * RecordsPerPage

 SQL = "SELECT * FROM ( SELECT I.IMAGESID, I.IMAGESNAME, I.IMAGESSMURL, ROW_NUMBER() OVER (ORDER BY I.IMAGESID) As Row"
 SQL = SQL & " FROM IMAGES I"
 SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &") ORDER BY I.IMAGESID DESC"
 Set objImages = objConn.Execute(SQL)
开发者_JAVA技巧
%>
                <div class="row">
                    <label for="Images">Images</label>
<% Do While Not objImages.EOF %>
     <img src="<%=objImages("IMAGESSMURL")%>" alt="<%=objImages("IMAGESNAME")%>" border="0" />
<%
 objImages.MoveNext
 Loop
%>


Try adding another ") A" before the where and change the I.imagesid for A.imagesid:

  SQL = SQL & " FROM IMAGES I ) A"
  SQL = SQL & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &") ORDER BY A.IMAGESID DESC"

The error message is telling you that you cannot use an order by in a subquery.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜