C# MySQL Transaction commit

Question

I want to update ~50 rows. So i do it in a foreach The Code run without any errors but there are no changes in the database.

public void updateItems(List<product> prdList)
    {
        MySqlTransaction tr = null;

        try
        {
            tr = this.con.BeginTransaction();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.Transaction = tr;

            foreach (product prd in prdList)
            {
                cmd.CommandText = "UPDATE products SET title='@title', quantity='@quantity' WHERE itemId LIKE '@itemId'";
                cmd.Parameters.AddWithValue("@title", prd.title);
                cmd.Parameters.AddWithValue("@quantity", prd.quantity);
                cmd.Parameters.AddWithValue("@itemId", prd.itemId);

                cmd.ExecuteNonQuery();
            }

            tr.Commit();
        }
        catch (MySqlException ex)
        {
            try
            {
                tr.Rollback();
            }
            catch (MySqlException ex1)
            {
                MessageBox.Show(ex1.ToString());
            }

            MessageBox.Show(ex.ToString());
        }
    }

If i print the Query String and run it on SQL-Bash, it works fine.

you're quoting your parameters. that is wrong. parameter go in "naked", never quoted. `set title=@title`, not `set title='@title'`. — Marc B, Jun 08 '15 at 14:41
And don't use `AddWithValue`, be specific about the types you are adding. — DavidG, Jun 08 '15 at 14:41

score 4 · Accepted Answer · edited May 23 '17 at 12:30

You should not use single quotes with your parameters. With single quotes, your query see them as a string literal, not a parameter.

cmd.CommandText = "UPDATE products SET title=@title, quantity=@quantity WHERE itemId LIKE @itemId";

Also since you try to parameterize LIKE part, you need to use %..% part in your parameter part or like '%' + @itemId + '%'.

By the way, if you have more than one prd in your prdList, you need to clear your all parameters at the top of your foreach loop.

foreach (product prd in prdList)
{
    cmd.Parameters.Clear();
    ...
    ...
}

As a last point, don't use AddWithValue anymore. It may generate unexpected results sometimes. Use .Add() method overloads to specify your parameter type and it's size. And don't forget to use using statement to dispose your connections and commands automatically.

Example DB: itemID - 0001 itemID - 00001 Now if i search %itemId i get 2 results back or? — Philipp Nies, Jun 08 '15 at 15:13
*If i use Parameters.Add() VS display .....Add() ist veraltet: Use AddWithValue() — Philipp Nies, Jun 08 '15 at 15:26

Ramgy Borja · Answer 2 · 2017-07-31T07:33:05.727

0

change your command statement like this

from

"UPDATE products SET title='@title', quantity='@quantity' WHERE itemId LIKE '@itemId'";

to

"UPDATE products SET title=@title, quantity=@quantity WHERE itemId LIKE @itemId";

and dont forget to add

cmd.Parameters.Clear();

you should clear the parameter every loop.

edited Jul 31 '17 at 07:33

answered Jul 29 '17 at 01:52

Ramgy Borja

2,330
2
19
40

C# MySQL Transaction commit

2 Answers2