SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member Desgarin's Avatar
    Join Date
    Jun 2004
    Location
    Almada
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Mysql syntax problem

    Salutations.

    I have an ASP aplication (which isn't written by me), connected to an Access Database, but recently they asked me to change it to MySQL.
    Although i'm a newbie in this matters of dinamic pages, I was able to configure "almost" all the ASP programation to work correctly with Mysql.
    But i have a single problem which is:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-debug]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 'select produtocategoria.id_cat_prod as ctl from produtocategori
    /manuquimica/sec/DetalheProduto.asp, line 118


    And the piece of code indicated by the error is this:

    Code:
    var strQryCP="select categoria.id_cat_prod, cat_desc, ctl FROM "
    strQryCP +="CATEGORIA left join "
    strQryCP += "(select produtocategoria.id_cat_prod as ctl from produtocategoria where produtocategoria.id_prod="+id+") as t1 " 
    strQryCP +="on categoria.id_cat_prod=t1.ctl "


    I've read that are some diferences in the way MySQL handles the code, such as the caracther ', anyway i just can't seem to resolve this syntax problem.
    Ive posted this question in the ASP Forum, and they said it would be wise to post it here.

    Thanks for your patience

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    mysql doesnb't support subqueries until version 4.1

    try this instead --
    Code:
    select categoria.id_cat_prod
         , cat_desc
         , produtocategoria.id_cat_prod as ctl 
      from categoria 
    left 
      join produtocategoria 
        on categoria.id_cat_prod
         = produtocategoria.id_cat_prod 
       and produtocategoria.id_prod
         = id 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member Desgarin's Avatar
    Join Date
    Jun 2004
    Location
    Almada
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI
    Thanks for the information, but i think there's more in the code i have to change, in order to make it work, i replaced the code by the one you said but unfortunately it still gives me an error, althought i changed my version of MySQL, and everything runs smoothly, my only problem is, if the server hosting this aplication have the latest version of MySQL, if not i will be in deep troubles, ehehehehe.

    Anyway, thanks alot for your tip, it sure helped me.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •