Syntaxproblem

9 svar · 264 visningar · startad av Bendro 11 dec. 2003

BendroMedlem sedan jan. 20013 039 inlägg

11 dec. 2003 16:00#1

Vid en uppdatering av mitt forum fick jag följande felmeddelande:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09' 

[MySQL][ODBC 3.51 Driver][mysqld-4.0.16-nt-log]You have an 
error in your SQL syntax. Check the manual that corresponds to 
your MySQL server version for the right syntax to use near '5 
FORUM_FORUM.F_SUBJECT, FORUM_TOPICS.T_STATUS, FORUM_TOPICS.T_ 

/forum2/forum/My.asp, line 337

Koden felmeddelandet hänvisar till ser ut så här:

strSql = strSql & strTablePrefix & "FORUM.F_SUBJECT, " & strTablePrefix & "TOPICS.T_STATUS, "
strSql = strSql & strTablePrefix & "TOPICS.T_VIEW_COUNT, " & strTablePrefix & "TOPICS.FORUM_ID, "
strSql = strSql & strTablePrefix & "TOPICS.TOPIC_ID, " & strTablePrefix & "TOPICS.CAT_ID, "
strSql = strSql & strTablePrefix & "TOPICS.T_SUBJECT, "
strSql = strSql & strTablePrefix & "TOPICS.T_AUTHOR, " & strTablePrefix & "TOPICS.T_REPLIES, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_NAME, " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR, "
strSql = strSql & strTablePrefix & "TOPICS.T_LAST_POST, " & strMemberTablePrefix & "MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME "
strSql = strSql & "FROM " & strMemberTablePrefix & "MEMBERS, " & strTablePrefix & "FORUM, "
strSql = strSql & strTablePrefix & "TOPICS, " & strMemberTablePrefix & "MEMBERS AS " & strMemberTablePrefix & "MEMBERS_1 "
strSql = strSql & "WHERE " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR = " & strMemberTablePrefix & "MEMBERS_1.MEMBER_ID  "
strSql = strSql & "AND " & strTablePrefix & "FORUM.FORUM_ID = " & strTablePrefix & "TOPICS.FORUM_ID "
strSql = strSql & "AND " & strTablePrefix & "FORUM.CAT_ID = " & strTablePrefix & "TOPICS.CAT_ID "
strSql = strSql & "AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID=" & id
strSql = strSql & "AND " & strTablePrefix & "TOPICS.T_AUTHOR=" & id
strSql = strSql & " ORDER BY " & strTablePrefix & "TOPICS.T_LAST_POST DESC;"

set rs = my_Conn.Execute (strSql)

Undrar om det är någon som ser vad det är felmeddelandet hänvisar till för syntaxfel?

LarsGMedlem sedan dec. 200012 464 inlägg

11 dec. 2003 16:06#2

Det saknas en del av sql-frågan. Det omnämns near '5 i meddelandet men någon 5:a finns ej med i din kod.

Gissar att du har

select top 5

någonstans!?

BendroMedlem sedan jan. 20013 039 inlägg

11 dec. 2003 16:08#3

My bad...så här ser raderna precis ovanför ut:

if flag_maxtopics = 0 then
	strSql = "SELECT "
else
	strSql = "SELECT TOP " & flag_maxtopics & " "
end if

LarsGMedlem sedan dec. 200012 464 inlägg

11 dec. 2003 16:15#4

top 5 är en icke standardkonstruktion som fungerar i MS-världen. Mysql har hittat på en egen syntax för detta

select ... from ... 
order by ...
limt 5

BendroMedlem sedan jan. 20013 039 inlägg

11 dec. 2003 16:16#5

Aha.. Bugar och tackar.

BendroMedlem sedan jan. 20013 039 inlägg

11 dec. 2003 17:02#6

Nu har jag ändrat till:

'if flag_maxtopics = 0 then
	strSql = "SELECT "
else
	strSql = "SELECT "
end if

