MySQL Update Query Error Message C#

Question

Before you comment please note that I understand that my code is vulnerable to SQL injection, please disregard any comments about it being vulnerable for purposes of simplicity

I've checked around the website for answers but none seem to fit my situation, many are PHP.

I am trying to update information on a MySQL database from C# Forms Application on Visual Studio 2012, so I've allowed the user to input data but I want them to be able to update their data.

I've tried all sorts of different methods many give me errors, I feel like I'm very close with this method.

    string Connection = "server = localhost; " + "database = root; " + "uid = root;" + "pwd = password;";
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        MySqlConnection Conn = new MySqlConnection(Connection);
        try
        {
            Conn.Open();
            string Query = "Update users (Calories) VALUES(@Calories) WHERE username = " + Form1.sName + "AND Day = " + Form1.iDay;
            MySqlCommand cmd = new MySqlCommand(Query, Conn);
            cmd.Parameters.AddWithValue("@Calories", txtCalories.Text);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Data Saved");
        }


        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            Conn.Close();
        }

It is telling me there is an error with my syntax.

I've allowed the user to enter information on the previous form, after clicking continue it passes the data to the next form which is this form.

I want them to be able to update their data on the database with information that they enter on this one BUT only for the most recent day which is why I've included the day variable.

You probably need quotes around Form1.sName at least. This would have been handled if you had made it a parameter instead of using string concatenation. — juharr
– juharr, Commented Mar 11, 2015 at 18:49
You should not call variables directly in your query as it is subject to potential issues. Your adding of the cmd.Parameters.AddWithValue() is the best way and should be used for all variables going into your query. — Ryan C
– Ryan C, Commented Mar 11, 2015 at 18:57

Rick S · Accepted Answer · 2015-03-11 18:53:26Z

1

You have the wrong syntax for UPDATE. And you are missing single quotes around your string values. Assuming username and Day are both strings in the db.

string Query = "Update users SET Calories = @Calories WHERE username = '" + Form1.sName + "' AND Day = '" + Form1.iDay + "'";

answered Mar 11, 2015 at 18:53

Rick S

6,5966 gold badges32 silver badges43 bronze badges

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

2 Comments

juharr Over a year ago

I'm guessing Day is an integer based on the name of the iDay variable.

Allahu Akbar Over a year ago

That works, thanks, it is always the syntactical errors that catch me out, I've tried looking up the MySQL manual but it can be confusing especially at 5 to 7 at night...

David Soussan · Accepted Answer · 2015-03-11 18:49:49Z

1

Wrong query structure, VALUES is for insert. You should use SET:

"Update users SET `Calories` = @Calories WHERE username = " + Form1.sName + "AND Day = " + Form1.iDay;

answered Mar 11, 2015 at 18:49

David Soussan

2,7261 gold badge18 silver badges19 bronze badges

3 Comments

juharr Over a year ago

Pretty sure there needs to also be single quotes around Form1.sName.

Allahu Akbar Over a year ago

Okay that seems to have gotten rid of the majority of syntax errors and has left me with one informing me that there is an error with the days

juharr Over a year ago

@AllahuAkbar put single quotes around it an a space before the and. ...Where username = '" + Form1.sName + "' AND...

Collectives™ on Stack Overflow

MySQL Update Query Error Message C#

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related