| Author |
 Topic  |
|
Panhandler
Average Member
USA
783 Posts |
 Posted - 09 February 2007 : 11:30:23
|
I did both a Snitz search and a Google search on this.
It appears to be a database related error. The Google results are way over my head, so. . .
This is from Snitz 3.4.06, MySql db and the Calendar Mod.
Results that I've found are that by removing the code in red (see below) the program Will stop throwing the error.
However, the events list will not be in proper sequence (by date).
Here is the code:
strSql = strSql & _
"GROUP BY T.TOPIC_ID, T.FORUM_ID, T.CAT_ID, T.T_SUBJECT, T.T_AUTHOR, T.T_STATUS, " & _
"E.EVENT_DATE, C.CAT_MODERATION, F.F_MODERATION " & _
"ORDER BY min(E2.EVENT_DATE) Asc, T.TOPIC_ID Asc, E.EVENT_DATE Asc "
Removing the red code will stop the error:
"Invalid use of group function"
You can see it here: Events List
Everything else works.
|
"5-in-1 Snitz Common Expansion Pack" - five popular mods packaged for easy install
". . .on a mote of dust, suspended in a sunbeam. . ."
HarborClassifieds
Support Snitz Forums
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 09 February 2007 : 11:59:34
|
I think you will find that when using group, any item in your oder clause must be in the group by, should it be
ORDER BY min(E.EVENT_DATE) rather than (E2.EVENT_DATE) ? |
 |
