C# Sql Incorrect syntax error

Question

In the following command I get the exception

Incorrect syntax near 'TPA_Approved_Date'

What am I doing wrong?:

  SqlCommand sqlComm = new SqlCommand();
  sqlComm = myConnection.CreateCommand();
 sqlComm.CommandText = @"UPDATE [MRT_MKT_BIDW].[biw].[CNX_TPA_Applied_F] 
                        SET TPA_Approver_Code='@Approver_Code' 
                        TPA_Approved_Date ='@Approved_Date'   
                        TPA_Approved_Flag='Y' WHERE 
                        MDM_Invoice_Date_Dim_Key='20150206'";
 sqlComm.Parameters.AddWithValue("@Approver_Code", Approver_Code);
 sqlComm.Parameters.AddWithValue("@Approved_Date", "2015-05-06 16:24:47.870");

D Stanley · Accepted Answer · 2015-05-21 15:34:01Z

3

don't enclose your parameters in quotes - otherwise they will be treated as literals

separate the columns with commas:

sqlComm.CommandText = @"UPDATE [MRT_MKT_BIDW].[biw].[CNX_TPA_Applied_F] SET TPA_Approver_Code=@Approver_Code, TPA_Approved_Date =@Approved_Date, TPA_Approved_Flag='Y' WHERE MDM_Invoice_Date_Dim_Key='20150206'";

answered May 21, 2015 at 15:34

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ravish.hacker · Accepted Answer · 2015-05-21 16:02:10Z

-2

Above answer is correct. But just wanted to mention, sometimes its quicker to not to use parameters (if you trust the values in variables). Like for example:

SqlCommand sqlComm = new SqlCommand();
sqlComm = myConnection.CreateCommand();
sqlComm.CommandText = @"UPDATE [MRT_MKT_BIDW].[biw].[CNX_TPA_Applied_F] 
                        SET TPA_Approver_Code='"+Approver_Code+"',
                        TPA_Approved_Date ='2015-05-06 16:24:47.870',
                        TPA_Approved_Flag='Y' WHERE 
                        MDM_Invoice_Date_Dim_Key='20150206'";

answered May 21, 2015 at 16:02

ravish.hacker

1,19314 silver badges21 bronze badges

7 Comments

D Stanley Over a year ago

Bad idea. Concatenation (especially with strings, but also with dates) can lead to all kind of errors, whether you "trust" the value or not. It's not just about SQL injection - what if the value legitimately contains an apostrophe?

ravish.hacker Over a year ago

Can you give some examples for your "all sort of errors" considering the situation it is a trusted non null value? It depends on situation I am not saying it is the best way forward, but it works well in some cases. Which means I seriously doubt you classification of this as BAD IDEA!

D Stanley Over a year ago

1. Strings with apostrophes ("O'Brien") 2. Assuming that the client and server represent dates in a compatible way (what date is "1/2/2015"in the US? In England?)

ravish.hacker Over a year ago

You are passing the date as string anyway in question and in your answer!! so if it is wrong to concatenate, then its wrong in parameter too. Any other... ?

RubberChickenLeader Over a year ago

@ravish.hacker parameterized sql gets you more than just protection from simple errors (O'Brian) and sql injection, for sql server it also allows the server to reuse an old plan (if one exists) saving recompile time. Lots of good reasons to use parameters and a couple really bad ones to not.

|

Collectives™ on Stack Overflow

C# Sql Incorrect syntax error

2 Answers 2

Comments

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Related