FewFlyBy FewFlyBy - 2 years ago 154
SQL Question

MySQL Syntax Error ASP.NET

Good day!

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

enter image description here

And here is my code:

protected void accountGridView_RowEditing(object sender, GridViewEditEventArgs e)
accountGridView.EditIndex = e.NewEditIndex;
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;

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

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



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

Any help would be much appreciated.

Answer Source

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 looks 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 looks 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download