Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/O Code)
 Thanks Mod Revisited		  New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 4

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 03 April 2015 :  10:18:13  Show Profile  Reply with Quote
OK. There's a SQL 'GROUP BY' syntax error on lines 20/50 (in red) below. It works fine with MS Access, but MS SQL gets the hiccups. Can anyone spot a problem?


<!--#INCLUDE FILE="config.asp" -->
<!--#INCLUDE FILE="inc_header_short.asp" -->
<%
'	##	Set value for intReaders below.
	'	0 = Author only
	'	1	= Author, Admins
	'	2 = Author, Admins, Moderators
	'	3	= All members
	'	4 = Anyone

intTYReaders = 1 : intTYAllowed=0
If Request("RID") > "0" Then
	strSqlTY = "SELECT R_AUTHOR FROM " & strTablePrefix & "REPLY WHERE REPLY_ID=" & Request("RID")
	Set rsTY=my_Conn.Execute(strSqlTY)
	If Not rsTY.EOF Then
		intRID=rsTY("R_AUTHOR")
		rsTY.Close
	End If
	Set rsTY = Nothing
	strSqlTY = "SELECT MEMBER_ID FROM (SELECT DISTINCT MEMBER_ID, TOPIC_ID, REPLY_ID FROM " & strTablePrefix & "THANKS WHERE TOPIC_ID=" & Request("TID") & " AND REPLY_ID=" & Request("RID") & ") GROUP BY MEMBER_ID, TOPIC_ID, REPLY_ID;"
	Select Case intTYReaders
		Case 0
			If (MemberID = intRID) Then
				Call TYP
			End If
		Case 1
			If (MemberID = intRID) Or (mLev > 2) Then
				Call TYP
			End If
		Case 2
			If (MemberID = intRID) Or (mLev > 1) Then
				Call TYP
			End If
		Case 3
			If (MemberID = intRID) Or (mLev > 0) Then
				Call TYP
			End If
		Case Else
			Call TYP
	End Select
End If
If Request("RID") = "0" Then
	strSqlTY = "SELECT T_AUTHOR FROM " & strTablePrefix & "TOPICS WHERE TOPIC_ID=" & Request("TID")
	Set rsTY=my_Conn.Execute(strSqlTY)
	If Not rsTY.EOF Then
		intTID=rsTY("T_AUTHOR")
		rsTY.Close
	End If
	Set rsTY = Nothing
	strSqlTY = "SELECT MEMBER_ID FROM (SELECT DISTINCT MEMBER_ID, TOPIC_ID, REPLY_ID FROM " & strTablePrefix & "THANKS WHERE TOPIC_ID=" & Request("TID") & " AND REPLY_ID=0) GROUP BY MEMBER_ID, TOPIC_ID, REPLY_ID;"
	Select Case intTYReaders
		Case 0
			If (MemberID = intTID) Then
				Call TYP
			End If
		Case 1
			If (MemberID = intTID) Or (mLev > 2) Then
				Call TYP
			End If
		Case 2
			If (MemberID = intTID) Or (mLev > 1) Then
				Call TYP
			End If
		Case 3
			If (MemberID = intTID) Or (mLev > 0) Then
				Call TYP
			End If
		Case Else
			Call TYP
	End Select
End If

Sub TYP
	Set rsTY=my_Conn.Execute(strSqlTY)
	If Not rsTY.EOF Then
		Response.Write	"<table align=""0"" border=""0"" cellpadding=""4"" cellspacing=""0"" bgColor=""" & strPageBGColor & """>" & vbNewLine & _
			"	<tr valign=""middle"">" & vbNewLine
		rsTY.MoveFirst
		intAvatDis=0
		Do While Not rsTY.EOF
			intAvatDis = intAvatDis + 1
			If intAvatDis=5 Then
				intAvatDis = 0
				Response.Write	"</tr><tr valign=""middle"">"
			End If
			Response.Write	"<td align=""center"">"
			strSqlTYM = "SELECT DISTINCT M_NAME FROM " & strMemberTablePrefix & "MEMBERS WHERE MEMBER_ID = " & rsTY("MEMBER_ID")
			Set rsTYM=my_Conn.Execute(strSqlTYM)
			If Not rsTYM.EOF Then
				Response.Write	profilelink(rsTYM("M_NAME"),rsTY("MEMBER_ID"))
				rsTYM.Close
			End If
			Set rsTYM = Nothing
			rsTY.MoveNext
			If Not rsTY.EOF Then
				Response.Write	", "
			Else
				Response.Write	"</td>"
			End If
		Loop
		rsTY.Close
	End If
	Set rsTY=Nothing
	intTYAllowed=1
End Sub
%>
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 03 April 2015 :  13:15:38  Show Profile  Send ruirib a Yahoo! Message  Reply with Quote
In SQL Server, fields in a Group By clause need to be included in the SELECT clause, as well. There are some exceptions to this, but they don't apply to your case.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 03 April 2015 :  16:57:10  Show Profile  Reply with Quote
Well, I previously only had group by member_id since that was the only selected field ... that didn't work, same error. So I guess I need to put all three fields in the first select as well as in the distinct select?
Go to Top of Page

ruirib
Snitz Forums Admin

Portugal
26364 Posts
Posted - 03 April 2015 :  17:44:47  Show Profile  Send ruirib a Yahoo! Message  Reply with Quote
quote:
Originally posted by Carefree

Well, I previously only had group by member_id since that was the only selected field ... that didn't work, same error. So I guess I need to put all three fields in the first select as well as in the distinct select?

Yes, you need to have all three fields in the SELECT clause.The fields are needed in the query that has the GROUP BY clause but here, since you are SELECT from the subquery, the subquery needs the three fields as well, or they wouldn't be in the main query, to start with.

With a single Group By field, and that field present in the SELECT clause, I don't see why the query would fail.

Snitz 3.4 Readme | Like the support? Support Snitz too
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 03 April 2015 :  22:51:08  Show Profile  Reply with Quote
Well, no change, still failed. Back to the drawing board.

Redid the routines in "topic.asp" and the "pop_thanks.asp" on SnitzBitz. They will now work with all.
Edited by - Carefree on 04 April 2015 00:06:21
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 07 April 2015 :  16:23:30  Show Profile  Reply with Quote
Heard that someone experienced an error. Please, either post a link to your "topic.asp" in .txt format for me to see, or reply here and surround your "topic.asp" with [scrollcode][/scrollcode].

Resolved. Had code blocks in wrong place.
Edited by - Carefree on 08 April 2015 11:03:04
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000 Version 3.4.07