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();

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
– Enigmativity, Commented Nov 10, 2018 at 9:18
Oh wow, well this is all going to be saved on the machine local to the user, no server or anything — Caden Buckelew
– Caden Buckelew, Commented Nov 10, 2018 at 9: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
– Enigmativity, Commented Nov 10, 2018 at 9:24

Salah Akbari · Accepted Answer · 2018-11-10 08:37:07Z

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, 2018 at 8:37

answered Nov 10, 2018 at 6:28

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

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

6 Comments

Caden Buckelew Over a year ago

now I get 'Data type mismatch in criteria expression.'

Salah Akbari Over a year ago

@CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'

Salah Akbari Over a year ago

@CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.

Salah Akbari Over a year ago

@CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.

Caden Buckelew Over a year ago

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!

|

AHeyne · Accepted Answer · 2018-11-10 06:33:44Z

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, 2018 at 6:33

AHeyne

3,5052 gold badges13 silver badges20 bronze badges

3 Comments

Caden Buckelew Over a year ago

I added the parameter queries and I get 'Data type mismatch in criteria expression.'

AHeyne Over a year ago

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 Over a year ago

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.

Halil İbrahim · Accepted Answer · 2018-11-10 06:29:46Z

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, 2018 at 6:29

Halil İbrahim

1422 silver badges8 bronze badges

2 Comments

Caden Buckelew Over a year ago

I now get this error: 'Data type mismatch in criteria expression.'

Halil İbrahim Over a year ago

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);

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related