Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > mysql > answers

Ms Access to MySQL conversion on asp shows this error

Newbie
  
Join Date: Oct 2008
Posts: 20
#1: Oct 19 '08
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.

Expand|Select|Wrap|Line Numbers
  1. 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.



Expand|Select|Wrap|Line Numbers
  1. <!--#include file="../config.asp"-->
  2.  
  3.    <% Dim ID, RS, SQL, NAME, EMAIL, COMMENT, sDATE, IO, M_ID, SUBJECT, CITY, COUNTRY, ALLOW_E, AID, NEXT_ID, BACK_ID
  4.  
  5.  ID = Trim(Request.QueryString("ID"))
  6.  AID = Trim(Request.QueryString("AID"))
  7.  IF IS_VALID_ID(ID) = False OR IS_VALID_ID(AID) = False THEN Response.END
  8.  IF Trim(Session("PMMS_IN")) = "True" THEN blLOGGED_IN = True 
  9.  
  10.  SQL = "SELECT fldNAME, fldEMAIL, fldCOMMENT, fldDATE, fldIP, fldM_ID, fldSUBJECT, fldCITY, fldCOUNTRY, fldALLOW FROM nm_tbl_comment WHERE ID = " & ID
  11.  Call OPEN_DB()
  12.  
  13.  ' Comment details
  14.  
  15.  Set RS = Server.CreateObject("ADODB.Recordset")
  16.  RS.LockType   = 1
  17.  RS.CursorType = 0
  18.  RS.Open SQL, MyConn 
  19.    IF NOT RS.EOF THEN
  20.     NAME = trim(RS("fldNAME"))
  21.    EMAIL = trim(RS("fldEMAIL"))
  22.    COMMENT = trim(RS("fldCOMMENT")) & ""
  23.    sDATE = trim(RS("fldDATE"))
  24.    IP = trim(RS("fldIP"))
  25.    M_ID = trim(RS("fldM_ID"))
  26.    SUBJECT = trim(RS("fldSUBJECT"))
  27.    CITY = trim(RS("fldCITY"))
  28.    COUNTRY = trim(RS("fldCOUNTRY"))
  29.    ALLOW_E = trim(RS("fldALLOW"))   
  30.    END IF
  31.  RS.Close 
  32.  Set RS = Nothing
  33.  
  34.  ' Next Link
  35.  
  36.  SQL = "SELECT TOP 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = " & AID & " AND ID > " & ID & " ORDER BY ID ASC"
  37.  Set RS = Server.CreateObject("ADODB.Recordset")
  38.  RS.Open SQL, MyConn 
  39.    IF NOT RS.EOF THEN
  40.     NEXT_ID = trim(RS("ID"))
  41.    END IF
  42.  RS.Close 
  43.  Set RS = Nothing
  44.  
  45.  ' Back Link
  46.  
  47.  SQL = "SELECT TOP 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = " & AID & " AND ID < " & ID & " ORDER BY ID DESC"
  48.  Set RS = Server.CreateObject("ADODB.Recordset")
  49.  RS.Open SQL, MyConn 
  50.    IF NOT RS.EOF THEN
  51.     BACK_ID = trim(RS("ID"))
  52.    END IF
  53.  RS.Close 
  54.  Set RS = Nothing 
  55.  
  56.  MyConn.Close
  57.  Set MyConn = Nothing
  58.  
  59.  
  60.  
  61. %>
any help appreciated, thanks in advance
Atli's Avatar
Moderator
  
Join Date: Nov 2006
Location: Iceland
Posts: 3,747
#2: Oct 19 '08

re: Ms Access to MySQL conversion on asp shows this error

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:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM aTable LIMIT 1
The advantage of the LIMIT clause over TOP is that you can also specify an offset, like:
Expand|Select|Wrap|Line Numbers
  1. SELECT * aTable LIMIT 10, 1
Which would read one row, starting at row number 10.
Newbie
  
Join Date: Oct 2008
Posts: 20
#3: Oct 19 '08

re: Ms Access to MySQL conversion on asp shows this error

Thank you for your reply, Atli

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

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1.   <%
  2.  
  3. 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"
  4. Set RS = Server.CreateObject("ADODB.Recordset")
  5. RS.LockType   = 1
  6. RS.CursorType = 0
  7. RS.Open SQL, MyConn    
  8. WHILE NOT RS.EOF 
  9. CCOUNT    = CCOUNT + 1
  10. C_ID      = trim(RS("ID"))
  11. C_NAME    = trim(RS("fldNAME"))
  12. C_COMMENT = trim(RS("fldCOMMENT"))
  13. C_SUBJECT = trim(RS("fldSUBJECT"))
  14. C_M_ID    = trim(RS("fldM_ID"))
  15. C_DATE    = trim(RS("fldDATE"))
  16. C_CITY    = trim(RS("fldCITY"))
  17. C_COUNTRY = trim(RS("fldCOUNTRY"))
  18. C_ALLOW_E = trim(RS("fldALLOW"))    
  19. C_EMAIL = trim(RS("fldEMAIL"))    
  20. %>
Atli's Avatar
Moderator
  
Join Date: Nov 2006
Location: Iceland
Posts: 3,747
#4: Oct 19 '08

re: Ms Access to MySQL conversion on asp shows this error

Quote:

Originally Posted by janetopps

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

