CODEWITHSUNDEEP
c#.netms-accessado.net
Caden Buckelew
Caden Buckelew

Reputation: 73

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

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

Upvotes: 1

Views: 109

Answers (3)

Salah Akbari
Salah Akbari

Reputation: 39966

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.

Upvotes: 1

AHeyne
AHeyne

Reputation: 3475

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.

Upvotes: 1

Halil İbrahim
Halil İbrahim

Reputation: 144

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

Upvotes: 0

Related Questions