locked
Update - Incorrect syntax RRS feed

  • Question

  • Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '201101'.

    declare @sql char(1000)
     set @sql='UPDATE Budgetctrl SET bgt01 = bgt01+2000   
                where tmonth='201101' and code= '1' and itemno='1'  '

     exec @sql

    can someone help me what causes the error

    thank you

    Fadjar

     

    Friday, February 11, 2011 8:36 AM

Answers

  • hi,

    You forgot to escape the single quotes for your literals. Also I would suspect that your literal doesn't form a valid date literal. Also the parenteses are missing for EXEC. btw, you should read Erland's articel about dynamic SQL:

    http://www.sommarskog.se/dynamic_sql.html

    DECLARE @sql NVARCHAR(MAX) = N'
      UPDATE Budgetctrl
      SET bgt01 = bgt01 + 2000
      WHERE tmonth = ''201101''
      AND code = ''1''
      AND itemno = ''1''' ;
    EXEC (@sql) ;
    

    Normally I would suspect that code and itemno are INT, in this case you don't need the quotes:

    DECLARE @sql NVARCHAR(MAX) = N'
      UPDATE Budgetctrl
      SET bgt01 = bgt01 + 2000
      WHERE tmonth = ''201101''
      AND code = 1
      AND itemno = 1' ;
    EXEC (@sql) ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by fadjar Friday, February 11, 2011 9:21 AM
    Friday, February 11, 2011 8:55 AM

All replies

  • Try that:

    set @sql='UPDATE Budgetctrl SET bgt01 = bgt01+2000   
                where tmonth=''201101'' and code= ''1'' and itemno=''1''  '

     

    those are not double quotes

    those are two times single quotes


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    • Proposed as answer by DeviantLogic Friday, February 11, 2011 8:54 AM
    Friday, February 11, 2011 8:45 AM
  • See you other thread "SQL Update error - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ''"
    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/7030a85a-ed0e-43f6-b896-86a9031302ca
    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Friday, February 11, 2011 8:45 AM
  • hi,

    You forgot to escape the single quotes for your literals. Also I would suspect that your literal doesn't form a valid date literal. Also the parenteses are missing for EXEC. btw, you should read Erland's articel about dynamic SQL:

    http://www.sommarskog.se/dynamic_sql.html

    DECLARE @sql NVARCHAR(MAX) = N'
      UPDATE Budgetctrl
      SET bgt01 = bgt01 + 2000
      WHERE tmonth = ''201101''
      AND code = ''1''
      AND itemno = ''1''' ;
    EXEC (@sql) ;
    

    Normally I would suspect that code and itemno are INT, in this case you don't need the quotes:

    DECLARE @sql NVARCHAR(MAX) = N'
      UPDATE Budgetctrl
      SET bgt01 = bgt01 + 2000
      WHERE tmonth = ''201101''
      AND code = 1
      AND itemno = 1' ;
    EXEC (@sql) ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by fadjar Friday, February 11, 2011 9:21 AM
    Friday, February 11, 2011 8:55 AM
  • Tipp:
    Such errors you can test easily:

    Replace exec @sql with print  @sql. Then call the string manuelly. 

     

    In a stored procedure you can make a optional parameter verbose = 0
    Inside the procedure:

    If verbose = 1 exec @sql else print @sql

    Friday, February 11, 2011 9:06 AM
  • Hi, Hoffmann

    Thank you for the sample code and link for Dynamic Sql

    It's really helpful

    Sincerely

    Fadjar

     

    Friday, February 11, 2011 9:26 AM