MySQL Syntax Error ASP.NET

Question

Good day!

I'm trying to figure out what error I'm having. This is the error:

And here is my code:

protected void accountGridView_RowEditing(object sender, GridViewEditEventArgs e)
{
    accountGridView.EditIndex = e.NewEditIndex;
    BindData();
}
protected void accountGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    int user_id = int.Parse(accountGridView.DataKeys[e.RowIndex].Value.ToString());
    TextBox txtUsername = (TextBox)accountGridView.Rows[e.RowIndex].FindControl("txtUsername");

    UpdateUser(user_id, txtUsername.Text);
    accountGridView.EditIndex = -1;
    BindData();
}

private void UpdateUser(int user_id, string username)
{
    GlobalVars cn = new GlobalVars();
    MySqlConnection connection = cn.connectDB();
    connection.Open();

    string query = "UPDATE user SET username = '" + username + " WHERE user_id = " + user_id + "";
    MySqlCommand com = new MySqlCommand(query, connection);

    com.ExecuteNonQuery();
    connection.Close();

}

I can't get it to work. Am I missing something here?

Any help would be much appreciated.

Looks like you are missing something -- like a single single quote. — mustaccio
– mustaccio, Commented Apr 19, 2016 at 1:48

sujith karivelil · Accepted Answer · 2019-07-17 13:55:16Z

The error message says that you have syntax errors in your query, so the other parts(connection) are working well as expected. Now consider the query:- if you debug the program and watch the query you can see that it may look like:

UPDATE user SET username = 'asd WHERE user_id= usr_123

So what is wrong here is, You ware missed a ' after asd, need to give a pair of ' to specify the user_id(if it is a string), so the query may look like this:

 string query = "UPDATE user SET username = '" + username + "' WHERE user_id = '" + user_id + "'";

But i strongly recommend you to use Parameterized queries instead for this to avoid injection. The parameterised query will looks like :

string query = "UPDATE user SET username = @username  WHERE user_id = @user_id";
MySqlCommand com = new MySqlCommand(query, connection);
com.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;
com.Parameters.Add("@user_id", MySqlDbType.VarChar).Value = user_id;
// execute query here

Collectives™ on Stack Overflow

MySQL Syntax Error ASP.NET

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related