none
Syntax error in ORDER BY clause error RRS feed

  • 问题

  • Hi all,

    When i run a sql,below error message apeared, who know what is the reason?

    Microsoft JET Database Engine error '80040e14'

    Syntax error in ORDER BY clause.

    /flexpower/includes/GetData.asp, line 45

     

    this code for this sql is:

    <%
    strSQLBomMdfStdData = "                                           " & vbCRLF &_
    " SELECT                                                          " & vbCRLF &_
    "       MDFStuff.AgilePartNumber                                  " & vbCRLF &_
    "     , MDFStuff.Description                                      " & vbCRLF &_
    "     , MDFStuff.Qty                                              " & vbCRLF &_

    "     , MDFStuff.ManufacturerName                                 " & vbCRLF &_

    "     , MDFStuff.ManufacturerPartNumber                           " & vbCRLF &_

    "     , format$(isnull(mdfstuff.mdfid)+1,'Yes/No')                " & vbCRLF &_
    "     , MDFStuff.GramWeight                                       " & vbCRLF &_
    "     , MDFStuff.PPMPass                                          " & vbCRLF &_
    "     , MDFStuff.CFName                                           " & vbCRLF &_
    "     , MDFStuff.HMName                                           " & vbCRLF &_
    "     , MDFStuff.MaterialName                                     " & vbCRLF &_
    "     , MDFStuff.CASNumber                                        " & vbCRLF &_

    "     , (MDFStuff.SubstanceMass * MDFStuff.Qty) AS CalcMgMass     " & vbCRLF &_
    "     , MDFStuff.SubstancePPM                                     " & vbCRLF &_
    "     , MDFStuff.Exemption                                        " & vbCRLF &_

    "     , MDFStuff.SubstanceMass                                    " & vbCRLF &_


    "     , STDStuff.StandardName                                     " & vbCRLF &_
    "     , STDStuff.StandardCompoundMaterialName                     " & vbCRLF &_
    "     , STDStuff.CASNumber                                        " & vbCRLF &_
    "     , STDStuff.MaterialName                                     " & vbCRLF &_
    "     , STDStuff.StandardLimitAmount                              " & vbCRLF &_

    " FROM (SELECT                                                    " & vbCRLF &_
    "           [_temp_BOM_import].BOMDataID                          " & vbCRLF &_
    "         , [_temp_BOM_import].AuthorityID                        " & vbCRLF &_
    "         , [_temp_BOM_import].Timestamp                          " & vbCRLF &_
    "         , [_temp_BOM_import].Filename                           " & vbCRLF &_
    "         , [_temp_BOM_import].AgilePartNumber                    " & vbCRLF &_
    "         , [_temp_BOM_import].Qty                                " & vbCRLF &_
    "         , [_temp_BOM_import].Description                      " & vbCRLF &_
    "         , MPN.VendorID                                          " & vbCRLF &_
    "         , [_temp_BOM_import].ManufacturerName                   " & vbCRLF &_
    "         , MPN.MPNid                                             " & vbCRLF &_
    "         , [_temp_BOM_import].ManufacturerPartNumber             " & vbCRLF &_

    "         , MDF.MDFid                                             " & vbCRLF &_
    "         , MDF.GramWeight                                        " & vbCRLF &_
    "         , (                                                     " & vbCRLF &_
    "             SELECT StandardResults.Pass                         " & vbCRLF &_
    "             FROM StandardResults                                " & vbCRLF &_
    "             WHERE                                               " & vbCRLF &_
    "                 StandardResults.StandardID = " & inSTDid & "    " & vbCRLF &_
    "                 AND                                             " & vbCRLF &_
    "                 StandardResults.MDFid = Series.MDFid            " & vbCRLF &_
    "            ) AS PPMPass                                         " & vbCRLF &_
    "         , MDFName.MDFName AS CFName                             " & vbCRLF &_
    "         , MDFName_1.MDFName AS HMName                           " & vbCRLF &_
    "         , MDFData.CASNumberID                                   " & vbCRLF &_
    "         , CASNumber.MaterialName                                " & vbCRLF &_
    "         , CASNumber.CASNumber                                   " & vbCRLF &_
    "         , MDFData.SubstanceMass                                 " & vbCRLF &_
    "         , MDFData.SubstancePPM                                  " & vbCRLF &_
    "         , MDFData.Exemption                                     " & vbCRLF &_
    "     FROM (((((((_temp_BOM_import                                " & vbCRLF &_
    "     LEFT JOIN MPN                                               " & vbCRLF &_
    "     ON [_temp_BOM_import].ManufacturerPartNumber = MPN.MPN)     " & vbCRLF &_
    "     LEFT JOIN Series                                            " & vbCRLF &_
    "     ON MPN.SeriesID = Series.SeriesID)                          " & vbCRLF &_
    "     LEFT JOIN MDF                                               " & vbCRLF &_
    "     ON Series.MDFid = MDF.MDFid)                                " & vbCRLF &_
    "     LEFT JOIN MDFData                                           " & vbCRLF &_
    "     ON MDF.MDFid = MDFData.MDFid)                               " & vbCRLF &_
    "     LEFT JOIN MDFName                                           " & vbCRLF &_
    "     ON MDFData.CompFragmentNameID = MDFName.MDFNameID)          " & vbCRLF &_
    "     LEFT JOIN MDFName AS MDFName_1                              " & vbCRLF &_
    "     ON MDFData.HomogeneousNameID = MDFName_1.MDFNameID)         " & vbCRLF &_
    "     LEFT JOIN CASNumber                                         " & vbCRLF &_
    "     ON MDFData.CASNumberID = CASNumber.CASNumberID)             " & vbCRLF &_
    "     ORDER BY                                                    " & vbCRLF &_
    "            [_temp_BOM_import].AgilePartNumber                   " & vbCRLF &_
    "         , [_temp_BOM_import].ManufacturerName                   " & vbCRLF &_
    "         , [_temp_BOM_import].ManufacturerPartNumber             " & vbCRLF &_
    "         , MDFName.MDFName                                       " & vbCRLF &_
    "         , MDFName_1.MDFName                                     " & vbCRLF &_
    "         , CASNumber.MaterialName                                " & vbCRLF &_
    " ) AS MDFStuff                                                   " & vbCRLF &_
    " LEFT JOIN (SELECT                                               " & vbCRLF &_
    "           Standard.StandardID                                   " & vbCRLF &_
    "         , Standard.StandardName                                 " & vbCRLF &_
    "         , StandardCompoundMaterial.StandardCompoundMaterialName " & vbCRLF &_
    "         , CASNumber.CASNumberID                                 " & vbCRLF &_
    "         , CASNumber.CASNumber                                   " & vbCRLF &_
    "         , CASNumber.MaterialName                                " & vbCRLF &_
    "         , StandardLimit.StandardLimitAmount                     " & vbCRLF &_
    "         , StandardMeasurementType.StandardMeasurementTypeName   " & vbCRLF &_
    "     FROM ((((Standard                                           " & vbCRLF &_
    "     RIGHT JOIN StandardLimit                                    " & vbCRLF &_
    "     ON Standard.StandardID = StandardLimit.StandardID)          " & vbCRLF &_
    "     LEFT JOIN StandardMeasurementType                           " & vbCRLF &_
    "     ON StandardLimit.StandardMeasurementTypeID                  " & vbCRLF &_
    "      = StandardMeasurementType.StandardMeasurementTypeID)       " & vbCRLF &_
    "     LEFT JOIN StandardMaterialList                              " & vbCRLF &_
    "     ON StandardLimit.StandardCompoundMaterialID                 " & vbCRLF &_
    "      = StandardMaterialList.StandardCompoundMaterialID)         " & vbCRLF &_
    "     LEFT JOIN StandardCompoundMaterial                          " & vbCRLF &_
    "     ON StandardMaterialList.StandardCompoundMaterialID          " & vbCRLF &_
    "      = StandardCompoundMaterial.StandardCompoundMaterialID)     " & vbCRLF &_
    "     LEFT JOIN CASNumber                                         " & vbCRLF &_
    "     ON StandardMaterialList.CASNumberID = CASNumber.CASNumberID " & vbCRLF &_
    "     WHERE                                                       " & vbCRLF &_
    "         Standard.StandardID = " & inSTDid & "                   " & vbCRLF &_
    " ) AS STDStuff                                                   " & vbCRLF &_
    " ON MDFStuff.CASNumberID = STDStuff.CASNumberID  order by MDFStuff.AgilePartNumber desc              " & vbCRLF

    %>


    Ares Zeng
    2011年4月29日 1:08

答案

  • 你好

    或者你可以嘗試 OUTPUT 這個 SQL 之後在 SQL SERVER/ACCESS DATABASE 上執行..

    看看那裡出現問題..

    Please correct me if my concept is wrong


    Chi
    2011年4月30日 6:31

全部回复