Query working fine in SQL but not in the code

Question

I have a simple query updating the ProductPrice column by replacing old data and updating ProductQuantity which is being added to the previous one.

I wrote the query in SQL and it's working fine, but in the VS code it's concatenating the ProductQuantity. Like if I have 20 in product quantity then after this the result should be 20 + 50 = 70 but after executing query in code it updates the value to 2050

The query in SQL is:

UPDATE ProductLog 
SET ProductQuantity = ProductQuantity + 50, 
  ProductPrice = 20 
WHERE ProductCode = 1

The query in my C# code is:

sql = "";
sql += "UPDATE ProductLog 
  SET ProductQuantity = ProductQuantity 
    + '" 
    + productQuantity 
    + "', ProductPrice = '" 
    + productPrice 
    + "' WHERE ProductCode = '" 
    + ProductCode 
    + "'";

I am not able to find my mistake in code query. Please guide me in this regard.

The column's datatype in the table is varchar.

You should really look into parameterized queries, first and foremost.[See Here](http://blogs.msdn.com/b/sqlphp/archive/2008/09/30/how-and-why-to-use-parameterized-queries.aspx). And Yes, see Andrew's comment also. You only need " " not '" "'. — Mark C., Apr 03 '14 at 19:34
Please **DO NOT** deploy this as implemented. This code is completely susceptible to [SQL Injection Attacks](http://en.wikipedia.org/wiki/SQL_injection). Please use [SQL Parameters](http://stackoverflow.com/questions/7505808/using-parameters-in-sql-statements). — Erik Philips, Apr 03 '14 at 19:44
[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! If you have a `Quantity` that's **clearly** a numerical value - *why on earth* are you storing this in a `varchar` column?!?!?!? Make this an `INT` column and all your problems are gone! — marc_s, Apr 03 '14 at 20:29

score 1 · Accepted Answer · answered Apr 03 '14 at 19:40

Jack,

That is because when you are manually generating a query via string concatenation, it will not add the numeric values together. It will run all your variables through their respective .ToString() methods, get the result, and concatenate the result into your string. Depending on how you are executing this query (ADO.NET, Linq2SQL, or Entity Framework) you are better off using the mechanisms in those technologies to update your values as opposed to manually creating those strings. If you want to continue doing this way you will have to retrieve the value for "ProductQuantity", add that value to 50 (or whatever value you want) and then do the concatenation before executing the query.

Query working fine in SQL but not in the code

1 Answers1