Update Syntax error in C#

Question

I want to update datebase with datagridview data here is my code:

        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            SqlCommand cmd2 = new SqlCommand("UPDATE Pharmacy_Items Set Quantity= Quantity + " + dataGridView1.Rows[x].Cells[4].Value + " where ItemName='" + dataGridView1.Rows[x].Cells[1].Value + "'", mycon);
            cmd2.ExecuteNonQuery();
            x += 1;
        }

it gives syntax error near where.

Before you do anything else please read about CommandParameters, see: http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements and https://xkcd.com/327/ — rene, Jul 04 '16 at 10:49
Shudder. Please, please, please don't concatenate input into sql queries. That is just ripe for SQL Injection, which is a huge and genuine problem. — Marc Gravell, Jul 04 '16 at 10:53
@Farhadpaikan Please open the SQL Profiler and check the query that is being executed by SQL Server. It might be that dataGridView1.Rows[x].Cells[4].Value has an invalid value — Vahid Farahmandian, Jul 04 '16 at 10:53

GuidoG · Answer 1 · 2016-07-05T06:59:04.370

2 things are wrong :

tdataGridView1.Rows[x].Cells[4].Value might produce a value with a comma in it which is recognized by the database so a value of 10,4 is not seen as 10.4 but the 4 is seen as a new field
OR
some value you assign from your dataGridView is empty
Use parameters instead of building your query like this, not only is it safer but it will also fix your problem with the quantity field

example :

cmd2.CommandText = "UPDATE Pharmacy_Items Set Quantity = Quantity + @Quantity where ItemName = @ItemName";
cmd2.Parameters.AddWithValue(@Quantity, dataGridView1.Rows[x].Cells[4].Value);  
cmd2.Parameters.AddWithValue(@ItemName, dataGridView1.Rows[x].Cells[1].Value);
cmd2.ExecuteNonQuery();

EDIT : the OP wants to increase the quantity field.

cmd2.CommandText = "UPDATE Pharmacy_Items Set Quantity = Quantity + @Quantity where ItemName = @ItemName";
cmd2.Parameters.AddWithValue(@Quantity, dataGridView1.Rows[x].Cells[4].Value);  
cmd2.Parameters.AddWithValue(@ItemName, dataGridView1.Rows[x].Cells[1].Value);
cmd2.ExecuteNonQuery();

And if the cell can be empty you can replace the empty with 0 like this so that you just add 0 to quantity instead of getting an exception.

cmd2.CommandText = "UPDATE Pharmacy_Items Set Quantity = Quantity + @Quantity where ItemName = @ItemName";
cmd2.Parameters.AddWithValue(@Quantity, dataGridView1.Rows[x].Cells[4].Value ?? 0);  
cmd2.Parameters.AddWithValue(@ItemName, dataGridView1.Rows[x].Cells[1].Value);
cmd2.ExecuteNonQuery();

Syntax was quantity = quantity + datagridview cell , i want to increase to quantity field — Farhad paikan, Jul 04 '16 at 17:50
I have put an edit in my answer how you can add to the quantity using parameters. — GuidoG, Jul 05 '16 at 06:59

score 0 · Answer 2 · answered Jul 04 '16 at 10:53

You should use parameters, appending strings in SQL queries is a very bad idea (SQL-Injection). Below should make the error clearer:

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    decimal qty = Convert.ToDecimal(dataGridView1.Rows[x].Cells[4].Value);
    string itemName = dataGridView1.Rows[x].Cells[1].Value;
    string commandText = "UPDATE Pharmacy_Items Set Quantity= Quantity + @p1 WHERE ItemName = @p2";
    SqlCommand cmd2 = new SqlCommand(commandText, mycon);
    cmd2.Parameters.AddWithValue("@p1", qty);
    cmd2.Parameters.AddWithValue("@p2", itemName);

    cmd2.ExecuteNonQuery();
}

score 0 · Answer 3 · answered Jul 04 '16 at 10:57

I'm going to assume that the syntax error is coming from the SQL concatenation. If so, parameterization should fix it. You can use tools like Dapper to make it trivial to parameterize correctly:

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    string itemName = (string)dataGridView1.Rows[x].Cells[1].Value;
    // note: I don't know what the actual type is here; int? decimal?
    int quantity = (int)dataGridView1.Rows[x].Cells[4].Value;
    myCon.Execute(
        "UPDATE Pharmacy_Items Set Quantity=Quantity+@quantity where ItemName=@itemName",
        new { itemName, quantity });
    x += 1;
}

Update Syntax error in C#

3 Answers3