| Author |
 Topic  |
|
|
Lac0niC
New Member
Turkey
78 Posts |
 Posted - 25 March 2005 : 13:47:19
|
I have following query to update database. But it's not complete.
1.) If FORUM_MEMBERS.M_AGE value is empty that user will be added to all forums's access list. Big problem. =)
2.) If user is exist in FORUM_ALLOWED_MEMBERS table, this query duplicates FORUM_ID for that MEMBER_ID. Another problem.
3.) I want if M_AGE is (ie) 10, that user should see forums where M_SUBJECTs are 7,8,9,10,11,12,13 (I mean +3 and -3)
4.) This query should be processed for members that registered after a special date.
If u can help about one of these, thank you in advance!..
INSERT INTO
FORUM_ALLOWED_MEMBERS
(
Member_ID,
Forum_ID
)
SELECT
FORUM_MEMBERS.Member_ID,
FORUM_FORUM.Forum_ID
FROM
FORUM_MEMBERS
INNER JOIN
FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_AGE + '%'
|
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 27 March 2005 : 01:49:48
|
Below code is a solution for 1. and 2. issues. Thanks to Terri from Asp.Net
INSERT INTO FORUM_ALLOWED_MEMBERS
(
Member_ID,
Forum_ID
)
SELECT
FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS
INNER JOIN
FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_AGE + '%'
WHERE ISNULL(FORUM_MEMBERS.M_AGE,'') <> '' AND
NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A
WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND
A.Forum_ID = FORUM_FORUM.Forum_ID)
|
 |
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 28 March 2005 : 01:54:55
|
| How can i make this SQL Query as an ASP Page under Snitz Forum? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 28 March 2005 : 07:00:17
|
Yes. Maybe a link in Admin section. In fact a button to run this query could be good but i don't want to want so much thing.  |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 March 2005 : 12:54:44
|
<!--#INCLUDE FILE="config.asp" -->
<%
Dim strSql, conn, lngCount
strSql = "Your SQL goes Here"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnString
conn.Execute strSql, lngCount, 1 + 128
Response.Write lngCount & " records were inserted into the FORUM_ALLLOWED_MEMBERS table."
conn.Close
%>
Add this to an asp file and then add a link in the sForumNavigation function in inc_header.asp, so that the file is executed when the link is clicked. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 28 March 2005 : 13:27:08
|
Thank you Rui. I'll use this code with access database (access on web, sql on local). Does it matter? By the way my Query's last version is below, someone may need it in future:
INSERT INTO FORUM_ALLOWED_MEMBERS
(
Member_ID,
Forum_ID
)
SELECT
FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS
INNER JOIN FORUM_FORUM ON REPLACE(LEFT(FORUM_FORUM.F_SUBJECT, CHARINDEX('.',FORUM_FORUM.F_SUBJECT)),'.','')
BETWEEN FORUM_MEMBERS.M_MARSTATUS-3 AND FORUM_MEMBERS.M_MARSTATUS+3
WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND
NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A
WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND
A.Forum_ID = FORUM_FORUM.Forum_ID) AND
EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050322000000)
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 March 2005 : 13:37:36
|
| The code will work with any database, since it completely ignores the SQL you plan to use. You must make sure it is correct, since the code will only try to execute it, without testing its correctness beforehand. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 28 March 2005 : 14:14:48
|
I get that error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Between operator without And in query expression 'REPLACE(LEFT(FORUM_FORUM.F_SUBJECT, CHARINDEX('.',FORUM_FORUM.F_SUBJECT)),'.','') BETWEEN FORUM_MEMBERS.M_MARSTATUS-3'.
AND is there, not?
<!--#INCLUDE FILE="config.asp" -->
<%
Dim strSql, conn, lngCount
strSql = " INSERT INTO FORUM_ALLOWED_MEMBERS ( Member_ID, Forum_ID ) " &_
" SELECT FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS " &_
" INNER JOIN FORUM_FORUM ON REPLACE(LEFT(FORUM_FORUM.F_SUBJECT, CHARINDEX('.',FORUM_FORUM.F_SUBJECT)),'.','') " &_
" BETWEEN FORUM_MEMBERS.M_MARSTATUS-3 AND FORUM_MEMBERS.M_MARSTATUS+3 " &_
" WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND" &_
" NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A " &_
" WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND" &_
" A.Forum_ID = FORUM_FORUM.Forum_ID) AND " &_
" EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050322000000)"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnString
conn.Execute strSql, lngCount, 1 + 128
Response.Write lngCount & " records were inserted into the FORUM_ALLLOWED_MEMBERS table."
conn.Close
%> |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 29 March 2005 : 02:24:14
|
| Same error when i use INSTR instead of CHARINDEX. |
|
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 29 March 2005 : 04:21:21
|
Seems hard. I'll go with MS SQL. I vreated tables by setup.asp but got another problem:
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'FORUM_ACTIVE_USERS'.
/IFRMA/inc_func_common.asp, line 57
Can't get in admin page to run MOD setup. I think i should create that table 'FORUM_ACTIVE_USERS' manually? |
Edited by - Lac0niC on 29 March 2005 04:21:59 |
|
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 29 March 2005 : 06:00:59
|
OK Now:
1.) First i created default tables with setup.asp?RC=5
2.) I created FORUM_ACTIVE_USERS table by DTS (just import it)
3.) Upload data as u tell here: http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=31144
4.) Then run Active Users MOD setup again
Bye Bye Jet OLEDB. :P
Have a nice day!.. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Lac0niC
New Member
Turkey
78 Posts |
Posted - 29 March 2005 : 08:08:13
|
| Between 2 Hours, Yes. =) |
|
|
| |
Topic |
|
Topic Locked