strSql = strSql & strTablePrefix & "FORUM.F_SUBJECT, " & strTablePrefix & "TOPICS.T_STATUS, "
strSql = strSql & strTablePrefix & "TOPICS.T_VIEW_COUNT, " & strTablePrefix & "TOPICS.FORUM_ID, "
strSql = strSql & strTablePrefix & "TOPICS.TOPIC_ID, " & strTablePrefix & "TOPICS.CAT_ID, "
strSql = strSql & strTablePrefix & "TOPICS.T_SUBJECT, "
strSql = strSql & strTablePrefix & "TOPICS.T_AUTHOR, " & strTablePrefix & "TOPICS.T_REPLIES, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_NAME, " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR, "
strSql = strSql & strTablePrefix & "TOPICS.T_LAST_POST, " & strMemberTablePrefix & "MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME "
strSql = strSql & "FROM " & strMemberTablePrefix & "MEMBERS, " & strTablePrefix & "FORUM, "
strSql = strSql & strTablePrefix & "TOPICS, " & strMemberTablePrefix & "MEMBERS AS " & strMemberTablePrefix & "MEMBERS_1 "
strSql = strSql & "WHERE " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR = " & strMemberTablePrefix & "MEMBERS_1.MEMBER_ID  "
strSql = strSql & "AND " & strTablePrefix & "FORUM.FORUM_ID = " & strTablePrefix & "TOPICS.FORUM_ID "
strSql = strSql & "AND " & strTablePrefix & "FORUM.CAT_ID = " & strTablePrefix & "TOPICS.CAT_ID "
strSql = strSql & "AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID=" & id
strSql = strSql & "AND " & strTablePrefix & "TOPICS.T_AUTHOR=" & id
strSql = strSql & " ORDER BY " & strTablePrefix & "TOPICS.T_LAST_POST LIMIT 5;" 

set rs = my_Conn.Execute (strSql)

Och får följande fel:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[MySQL][ODBC 3.51 Driver][mysqld-4.0.16-nt-log]You have an 
error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use 
near 'FORUM_TOPICS.T_AUTHOR=1 ORDER BY FORUM_TOPICS.T_LAST_POST LIMIT 

/forum2/forum/My.asp, line 337

@ndersMedlem sedan juni 200032 969 inlägg

11 dec. 2003 17:04#7

Skriv ut den färdiga SQL-frågan med response.write så blir det lättare att felsöka.

Mvh

BendroMedlem sedan jan. 20013 039 inlägg

11 dec. 2003 17:06#8

Er önskan är min lag:

SELECT FORUM_FORUM.F_SUBJECT, 
FORUM_TOPICS.T_STATUS, FORUM_TOPICS.T_VIEW_COUNT, 
FORUM_TOPICS.FORUM_ID, FORUM_TOPICS.TOPIC_ID, 
FORUM_TOPICS.CAT_ID, FORUM_TOPICS.T_SUBJECT, 
FORUM_TOPICS.T_AUTHOR, FORUM_TOPICS.T_REPLIES, 
FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_LAST_POST_AUTHOR, FORUM_TOPICS.T_LAST_POST, FORUM_MEMBERS_1.M_NAME AS 
LAST_POST_AUTHOR_NAME FROM FORUM_MEMBERS, 
FORUM_FORUM, FORUM_TOPICS, FORUM_MEMBERS AS 
FORUM_MEMBERS_1 WHERE 
FORUM_TOPICS.T_LAST_POST_AUTHOR = FORUM_MEMBERS_1.MEMBER_ID AND FORUM_FORUM.FORUM_ID 
= FORUM_TOPICS.FORUM_ID AND FORUM_FORUM.CAT_ID = 
FORUM_TOPICS.CAT_ID AND FORUM_MEMBERS.MEMBER_ID=1AND FORUM_TOPICS.T_AUTHOR=1 ORDER BY 
FORUM_TOPICS.T_LAST_POST LIMIT 5;

LarsGMedlem sedan dec. 200012 464 inlägg

11 dec. 2003 17:18#9

Det saknas ett mellanslag

FORUM_MEMBERS.MEMBER_ID=1AND

Det skulle inte vara så dumt om du delade upp föregående inlägg i flera rader.

BendroMedlem sedan jan. 20013 039 inlägg

11 dec. 2003 21:16#10

Sorry :r
Tänkte mig inte för.