Click here to Skip to main content
15,908,020 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Syntax error in update statement, any clue ?
C#
try
{
   DialogResult dr = MessageBox.Show("Are you sure you want to update?", "Details Updation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
   if (dr == DialogResult.No)
   {
      return;
   }
   else
   {                    
      OleDbConnection oleDbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=users.mdb;Persist Security Info=False");
      OleDbCommand update = new OleDbCommand("UPDATE users SET user = '"+textBox2.Text+"' where code = 1", oleDbConnection);
      oleDbConnection.Open();
      update.ExecuteNonQuery();
      oleDbConnection.Close();

      MessageBox.Show("User name updated successfully", "Update Username", MessageBoxButtons.OK, MessageBoxIcon.Information);
   }
}
catch (SystemException se)
{
   MessageBox.Show(se.Message);
}
finally
{

}
Posted
Updated 25-Mar-15 20:41pm
v2
Comments
Suvendu Shekhar Giri 26-Mar-15 2:42am    
Everything looks fine. Only make sure that you are not passing special characters in the textbox which can mean different while evaluating the string expression.
Member 11160992 26-Mar-15 2:58am    
Hi, thanks for reply. No special characters are there. I am passing as simple as abc
Suvendu Shekhar Giri 26-Mar-15 3:01am    
Then, check once if you have written correct names for table and columns.
Sinisa Hajnal 26-Mar-15 3:00am    
In the textbox2 you cannot use O'Reily or similar names because apostrophe (') ends your string. Also, consider what would happen if you enter: '; DROP USERS; -- into the textbox...

NEVER, EVER put user input directly into your system. Use stored procedure or at least parametrized query. At the very least use some checking of the content...for example, if you replace ' with '' (that is two apostrophes, not quotes) it will be escaped in the query and you'll get the name into the database
Member 11160992 26-Mar-15 3:21am    
Nope, checked everything, but exhausted however the same syntax for updating password is working fine. Below is the syntax

//int number = int.Parse(comboBox1.Text);
OleDbConnection oleDbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=users.mdb;Persist Security Info=False");
OleDbCommand update = new OleDbCommand("UPDATE users SET pass = '" + textBox36.Text + "' where code = 1", oleDbConnection);
oleDbConnection.Open();
update.ExecuteNonQuery();
oleDbConnection.Close();

1 solution

Please, don't do it like that. Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

That may cure your problem on it's own, but in addition "USER" is an SQL and ACCESS reserved word, so you shouldn't be using it as a column name. If you do, you should excape it with swaure brackets:
C#
OleDbCommand update = new OleDbCommand("UPDATE users SET [user] = ...


But seriously, use parametrized queries, or your best mate will delete your database just to see the look on your face...
 
Share this answer
 
Comments
Member 11160992 26-Mar-15 3:25am    
A million thanks to Original Griff, i didn't even think about reserved word. Thanks again sir.
OriginalGriff 26-Mar-15 5:02am    
You're welcome - but seriously, you need to use parametrized queries, or anyone can delete your database by typing into your text boxes...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900