The Forum has been Updated
The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.
I am on SQL Server 2008 so offset doesn't work. I want to get the last 15 replies to a topic.
I keep getting this error....
Microsoft SQL Server Native Client 10.0 error '80040e14'
Invalid column name 'ROW'.
Everything I've read seems to say it should work. Any ideas?
Code:
ReplyCountOffset = (Topic_Replies - strPageSize)
' Response.Write ReplyCountOffset
'## Forum_SQL
strSql = "SELECT ROW_NUMBER() OVER(ORDER BY R.DATE) AS ROW, M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_STATUS"
strSql = strSql & ", M.M_TITLE, M.MEMBER_ID, M.M_HOMEPAGE, M.M_LEVEL, M.M_POSTS, M.M_COUNTRY, M.M_AVATAR_URL, M_SUPPORTER, M_MOD_NOTE"
strSql = strSql & ", R.REPLY_ID, R.FORUM_ID, R.R_AUTHOR, R.TOPIC_ID, R.R_MESSAGE, R.R_LAST_EDIT"
strSql = strSql & ", R.R_LAST_EDITBY, R.R_SIG, R.R_STATUS, R.R_DATE, R.R_MOD_NOTE"
if CanShowSignature = 1 then
strSql = strSql & ", M.M_SIG"
end if
strSql2 = " FROM " & strMemberTablePrefix & "MEMBERS M, " & strActivePrefix & "REPLY R "
strSql3 = " WHERE M.MEMBER_ID = R.R_AUTHOR AND ROW > " & ReplyCountOffset
strSql3 = strSql3 & " AND R.TOPIC_ID = " & Topic_ID & " "
' DEM --> if not a Moderator, all unapproved posts should not be viewed.
if AdminAllowed = 0 then
strSql3 = strSql3 & " AND (R.R_STATUS < "
if Moderation = "Y" then
' Ignore unapproved/rejected posts
strSql3 = strSql3 & "2"
else
' Ignore any previously rejected topic
strSql3 = strSql3 & "3"
end if
strSql3 = strSql3 & " OR R.R_AUTHOR = " & MemberID & ")"
end if
I keep getting this error....
Microsoft SQL Server Native Client 10.0 error '80040e14'
Invalid column name 'ROW'.
Everything I've read seems to say it should work. Any ideas?
Posted
Your problem is you can't use an aliased column name in a where clause, so you can't do "WHERE M.MEMBER_ID = R.R_AUTHOR AND ROW > " & ReplyCountOffset
you SQL needs to follow this pattern (it should work in 2008)
you SQL needs to follow this pattern (it should work in 2008)
Code:
WITH MemberReplies AS
(
SELECT ROW_NUMBER() OVER (ORDER BY R.DATE) AS RowNumber …. Other fields here
FROM FORUM_MEMBERS M JOIN FORUM_REPLY R ON R.R_AUTHOR = M.MEMBER_ID
)
SELECT *
FROM MemberReplies
WHERE RowNumber > ReplyCountOffset
Posted
I'll give it try, thank you!