CODEWITHSUNDEEP
c#sql-serversyntaxnumeric
Patrick
Patrick

Reputation: 123

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

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
);

Upvotes: 0

Views: 711

Answers (1)

derpirscher
derpirscher

Reputation: 17407

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.

Upvotes: 1

Related Questions