errors after converting from Access to MySQL

[karen987]

I am trying to migrate from an MS access to MySQL and am having problems. I have asp pages on my website. I tried database conversion software, and managed to find one, transfer data,. Upon checking on the web, some works well, and other pages produce errors. i'm sure the asp code needs tweaking. 2 examples below



I ran this query in the MySQL query analayser


SELECT ID, fldNAME, ID as ID1, 0 as ID2, 0 as ID3 FROM nm_tbl_cate WHERE fldLEVEL = 1 UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, nm_tbl_cate.ID, 0 FROM nm_tbl_cate, nm_tbl_cate AS PARENT WHERE (nm_tbl_cate.fldLEVEL = 2) AND (nm_tbl_cate.fldPID =PARENT.ID) UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , SUBPARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, SUBPARENT.id, nm_tbl_cate.ID FROM nm_tbl_cate, nm_tbl_cate AS PARENT, nm_tbl_cate AS SUBPARENT WHERE (nm_tbl_cate.fldLEVEL = 3) AND (nm_tbl_cate.fldPID =PARENT.ID) AND (nm_tbl_cate.fldSID =SUBPARENT.ID) ORDER BY [fldName] ASC

it didnt run, and i got this message



error 1064

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 '[fldName] ASC' at line 1


I tried running without the sqaure brackets around "fldName" and this time it produced results . I presume this means the square brackets need to be removed from the data.

Here is another part of the code in the page that is causing problems:

SELECT TOP 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = 3324 AND ID > 585 ORDER BY ID ASC

When i ran the above codein the MySQL query analayser this is the result:

error 1064

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 '1 ID, `ID` FROM nm_tbl_comment WHERE fldNEWS_ID = 3324 AND ID > 585 ORDER BY ID' at line 1


So does anyone know what i need to do? how can i solve this problem? I'm prepared to change the method of importing data, but don't know how to do it. I used a database converter, as i thought it would be easy, but even the database converter cant convert some of code it seems

Any tips or help appreciated. i'm html ignorant. Thanks in advance

[karen987]

The second problem i mentioned above needs to explained more, so are more details and the full relevant code of the page at the bottom


THis is another asp page which is showing the error below. It's a page that pulls out the comments that users made on a news article. This page displays each comment,

At the bottom is the full code of this page, but here is the part that seems to be causing the problem. Remember i migrated from Ms Acess to MySQL, using a database converter.

SELECT TOP 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = 3324 AND ID > 585 ORDER BY ID ASC

When i ran the above code in the MySQL query analayser this is the result:

error 1064

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 '1 ID, `ID` FROM nm_tbl_comment WHERE fldNEWS_ID = 3324 AND ID > 585 ORDER BY ID' at line 1


[B]So does anyone know what i need to do? how can i solve this problem? I'm prepared to change the method of importing data, but don't know how to do it. I used a database converter, as i thought it would be easy, but even the database converter cant convert some of code it seems

I am including all the relevant code in this page below, as it may be clearer to anyone who understands , what the problem may be.



<!--#include file="../config.asp"-->

<% Dim ID, RS, SQL, NAME, EMAIL, COMMENT, sDATE, IO, M_ID, SUBJECT, CITY, COUNTRY, ALLOW_E, AID, NEXT_ID, BACK_ID

ID = Trim(Request.QueryString("ID"))
AID = Trim(Request.QueryString("AID"))
IF IS_VALID_ID(ID) = False OR IS_VALID_ID(AID) = False THEN Response.END
IF Trim(Session("PMMS_IN")) = "True" THEN blLOGGED_IN = True

SQL = "SELECT fldNAME, fldEMAIL, fldCOMMENT, fldDATE, fldIP, fldM_ID, fldSUBJECT, fldCITY, fldCOUNTRY, fldALLOW FROM nm_tbl_comment WHERE ID = " & ID
Call OPEN_DB()

' Comment details

Set RS = Server.CreateObject("ADODB.Recordset")
RS.LockType = 1
RS.CursorType = 0
RS.Open SQL, MyConn
IF NOT RS.EOF THEN
NAME = trim(RS("fldNAME"))
EMAIL = trim(RS("fldEMAIL"))
COMMENT = trim(RS("fldCOMMENT")) & ""
sDATE = trim(RS("fldDATE"))
IP = trim(RS("fldIP"))
M_ID = trim(RS("fldM_ID"))
SUBJECT = trim(RS("fldSUBJECT"))
CITY = trim(RS("fldCITY"))
COUNTRY = trim(RS("fldCOUNTRY"))
ALLOW_E = trim(RS("fldALLOW"))
END IF
RS.Close
Set RS = Nothing

' Next Link

SQL = "SELECT TOP 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = " & AID & " AND ID > " & ID & " ORDER BY ID ASC"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, MyConn
IF NOT RS.EOF THEN
NEXT_ID = trim(RS("ID"))
END IF
RS.Close
Set RS = Nothing

' Back Link

SQL = "SELECT TOP 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = " & AID & " AND ID < " & ID & " ORDER BY ID DESC"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open SQL, MyConn
IF NOT RS.EOF THEN
BACK_ID = trim(RS("ID"))
END IF
RS.Close
Set RS = Nothing

MyConn.Close
Set MyConn = Nothing



%>