| Author |
 Topic  |
|
|
Mojox
Starting Member
10 Posts |
 Posted - 11 April 2001 : 15:43:15
|
I've searched this forum and couldn't find an answer to my problem. sorry, if this has been posted already.
I ran mod_dbsetup.asp with no problems. When I attempt to view events.asp, I get the following error:
ADODB.Recordset.1 error '80004005'
SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near 'ON FORUM_EVENTS.added_by = FORUM_Members.Member_ID WHERE (Start_Date >='20010401' at line 1
events_functions.asp, line 316
Will someone help me figure out this problem? Thanks.
|
|
|
Aznknight
Senior Member
USA
1373 Posts |
|
|
Mojox
Starting Member
10 Posts |
Posted - 11 April 2001 : 16:27:11
|
I am using mySQL. (Is that the problem?)
|
 |
|
|
Mojox
Starting Member
10 Posts |
Posted - 11 April 2001 : 17:15:51
|
I added a Response.Write line to take a look at the SQL statement. Maybe this will help spot the error:
SELECT event_id, start_date, end_date, event_title, PRIVATE, M_NAME, event_details FROM FORUM_EVENTS Inner JOIN FORUM_Members ON FORUM_EVENTS.added_by = FORUM_Members.Member_ID WHERE (Start_Date >='20010401000000' AND Start_Date <= '20010430000000') OR (End_Date >='20010401000000' AND End_Date <= '20010430000000') OR (Start_Date < '20010401000000' AND End_Date > '20010430000000') ORDER BY Start_Date
|
|
|
|
Aznknight
Senior Member
USA
1373 Posts |
|
|
Mojox
Starting Member
10 Posts |
Posted - 12 April 2001 : 12:03:56
|
Yeah, mySQL didn't like the INNER JOIN for some odd reason. So, yesterday, I changed to join type and now everything works perfectly fine.
|
|
|
|
Aznknight
Senior Member
USA
1373 Posts |
Posted - 12 April 2001 : 15:30:08
|
quote:
Yeah, mySQL didn't like the INNER JOIN for some odd reason. So, yesterday, I changed to join type and now everything works perfectly fine.
Oh cool! could you write out what the mySQL friendly strSQL looks like?
- Alan
www.iamviet.com
www.calvsa.net
Snitz Resource |
|
|
|
Mojox
Starting Member
10 Posts |
Posted - 12 April 2001 : 16:16:46
|
The mySQL friendly syntax looks like this:
SELECT event_id, start_date, end_date, event_title, PRIVATE, M_NAME, event_details FROM FORUM_EVENTS LEFT JOIN FORUM_MEMBERS ON FORUM_EVENTS.added_by = FORUM_MEMBERS.Member_ID WHERE (Start_Date >='20010401000000' AND Start_Date <= '20010430000000') OR (End_Date >='20010401000000' AND End_Date <= '20010430000000') OR (Start_Date < '20010401000000' AND End_Date > '20010430000000' )ORDER BY Start_Date
Aside from changing the JOIN, I had to make sure all the table names were capitalized, as all my table names were capitalized. Case doesn't seem to matter for field names.
I got the idea to change the JOIN type from the mySQL online reference. The statement I am referring to reads:
"The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables."
This may not be the most efficient solution. If I find that a different JOIN type works better I'll post the info here.
|
|
|
|
Aznknight
Senior Member
USA
1373 Posts |
Posted - 12 April 2001 : 16:34:05
|
quote:
The mySQL friendly syntax looks like this:
SELECT event_id, start_date, end_date, event_title, PRIVATE, M_NAME, event_details FROM FORUM_EVENTS LEFT JOIN FORUM_MEMBERS ON FORUM_EVENTS.added_by = FORUM_MEMBERS.Member_ID WHERE (Start_Date >='20010401000000' AND Start_Date <= '20010430000000') OR (End_Date >='20010401000000' AND End_Date <= '20010430000000') OR (Start_Date < '20010401000000' AND End_Date > '20010430000000' )ORDER BY Start_Date
Aside from changing the JOIN, I had to make sure all the table names were capitalized, as all my table names were capitalized. Case doesn't seem to matter for field names.
I got the idea to change the JOIN type from the mySQL online reference. The statement I am referring to reads:
"The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables."
This may not be the most efficient solution. If I find that a different JOIN type works better I'll post the info here.
Thanks for letting me know how you got it to work with mySQL. Had no idea that mySQL was case sensitive on the table names. Learn something new everyday  .
Hopefully, the next versions of my mod will be mySQL friendly by default 
- Alan
www.iamviet.com
www.calvsa.net
Snitz Resource |
|
|
| |
Topic |
|
Topic Locked