|
|
Panhandler
Average Member
USA
783 Posts |
Posted - 09 February 2007 : 12:26:07
|
I made the suggested correction:
ORDER BY min(E.EVENT_DATE) rather than (E2.EVENT_DATE)
The same error occurs.
Going to the line number 1155 that the error specifies, it reads:
set rs = Server.CreateObject("ADODB.Recordset")
rs.open StrSql, My_conn
My zero sum knowledge of databases isn't helping any either.
|
"5-in-1 Snitz Common Expansion Pack" - five popular mods packaged for easy install
". . .on a mote of dust, suspended in a sunbeam. . ."
HarborClassifieds
Support Snitz Forums
|
|
|
|
JJenson
Advanced Member
USA
2121 Posts |
Posted - 09 February 2007 : 13:34:35
|
I don't know if this is the case here but usually when have gotten that code its cause I have misplaced some code. Usually it has referred to the active users mod. But like I said I am not sure that applies here just got that same error when that has happend to me. Hope that helps.
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Panhandler
Average Member
USA
783 Posts |
Posted - 09 February 2007 : 14:48:42
|
quote:
Originally posted by ruirib
Can you post the entire SQL statement?
If you mean the error code that it is giving, yes:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[MySQL][ODBC 3.51 Driver][mysqld-4.1.19-community-nt]Invalid use of group function
/campfire/cal.asp, line 1155
|
"5-in-1 Snitz Common Expansion Pack" - five popular mods packaged for easy install
". . .on a mote of dust, suspended in a sunbeam. . ."
HarborClassifieds
Support Snitz Forums
|
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 09 February 2007 : 15:59:39
|
| no I think he meant can you show us all the code that makes up the sql rather than just the group by part |
|
|
|
Panhandler
Average Member
USA
783 Posts |
Posted - 09 February 2007 : 16:49:59
|
Ah. . .
'Get the events from the database
strSql = "SELECT T.TOPIC_ID, " & _
"T.FORUM_ID, " & _
"T.CAT_ID, " & _
"T.T_SUBJECT, " & _
"T.T_AUTHOR, " & _
"T.T_STATUS, " & _
"E.EVENT_DATE, " & _
"C.CAT_MODERATION, " & _
"F.F_MODERATION " & _
"FROM (((" & _
strTablePrefix & "TOPICS AS T " & _
"INNER JOIN " & _
strTablePrefix & "CAL_EVENTS AS E ON T.TOPIC_ID = E.TOPIC_ID) " & _
"INNER JOIN " & _
strTablePrefix & "CATEGORY AS C ON T.CAT_ID = C.CAT_ID) " & _
"INNER JOIN " & _
strTablePrefix & "FORUM AS F ON T.FORUM_ID = F.FORUM_ID) " & _
"INNER JOIN " & _
strTablePrefix & "CAL_EVENTS AS E2 ON E.TOPIC_ID = E2.TOPIC_ID "
Select Case strDisplay
Case "future"
strSql = strSql & "WHERE E.EVENT_DATE >= '" & DateToStr(DateValue(strForumTimeAdjust)) & "' "
Case "past"
strSql = strSql & "WHERE E.EVENT_DATE < '" & DateToStr(DateValue(strForumTimeAdjust)) & "' "
end Select
if intRqCalForumID <> 0 then strSQL = strSQL & _
"AND T.FORUM_ID = " & intRqCalForumID & " "
strSql = strSql & _
"GROUP BY T.TOPIC_ID, T.FORUM_ID, T.CAT_ID, T.T_SUBJECT, T.T_AUTHOR, T.T_STATUS, " & _
"E.EVENT_DATE, C.CAT_MODERATION, F.F_MODERATION " & _
"ORDER BY min(E.EVENT_DATE) Asc, T.TOPIC_ID Asc, E.EVENT_DATE Asc "
set rs = Server.CreateObject("ADODB.Recordset")
rs.open StrSql, My_conn
|
"5-in-1 Snitz Common Expansion Pack" - five popular mods packaged for easy install
". . .on a mote of dust, suspended in a sunbeam. . ."
HarborClassifieds
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Panhandler
Average Member
USA
783 Posts |
Posted - 09 February 2007 : 17:48:44
|
When done as instructed,
It doesn't crash the whole forum/calendar. . .see here
Instead of the "list of events", it shows this code:
SELECT T.TOPIC_ID, T.FORUM_ID, T.CAT_ID, T.T_SUBJECT, T.T_AUTHOR, T.T_STATUS, E.EVENT_DATE, C.CAT_MODERATION, F.F_MODERATION FROM (((FORUM_TOPICS AS T INNER JOIN FORUM_CAL_EVENTS AS E ON T.TOPIC_ID = E.TOPIC_ID) INNER JOIN FORUM_CATEGORY AS C ON T.CAT_ID = C.CAT_ID) INNER JOIN FORUM_FORUM AS F ON T.FORUM_ID = F.FORUM_ID) INNER JOIN FORUM_CAL_EVENTS AS E2 ON E.TOPIC_ID = E2.TOPIC_ID WHERE E.EVENT_DATE >= '20070209000000' GROUP BY T.TOPIC_ID, T.FORUM_ID, T.CAT_ID, T.T_SUBJECT, T.T_AUTHOR, T.T_STATUS, E.EVENT_DATE, C.CAT_MODERATION, F.F_MODERATION ORDER BY min(E.EVENT_DATE) Asc, T.TOPIC_ID Asc, E.EVENT_DATE Asc |
"5-in-1 Snitz Common Expansion Pack" - five popular mods packaged for easy install
". . .on a mote of dust, suspended in a sunbeam. . ."
HarborClassifieds
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 February 2007 : 18:49:09
|
I am sorry, but I fail to see the need for using a Group By in that statement, since no aggregate function is used. Why would you group if you don't aggregate?!
What is that page supposed to do? |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Panhandler
Average Member
USA
783 Posts |
Posted - 09 February 2007 : 19:08:21
|
That page is suppossed to take all the calendar events and list them sequentially.
I assume that the as each event is posted in the calendar it goes into the database with some kind of date identifier.
When the "Events List" is clicked, it should retrieve all the calendar events and put them into cronological order, regardless of what order they were posted.
By removing the min(E2.EVENT)DATE) Asc, from the code, the application will retrieve all dates and list them in the order that they were posted! When in fact, they should be listed in the order by which they will occur.
You can see my little test list in it's disordered form with I remove that little bit of min(E2.EVENT)DATE) Asc, here: See Events List |
"5-in-1 Snitz Common Expansion Pack" - five popular mods packaged for easy install
". . .on a mote of dust, suspended in a sunbeam. . ."
HarborClassifieds
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 February 2007 : 19:31:52
|
Change the code to this:
'Get the events from the database
strSql = "SELECT T.TOPIC_ID, " & _
"T.FORUM_ID, " & _
"T.CAT_ID, " & _
"T.T_SUBJECT, " & _
"T.T_AUTHOR, " & _
"T.T_STATUS, " & _
"E.EVENT_DATE, " & _
"C.CAT_MODERATION, " & _
"F.F_MODERATION " & _
"FROM (((" & _
strTablePrefix & "TOPICS AS T " & _
"INNER JOIN " & _
strTablePrefix & "CAL_EVENTS AS E ON T.TOPIC_ID = E.TOPIC_ID) " & _
"INNER JOIN " & _
strTablePrefix & "CATEGORY AS C ON T.CAT_ID = C.CAT_ID) " & _
"INNER JOIN " & _
strTablePrefix & "FORUM AS F ON T.FORUM_ID = F.FORUM_ID) " & _
"INNER JOIN " & _
strTablePrefix & "CAL_EVENTS AS E2 ON E.TOPIC_ID = E2.TOPIC_ID "
Select Case strDisplay
Case "future"
strSql = strSql & "WHERE E.EVENT_DATE >= '" & DateToStr(DateValue(strForumTimeAdjust)) & "' "
Case "past"
strSql = strSql & "WHERE E.EVENT_DATE < '" & DateToStr(DateValue(strForumTimeAdjust)) & "' "
end Select
if intRqCalForumID <> 0 then strSQL = strSQL & _
"AND T.FORUM_ID = " & intRqCalForumID & " "
strSql = strSql & "ORDER BY E.EVENT_DATE Asc, T.TOPIC_ID Asc"
set rs = Server.CreateObject("ADODB.Recordset")
rs.open StrSql, My_conn
Should do what you want. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Panhandler
Average Member
USA
783 Posts |
Posted - 09 February 2007 : 19:34:32
|
Never mind. . .I finally got it.
Thanks for making me look, and look, and look.
Simply reversing the
E.EVENT_DATE Asc, T.TOPIC_ID Asc in
"ORDER BY min(E2.EVENT_DATE) Asc, T.TOPIC_ID Asc, E.EVENT_DATE Asc "
and removing the min(E2.EVENT_DATE) Asc solved the problem.
I don't know why it work. . .it just works.
 |
"5-in-1 Snitz Common Expansion Pack" - five popular mods packaged for easy install
". . .on a mote of dust, suspended in a sunbeam. . ."
HarborClassifieds
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Panhandler
Average Member
USA
783 Posts |
|
|
Topic |
|
Topic Locked
