Why won't this C# database application with parameterized sql work?

Question

I have C# forms application with a TextBox called ageTextBox;

After I press a button, the application should update one or more values from my MySQL database, but it doesn't.

Here's the code:

private void button2_Click(object sender, EventArgs e)
    {
        // this works perfectly if I put an actual number instead of @ag
        string sql = "update persons set age = 30 where age = @ag";
        OdbcCommand cmd = new OdbcCommand(sql, conn);
        cmd.Parameters.Add("@ag", OdbcType.Int);
        cmd.Parameters["@ag"].Value = ageTextBox.Text;
        int aff = cmd.ExecuteNonQuery();
        if (aff == 0)
            MessageBox.Show("No rows afected");
        else
            MessageBox.Show("Everything went as planned");
        }
    }

It always shows "No rows affected"! (unless I enter something that's not a number, in which case, an exception is thrown)

Does anyone know what's going on?

Try using Convert.ToInt(ageTextBox.Text) on the Value line. It's possible that MySQL is choking because you're passing it a string and not an int. — Michael Todd
– Michael Todd, Commented Jan 4, 2012 at 23:38
@MichaelTodd I've tried int.parse and Convert.ToInt... Still not working... Thanks for your suggestion anyway. — conectionist
– conectionist, Commented Jan 4, 2012 at 23:41
I remember a scenario previously where suppling a string arg when comparing against a numeric column would actually perform a string comparison (i.e. MySQL will convert the column to a string value). — p.campbell
– p.campbell, Commented Jan 4, 2012 at 23:42
Ignoring the returned rowcount, did the row(s) actually get changed in MySQL? — p.campbell
– p.campbell, Commented Jan 4, 2012 at 23:47

p.campbell · Accepted Answer · 2012-01-04 23:43:12Z

2

cmd.Parameters.Add is obsolete you should use cmd.Parameters.AddWithValue try using this instead:

cmd.Parameters.AddWithValue("@ag", int.Parse(ageTextBox.Text));

edited Jan 4, 2012 at 23:43

p.campbell

101k70 gold badges263 silver badges326 bronze badges

answered Jan 4, 2012 at 23:39

Xtian Macedo

8355 silver badges19 bronze badges

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

15 Comments

p.campbell Over a year ago

Should really supply an actual numeric argument.

Ted Over a year ago

And please make sure you're disposing of your connection, command, etc. when you're finished.

Marc Gravell Over a year ago

Where is that documented as obsolete? indeed, in many ways AddWithValue is not recommended in particular for strings, since it can lead to using variable parameter lengths (causing query plan misses) and getting the db-type wrong (unicode vs ansi, etc). Here's the MSDN page; not obsolete: msdn.microsoft.com/en-us/library/8dcw81x5.aspx

p.campbell Over a year ago

@Xtian: using OdbcCommand here. You're referencing SqlClient.

p.campbell Over a year ago

@@rowcount doesn't exist anywhere in MySQL.

|

Marc Gravell · Accepted Answer · 2012-01-04 23:44:39Z

0

it is possible that ODBC isn't reporting rowcounts (the equivalent of SET NOCOUNT ON). You might try adding a select @@rowcount as a final statement (or the equivalent for your particular DB), and using ExecuteScalar() (which will read that value).

edited Jan 4, 2012 at 23:44

answered Jan 4, 2012 at 23:39

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Tallmaris · Accepted Answer · 2012-01-04 23:48:03Z

0

This is not a solution but rather a debugging tip. Since you said it works ok when you enter a number in your code, can you try and fire a SQL tracer tool for MySQL (I suppose there is one available somwhere) and see what query is actually executed on the DB in the "working" and in the non working case (and in the exception case maybe)?

This can give you a good insight on what is going on...

answered Jan 4, 2012 at 23:48

Tallmaris

7,5903 gold badges31 silver badges59 bronze badges

Collectives™ on Stack Overflow

Why won't this C# database application with parameterized sql work?

3 Answers 3

15 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

15 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related