Expand|Select|Wrap|Line Numbers
  1. 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.
Newbie
  
Join Date: Oct 2008
Posts: 20
#5: Oct 19 '08

re: Ms Access to MySQL conversion on asp shows this error

Quote:

Originally Posted by Atli

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


Expand|Select|Wrap|Line Numbers
  1.     <% ' Dim SQL_SIDE, RS_SIDE, NID, TITLE, POSTED, NEWS_LISTING, SHOW_HL, MyConn_SIDE, I_SIDE, SUMMARY, CATE_ID, ANAME
  2.  
  3.  
  4.  
  5.  
  6.     ' ------------------------------------------------------------------------------------------------------------------
  7.  
  8.         ' DATABASE CONNECTION
  9.  
  10.         Set MyConn_SIDE = Server.CreateObject("ADODB.Connection")
  11.  
  12.         MyConn_SIDE.Open "Driver={MySQL ODBC 3.51 Driver}; Server=987.564.300.77; uid=aname; pwd=mysql; database=title; option=3; port=3306;"
  13.  
  14.  
  15.         ' NUMBER OF HEADLINES TO SHOW
  16.  
  17.         SHOW_HL = 18
  18.  
  19.         ' Set to 1 if you are using MS Access, set to 2 if you are using MS SQL database
  20.  
  21.         DB_TO_USE_CUSTOM = 3
  22.  
  23.         ' Category ID to pull the articles from
  24.  
  25.         CATE_ID = "108"
  26.  
  27.     ' ------------------------------------------------------------------------------------------------------------------
  28.  
  29.  
  30.  
  31.  
  32.  
  33.  
  34.  
  35.     SELECT CASE DB_TO_USE_CUSTOM
  36.         CASE 1
  37.             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"
  38.         CASE 2
  39.             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"
  40.         CASE 3
  41.             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
  42.     END SELECT                
  43.  
  44.  
  45.     I_SIDE = 0
  46.     Set RS_SIDE = Server.CreateObject("ADODB.Recordset")
  47.     RS_SIDE.LockType   = 1
  48.     RS_SIDE.CursorType = 0
  49.     RS_SIDE.Open SQL_SIDE, MyConn_SIDE    
  50.          WHILE NOT RS_SIDE.EOF 
  51.              NID      = trim(RS_SIDE("NID"))
  52.             TITLE    = trim(RS_SIDE("TITLE"))
  53.             POSTED   = trim(RS_SIDE("POSTED"))
  54.             SUMMARY  = trim(RS_SIDE("fldSUMMARY"))
  55.              AUTHOR  = trim(RS_SIDE("ANAME"))
  56.             IMAGE    = trim(RS_SIDE("NIMAGE")) & ""
  57.             TITLE = PROCESS_SHORTCUTS_INC(False, TITLE)
  58.             SUMMARY = PROCESS_SHORTCUTS_INC(False, SUMMARY)            
  59.             I_SIDE = I_SIDE + 1
  60.             IF I_SIDE =< SHOW_HL   THEN
  61.                 %>
  62.  
  63.  
  64.  
  65.  
  66. <%IF NOT I_SIDE = SHOW_HL THEN%></div>
  67.                     <hr align="JUSTIFY">
  68.                     <div align="justify">
  69.                       <%End If%>
  70.  
  71.  
  72.                       <%
  73.             END IF    
  74.             RS_SIDE.MoveNext
  75.          WEND
  76.     RS_SIDE.Close 
  77.     Set RS_SIDE = Nothing
  78.     MyConn_SIDE.Close
  79.     Set MyConn_SIDE = Nothing
  80.  
  81.     FUNCTION PROCESS_SHORTCUTS_INC(blOPEN, TEXT)
  82.         Dim SQL, RS, strRETURNED_DATA, EOF_VAL, intNUM_COL, intNUM_ROW, intROW_COUNTER, strSIGN, strIMAGE    
  83.         SQL = "SELECT fldSIGN, fldIMAGE FROM nm_tbl_library WHERE fldACTIVE = 1"
  84.         Set RS = Server.CreateObject("ADODB.Recordset")
  85.         RS.LockType   = 1
  86.         RS.CursorType = 0
  87.         RS.Open SQL, MyConn_SIDE    
  88.             IF NOT RS.EOF THEN
  89.                 strRETURNED_DATA = RS.getrows
  90.             ELSE
  91.                 EOF_VAL = True
  92.             END IF    
  93.         RS.close
  94.         Set RS = Nothing   
  95.         IF Not EOF_VAL = True Then
  96.             intNUM_COL=ubound(strRETURNED_DATA,1)
  97.             intNUM_ROW=ubound(strRETURNED_DATA,2)
  98.             FOR intROW_COUNTER = 0 TO intNUM_ROW
  99.                 strSIGN  = Trim(strRETURNED_DATA(0,intROW_COUNTER))
  100.                 strIMAGE = Trim(strRETURNED_DATA(1,intROW_COUNTER))
  101.                 strIMAGE = "<img src='" & strIMAGE & "' border='0' alt='' />"
  102.                 TEXT = Replace(TEXT, strSIGN, strIMAGE)
  103.             NEXT
  104.         END IF                              
  105.         PROCESS_SHORTCUTS_INC = TEXT
  106.     END FUNCTION    
  107.     %>
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.
Reply

« previous question | next question »

Similar MySQL Database bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,392 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved