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.
精彩评论