SQL Server Numeric, can create but not update using C#

Question

Just a simple quick question, I'm using Microsoft SQL Server 2014 Express and now I have two functions to create a record and to update a record containing a numeric value.

For some unknown reason I can create the record using a numeric value with a number bigger than 0 after the decimal point (like 50.50), however, when trying to update this record with the numeric value it just says that my syntax is wrong after the decimal point. So tl,dr (50.00 works, 50.50 or something like that, doesn't).

My question now is: what am I doing wrong?

Here are my two functions:

public static void UpdateProduct(int id, string name, decimal price)
{
    try
    {
        string query = "UPDATE dbo.Products SET Name = '" + name + "' , Price = " + price + " WHERE ProductID = " + id;

        SqlCommand command = new SqlCommand(query, connection);
        command.ExecuteNonQuery();
    }
    catch (SqlException e)
    {
        Console.WriteLine(e.Message);
    }
}

public static void AddProduct(string name, decimal price)
{
    string query = "INSERT INTO dbo.Products (Name, Price) VALUES (@name, @price)";

    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@name", name);
    command.Parameters.AddWithValue("@price", price);

    command.ExecuteNonQuery();
}

And here is my SQL create for this value

create Table Products
(
    ProductID INT IDENTITY(1,1) PRIMARY key,
    Name VARCHAR(255) NOT NULL,
    Price NUMERIC(5,2) NOT NULL,
    Active BIT DEFAULT 1
);

SQL Parameters work for UPDATE queries too (but do use Add rather than AddWithValue). — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jul 5, 2016 at 17:13
You might want to read Can we stop using AddWithValue() already? — juharr
– juharr, Commented Jul 5, 2016 at 17:14
It is simple. When the decimal is concatenated to a string like you do in the UPDATE it is converted to a string using the ToString method. In your locale the ToString method produces a sequence of characters where the decimal point is represented by a comma. And this wreak havoc with your sql text. The solution is simple. Use parameters like you do in the Add — Steve
– Steve, Commented Jul 5, 2016 at 17:15
Why did you use parameters in one method and not the other? Your Update method is a textbook example of sql injection. You need to parameterize this query before bobby tables comes to visit. bobby-tables.com — Sean Lange
– Sean Lange, Commented Jul 5, 2016 at 18:20

derpirscher · Accepted Answer · 2016-07-05 21:24:59Z

1

In your insert query you use query parameters, which, among other things, take care of correct formatting of your decimal value.

In you update query you use string concatenation to add you decimal to the query. Most certainly, your current culture formats the decimal point not as point but as comma, resulting in an syntactically incorrect query.

So your assignment of

string query = "UPDATE dbo.Products SET Name = '" + name + "' , Price = " + price + " WHERE ProductID = " + id;

Will result in a string like

UPDATE dbo.Products SET Name = 'somename' , Price = 50,5 WHERE ProductID = 3

Instead of

UPDATE dbo.Products SET Name = 'somename' , Price = 50.5 WHERE ProductID = 3

Use parametrized queries like in the insert and this problem -- and many potential others you didn't even notice yet -- will be gone.

answered Jul 5, 2016 at 21:24

derpirscher

17.7k3 gold badges25 silver badges54 bronze badges

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

1 Comment

Patrick Over a year ago

Using the update with parameters worked perfectly for me now, thanks for helping.

Collectives™ on Stack Overflow

SQL Server Numeric, can create but not update using C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related