Incorrect syntax near the keyword 'ORDER'

Asked

Viewed 318 times

1

I started to perform a program in classic Portuguese and in a part of that program I wrote the following code:

<%

    SQL = " SELECT Id =   TCD.Id "_
        & "   ,     IdTipoContrato  =   TC.IdTipoContrato  "_
        & "   ,     Codigo          =   TCD.Codigo "_
        & "   ,     Mercado         =   M.Mercado "_
        & "   ,     Empresa         =   M.EmpresaBD "_
        & "   ,     Valor           =   TCD.Valor " _
        & "   ,     Descricao       =   TCD.Descricao "_ 
        & "   ,     Moeda           =   MDS.Moeda  "_ 
        & "   ,     Ativo           =  TCD.Ativo "_ 
        & " FROM        LI_TipoContratosDetalhe TCD  WITH (NOLOCK) "_
        & " INNER JOIN  LI_TipoContratos        TC   WITH (NOLOCK) ON TCD.IdTipoContrato        =   TC.IDtipocontrato "_
        & " INNER JOIN  LI_Mercado              M    WITH (NOLOCK) ON TCD.Mercado               =   M.Mercado and TCD.Empresa = M.EmpresaBD"_
        & " LEFT JOIN   LI_Moedas                    MDS    WITH (NOLOCK) ON TCD.Moeda          =   MDS.Moeda "_
        & " WHERE       TC.IdTipoContrato = " & Request("IdTipoContrato") & " " _
        & " ORDER BY    TCD.Id " _
        & "      ,      TC.IdTipoContrato "

       rs.Open safeSQL(SQL), con, 1, 1
       NLinhas = rs.RecordCount
       'numLinhaAtual = 1
       While not rs.EOF 

   %>

When I open the page ( along with the rest of the code that is not shown) I get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'ORDER'.

/GIP-Ep/administrative/contracts/managementTipoContracts.Asp, line 915

What I’m doing wrong??

Thanks in advance for the help!

I did a Response.Write SQL and got this 

SELECT Id = TCD.Id ,    IdTipoContrato  =   TC.IdTipoContrato , Codigo  =   TCD.Codigo ,    Mercado =   M.Mercado , Empresa =   M.EmpresaBD , Valor =   TCD.Valor , Descricao   =   TCD.Descricao , Moeda = MDS.Moeda , Ativo = TCD.Ativo FROM  LI_TipoContratosDetalhe TCD WITH (NOLOCK) INNER JOIN    LI_TipoContratos TC WITH (NOLOCK) ON TCD.IdTipoContrato =   TC.IDtipocontrato INNER JOIN    LI_Mercado  M WITH (NOLOCK) ON TCD.Mercado  =   M.Mercado and TCD.Empresa = M.EmpresaBD LEFT JOIN   LI_Moedas MDS   WITH (NOLOCK) ON TCD.Moeda  =   MDS.Moeda WHERE TC.IdTipoContrato = ORDER BY TCD.Id ,   TC.IdTipoContrato

I don’t know you’re referring to this, I’m still a little new at this

I have fixed the code now the Idtipocontrate is already entered correctly:

SELECT Id = TCD.Id ,    Codigo  =   TCD.Codigo ,    Mercado =   M.Mercado , Empresa =   M.EmpresaBD ,   Valor   =   TCD.Valor , Descricao   =   TCD.Descricao , Moeda = MDS.Moeda , Ativo = TCD.Ativo FROM  LI_TipoContratosDetalhe TCD WITH (NOLOCK) INNER JOIN    LI_TipoContratos TC WITH (NOLOCK) ON TCD.IdTipoContrato =   TC.IDtipocontrato INNER JOIN    LI_Mercados M WITH (NOLOCK) ON TCD.Mercado  =   M.Mercado and TCD.Empresa = M.EmpresaBD LEFT JOIN   LI_Moedas MDS WITH (NOLOCK) ON TCD.Moeda    =   MDS.Moeda WHERE TC.IdTipoContrato = 3 ORDER BY TCD.Codigo

Thank you

sql asp
  • 1

    You can print the generated SQL before running?

  • 1

    Probably what’s happening is that Request("IdTipoContrato") is not returning any value and thereby generating your SQL incorrectly.

  • 1

    Read this: Injection of SQL.

  • 1

    @Idkwhy vc could post the SQL generated by the concatenations or the value that is returning in Request("IdTipoContrato")?

  • Try to print the result of safeSQL(SQL) and put here to analyze.

  • Your variable IdTipoContrato is empty

  • Your Request("Idtipocontrate") is empty. I suggest you use parameters instead of concatenating the string, as it ends up opening a great opportunity for SQL Injection.

  • 2

    It turns out that the code above was outside the If I had entered, I already entered it inside the if now the Idtipocontrato receives the right Id, Thanks for showing me where the problem was.

  • Put the Request("IdTipoContrato")in single quotes.

  • Thus: & " WHERE TC.IdTipoContrato = '" & Request("IdTipoContrato") & "' " _

Show 5 more comments
No answers

Browser other questions tagged sql asp

You are not signed in. Login or sign up in order to post.