Ms Access to MySQL conversion on asp shows this error

[janetopps]

I have an asp website, and i was using an MS Acess database, and i had to move to MySQL. I'm using a database converter, and some of the data shows, and other pulls up errors which im trying to resolve.



At the bottom is the full code of this page, but here is the part that seems to be causing the problem.

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

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
 
 
 
%>

any help appreciated, thanks in advance

[Atli]

Hi.

It's the "TOP 1" part that is causing the problem. The TOP clause doesn't exist in MySQL. Use LIMIT instead.

For example, to get the first row in a table:

SELECT * FROM aTable LIMIT 1

The advantage of the LIMIT clause over TOP is that you can also specify an offset, like:

SELECT * aTable LIMIT 10, 1

Which would read one row, starting at row number 10.

[janetopps]

Thank you for your reply, Atli

I tried changing the code in line 36 and 47 to:

SQL = "SELECT * FROM aTable LIMIT 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = " & AID & " AND ID > " & ID & " ORDER BY ID ASC"

however it didn't solve it,

This page is opened in a javascript pop up window as it is an article comment. It's actual calling page is another page (the news article). But i don't think that is of relevence or is it? i dont think i change to change the code in the article page do i?


On the news article page, this is the code that opens up the page whose code i posted above. It's opened up in a small javascript window. In the error message it says the error is actually in the comment page itself. But just in case, here is the code for the article page that opens the comment page:

  <%
 
SQL = "SELECT ID, fldNAME, fldCOMMENT, fldDATE, fldSUBJECT, fldM_ID, fldCITY, fldCOUNTRY, fldALLOW, fldEMAIL FROM nm_tbl_comment WHERE fldNEWS_ID = " & NID & " ORDER BY ID ASC"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.LockType   = 1
RS.CursorType = 0
RS.Open SQL, MyConn    
WHILE NOT RS.EOF 
CCOUNT    = CCOUNT + 1
C_ID      = trim(RS("ID"))
C_NAME    = trim(RS("fldNAME"))
C_COMMENT = trim(RS("fldCOMMENT"))
C_SUBJECT = trim(RS("fldSUBJECT"))
C_M_ID    = trim(RS("fldM_ID"))
C_DATE    = trim(RS("fldDATE"))
C_CITY    = trim(RS("fldCITY"))
C_COUNTRY = trim(RS("fldCOUNTRY"))
C_ALLOW_E = trim(RS("fldALLOW"))    
C_EMAIL = trim(RS("fldEMAIL"))    
%>

[Atli]

I tried changing the code in line 36 and 47 to:

SQL = "SELECT * FROM aTable LIMIT 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = " & AID & " AND ID > " & ID & " ORDER BY ID ASC"

however it didn't solve it,

Of course that didn't solve it. You have two FROM clauses in there, once of which refers to a table that I made up for my example!

You need to add the LIMIT clause to your old query, while removing the TOP clause.
The LIMIT clause needs to be the last thing in your query, like it was in my example query.

See the MySQL manual page on the SELECT statement to see exactly how it should be used.

[janetopps]

Of course that didn't solve it. You have two FROM clauses in there, once of which refers to a table that I made up for my example!

You need to add the LIMIT clause to your old query, while removing the TOP clause.
The LIMIT clause needs to be the last thing in your query, like it was in my example query.

See the MySQL manual page on the SELECT statement to see exactly how it should be used.

Atli, thanks, that worked. Much appreciated.

I wonder if you or anyone else can see what is wrong with another similar problem i have. Again remember i migrated from Ms Access to MySql

This is an asp include page, and once again, it needs modifying somewhere, i'm not sure where.

It pulls up the latest 18 articles in a certain category, which are shown in a column, with a title, paragraph, image and link leading to the full news story.

The page doesn't return an error, it just doesn't show anything. ie no records are being pulled out of the database, or if they are, they aren't showing.

