Why am I getting syntax error while trying to update database?

Question

I've been trying to look around and fix this and I've tried multiple things for hours, so I decided I'll ask others. I'm getting a

'Syntax error in UPDATE statement.'

When clicking the save button.

Here is my code:

OleDbCommand command = new OleDbCommand();
command.Connection = connection;

string query = "update Profiles set [PROFILE NAME]='" + textBox1.Text + "', [LOGIN EMAIL]='" + textBox2.Text + "', [PASSWORD]='" + textBox3.Text + "', [FULL NAME]='" + textBox4.Text + "', [CARD NUMBER]='" + textBox5.Text + "', [EXP MONTH]='" + comboBox1.Text + "', [EXP YEAR]='" + comboBox2.Text + "', CVV='" + textBox6.Text + "', where ID=" + textBox7.Text + "";
command.CommandText = query;
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Profile Saved");
RefreshDBConnection();

Updated Code:

ConnectToDataBase();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;

//string query = "update Profiles set [PROFILE NAME]='" + textBox1.Text + "', [LOGIN EMAIL]='" + textBox2.Text + "', [PASSWORD]='" + textBox3.Text + "', [FULL NAME]='" + textBox4.Text + "', [CARD NUMBER]='" + textBox5.Text + "', [EXP MONTH]='" + comboBox1.Text + "', [EXP YEAR]='" + comboBox2.Text + "', CVV='" + textBox6.Text + "' where  ID='" + Convert.ToInt32(textBox7.Text) + "'";
string query = "update Profiles set [PROFILE NAME]= @Profile, [LOGIN EMAIL]= @Email, [PASSWORD]= @Pass, [FULL NAME]= @Name, [CARD NUMBER]= @Card, [EXP MONTH]= @EXPM, [EXP YEAR]= @EXPY, CVV= @CVV where ID = '" +textBox7.Text+ "'";
command.Parameters.AddWithValue("@Profile", textBox1.Text);
command.Parameters.AddWithValue("@Email", textBox2.Text);
command.Parameters.AddWithValue("@Pass", textBox3.Text);
command.Parameters.AddWithValue("@Name", textBox4.Text);
command.Parameters.AddWithValue("@Card", Convert.ToInt32(textBox5.Text));
command.Parameters.AddWithValue("@EXPM", Convert.ToInt32(comboBox1.Text));
command.Parameters.AddWithValue("@EXPY", Convert.ToInt32(comboBox2.Text));
command.Parameters.AddWithValue("@CVV", Convert.ToInt32(textBox6.Text));
command.CommandText = query;
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Profile Saved");
RefreshDBConnection();
this.Close();

Are you aware of what a [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) attack is? — Enigmativity, Nov 10 '18 at 07:35
It's where someone can type valid SQL in the `textBox1` `TextBox`, for example, and your code would allow their custom SQL to be run on the server. It could query sensitive information or drop the database or insert new records in a user admin table. — Enigmativity, Nov 10 '18 at 09:18
Oh wow, well this is all going to be saved on the machine local to the user, no server or anything — Caden Buckelew, Nov 10 '18 at 09:22
It's good practice to use parameters to avoid this kind of thing. The number of times that code that was only ever meant to be local that ends up going to production astounds me. — Enigmativity, Nov 10 '18 at 09:24
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Nov 10 '18 at 10:22

Salah Akbari · Accepted Answer · 2018-11-10T08:37:07.280

1

You have one extra comma , before your Where statement:

CVV='" + textBox6.Text + "', where

Just remove it. And you should convert your textBox7.Text to int, if it's type is integer, ID= '" + Convert.ToInt32(textBox7.Text) + "' (don't forget to surround it with single quotes). Also you should always use parameterized queries to avoid SQL Injection. Something like this:

string query = "update Profiles set [PROFILE NAME]= @Profile,... where ID = @Id";
command.Parameters.AddWithValue("@Profile", textBox1.Text);
command.Parameters.AddWithValue("@Id", textBox7.Text);//Or Convert.ToInt32(textBox7.Text)

Although specify the type directly and use the Value property is more better than AddWithValue:

command.Parameters.Add("@Profile", SqlDbType.VarChar).Value = textBox1.Text;
command.Parameters.Add("@Id", SqlDbType.Int).Value = Convert.ToInt32(textBox7.Text);

And of course, it has been recommended to use using statement always.

edited Nov 10 '18 at 08:37

answered Nov 10 '18 at 06:28

Salah Akbari

39,330
10
79
109

now I get 'Data type mismatch in criteria expression.' – Caden Buckelew Nov 10 '18 at 06:53
@CadenBuckelew Did you try this? `where ID= '" + Convert.ToInt32(textBox7.Text) + "'` – Salah Akbari Nov 10 '18 at 06:55
@CadenBuckelew Also using `Add` instead of `AddWithValue` can help your more. Again check my updated answer. – Salah Akbari Nov 10 '18 at 07:05
@CadenBuckelew And you should use parameters with your `ID` as well. Check my updated answer to see how. – Salah Akbari Nov 10 '18 at 07:08
After a lot of trial and error it worked, thank you so much! sorry for being new at coding, this is my first week or so learning how to use c#. I really appreciate the help! – Caden Buckelew Nov 10 '18 at 07:13
@CadenBuckelew You're Welcome. Good luck and enjoy programing :) – Salah Akbari Nov 10 '18 at 07:14

score 1 · Answer 2 · answered Nov 10 '18 at 06:33

1

Another reason could be, that the values you read from the textboxes could contain special characters which will make the syntax invalid when beiing concatenated to the SQL string. This you also can avoid when using parameter queries.

answered Nov 10 '18 at 06:33

AHeyne

3,377
2
11
16

I added the parameter queries and I get 'Data type mismatch in criteria expression.' – Caden Buckelew Nov 10 '18 at 06:53
Please show us the whole resulting query you have so far. And a valuable/necessary additional information would be the datatype of the fields of the table. – AHeyne Nov 10 '18 at 06:55
Because you get an`'Data type mismatch in criteria expression` we still would need the data types of the fields of the table. And your criteria still uses string concatenation. – AHeyne Nov 10 '18 at 07:05

score 0 · Answer 3 · answered Nov 10 '18 at 06:29

0

CVV='" + textBox6.Text + "', where

you have to delete comma here. also its better to use parameters since you have several. sending them like that will cause problems in future. so i suggest you to use cmd.Parameters.Add(); instead of raw usage. also if ID is Integer you have to Convert.ToInt32(textBox7.Text);

answered Nov 10 '18 at 06:29

Halil İbrahim

144
2
8

I now get this error: 'Data type mismatch in criteria expression.' – Caden Buckelew Nov 10 '18 at 06:46
It's because you sending all parameters as string. which db sees as nvarchar so its occurs. you gotta convert it all to related field's data type. like if integer Convert.ToInt32(textbox7.text); – Halil İbrahim Nov 10 '18 at 06:57

Why am I getting syntax error while trying to update database?

3 Answers3