none
ERROR [42000] [Microsoft][ODBC Visual FoxPro Driver]Syntax error. Query works fine in foxpro but when i exceute it from .net show this error RRS feed

  • Question

  • string sqlCommand =
                        "select stienda, sarticulo, stalla, sum(scantidad - NVL(cantidad ,0000)) as tcantidad, stock580" + semana +
                        ".sstorage, ALLTRIM(storage.snombre) + \" \" + ALLTRIM(storagetype.snombre) as snombre "
                        + "from storagetype INNER JOIN storage ON storagetype.scodigo = stipo "
                        + "INNER JOIN stock\\stock580" + semana + " ON storage.scodigo = stock580" + semana + ".sstorage "
                        + "LEFT JOIN (SELECT RARTICULO, RTALLA, RSTORAGE, RCALIDAD, SUM(RCANTIDAD) AS CANTIDAD "
                                    + "FROM RESURTIDOP INNER JOIN RESURTIDOH ON RPEDIDO = RPEDIDOP AND RTIEGRABA = RTIENDA "
                                    + "WHERE restado IN(\"PEDIDO\", \"MARCACION\", \"FACTURACION\") GROUP BY RARTICULO, RTALLA, RCALIDAD, RSTORAGE) "
                                    + "rr  ON sarticulo = rarticulo AND stalla = rtalla AND rr.rstorage = stock580" + semana + ".sstorage "
                        + "WHERE sbodega = \"0580\" AND spublico = .t. and sarticulo = '" + articulo + "' and stalla = '" + talla + "'  HAVING SUM(scantidad - NVL(cantidad,0000)) > " + cantidad
                        + " group by sarticulo, stalla, stock580" + semana + ".sstorage, storage.snombre, storagetype.snombre";

    try {
       using(OdbcConnection conn = new OdbcConnection (constants.connetionStringRetailBodega580)) {
       conn.Open ();

       System.Data.Odbc.OdbcDataAdapter da = new OdbcDataAdapter ();

       da.SelectCommand = conn.CreateCommand ();
       da.SelectCommand.CommandText = sqlCommand;

       OdbcDataReader dr = da.SelectCommand.ExecuteReader ();
       ....

       ....

      }
    } catch(OdbcException ex) {
         .....
    }

    Monday, March 6, 2017 1:23 PM

All replies

  • I'm not sure whether it will help or not, but try using OleDb instead of ODBC. And, take a look here for the proper Connection Strings:

    https://www.connectionstrings.com/dbf-foxpro/


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, March 7, 2017 5:57 AM
  • Hi CarlosGJ,

    Based on your description, I create a simple with a simple sql statement (like below), it works well. please create a simple SQL statement as me and check if it works.

    string sqlCommand = "select * from Baseball";

    In addition, please debug your code and copy the value of variable named sqlCommand and execute it in your FoxPro and check if it works.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 7, 2017 6:31 AM
    Moderator