I'd appreciate it, if you or anyone could show me what i need to change here, i've copied and pasted, the code which i think is relevant below

    <% ' Dim SQL_SIDE, RS_SIDE, NID, TITLE, POSTED, NEWS_LISTING, SHOW_HL, MyConn_SIDE, I_SIDE, SUMMARY, CATE_ID, ANAME
 
 
 
 
    ' ------------------------------------------------------------------------------------------------------------------
 
        ' DATABASE CONNECTION
 
        Set MyConn_SIDE = Server.CreateObject("ADODB.Connection")
 
        MyConn_SIDE.Open "Driver={MySQL ODBC 3.51 Driver}; Server=987.564.300.77; uid=aname; pwd=mysql; database=title; option=3; port=3306;"
 
 
        ' NUMBER OF HEADLINES TO SHOW
 
        SHOW_HL = 18
 
        ' Set to 1 if you are using MS Access, set to 2 if you are using MS SQL database
 
        DB_TO_USE_CUSTOM = 3
 
        ' Category ID to pull the articles from
 
        CATE_ID = "108"
 
    ' ------------------------------------------------------------------------------------------------------------------
 
 
 
 
 
 
 
    SELECT CASE DB_TO_USE_CUSTOM
        CASE 1
            SQL_SIDE = "SELECT TOP " & SHOW_HL & " nm_tbl_news.ID AS NID, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_agent.fldNAME AS ANAME, nm_tbl_news.fldPOSTED AS POSTED, fldSUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (Now() BETWEEN fldPOSTED AND fldEXPIRES) AND nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CATE_ID & ") ORDER BY nm_tbl_news.ID DESC"
        CASE 2
            SQL_SIDE = "SELECT TOP " & SHOW_HL & " nm_tbl_news.ID AS NID, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_news.fldPOSTED AS POSTED, fldSUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (GetDate() BETWEEN fldPOSTED AND fldEXPIRES) AND nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CATE_ID & ") ORDER BY nm_tbl_news.ID DESC"
        CASE 3
            SQL_SIDE = "SELECT nm_tbl_news.ID AS NID, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_news.fldPOSTED AS POSTED, fldSUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (Now() BETWEEN fldPOSTED AND fldEXPIRES) AND nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CATE_ID & ") ORDER BY nm_tbl_news.ID DESC LIMIT " & SHOW_HL
    END SELECT                
 
 
    I_SIDE = 0
    Set RS_SIDE = Server.CreateObject("ADODB.Recordset")
    RS_SIDE.LockType   = 1
    RS_SIDE.CursorType = 0
    RS_SIDE.Open SQL_SIDE, MyConn_SIDE    
         WHILE NOT RS_SIDE.EOF 
             NID      = trim(RS_SIDE("NID"))
            TITLE    = trim(RS_SIDE("TITLE"))
            POSTED   = trim(RS_SIDE("POSTED"))
            SUMMARY  = trim(RS_SIDE("fldSUMMARY"))
             AUTHOR  = trim(RS_SIDE("ANAME"))
            IMAGE    = trim(RS_SIDE("NIMAGE")) & ""
            TITLE = PROCESS_SHORTCUTS_INC(False, TITLE)
            SUMMARY = PROCESS_SHORTCUTS_INC(False, SUMMARY)            
            I_SIDE = I_SIDE + 1
            IF I_SIDE =< SHOW_HL   THEN
                %>
 
 
 
 
<%IF NOT I_SIDE = SHOW_HL THEN%></div>
                    <hr align="JUSTIFY">
                    <div align="justify">
                      <%End If%>
 
 
                      <%
            END IF    
            RS_SIDE.MoveNext
         WEND
    RS_SIDE.Close 
    Set RS_SIDE = Nothing
    MyConn_SIDE.Close
    Set MyConn_SIDE = Nothing
 
    FUNCTION PROCESS_SHORTCUTS_INC(blOPEN, TEXT)
        Dim SQL, RS, strRETURNED_DATA, EOF_VAL, intNUM_COL, intNUM_ROW, intROW_COUNTER, strSIGN, strIMAGE    
        SQL = "SELECT fldSIGN, fldIMAGE FROM nm_tbl_library WHERE fldACTIVE = 1"
        Set RS = Server.CreateObject("ADODB.Recordset")
        RS.LockType   = 1
        RS.CursorType = 0
        RS.Open SQL, MyConn_SIDE    
            IF NOT RS.EOF THEN
                strRETURNED_DATA = RS.getrows
            ELSE
                EOF_VAL = True
            END IF    
        RS.close
        Set RS = Nothing   
        IF Not EOF_VAL = True Then
            intNUM_COL=ubound(strRETURNED_DATA,1)
            intNUM_ROW=ubound(strRETURNED_DATA,2)
            FOR intROW_COUNTER = 0 TO intNUM_ROW
                strSIGN  = Trim(strRETURNED_DATA(0,intROW_COUNTER))
                strIMAGE = Trim(strRETURNED_DATA(1,intROW_COUNTER))
                strIMAGE = "<img src='" & strIMAGE & "' border='0' alt='' />"
                TEXT = Replace(TEXT, strSIGN, strIMAGE)
            NEXT
        END IF                              
        PROCESS_SHORTCUTS_INC = TEXT
    END FUNCTION    
    %>

by the way, do you know a good free database converter that will do the whole database? i'm using a trial version of one programme at the moment, which only pulls up a few records,


Many thanks again.