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









up vote
1
down vote

favorite












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









share|improve this question









New contributor




Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.



















  • Are you aware of what a SQL Injection attack is?
    – Enigmativity
    Nov 10 at 7:35










  • I have no idea, I changed though.
    – Caden Buckelew
    Nov 10 at 9:08










  • 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 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
    Nov 10 at 9:22






  • 1




    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 at 9:24














up vote
1
down vote

favorite












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









share|improve this question









New contributor




Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.



















  • Are you aware of what a SQL Injection attack is?
    – Enigmativity
    Nov 10 at 7:35










  • I have no idea, I changed though.
    – Caden Buckelew
    Nov 10 at 9:08










  • 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 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
    Nov 10 at 9:22






  • 1




    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 at 9:24












up vote
1
down vote

favorite









up vote
1
down vote

favorite











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









share|improve this question









New contributor




Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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






c# .net ms-access ado.net






share|improve this question









New contributor




Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited Nov 10 at 11:42









S.Akbari

28.8k93368




28.8k93368






New contributor




Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked Nov 10 at 6:27









Caden Buckelew

116




116




New contributor




Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Caden Buckelew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











  • Are you aware of what a SQL Injection attack is?
    – Enigmativity
    Nov 10 at 7:35










  • I have no idea, I changed though.
    – Caden Buckelew
    Nov 10 at 9:08










  • 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 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
    Nov 10 at 9:22






  • 1




    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 at 9:24
















  • Are you aware of what a SQL Injection attack is?
    – Enigmativity
    Nov 10 at 7:35










  • I have no idea, I changed though.
    – Caden Buckelew
    Nov 10 at 9:08










  • 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 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
    Nov 10 at 9:22






  • 1




    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 at 9:24















Are you aware of what a SQL Injection attack is?
– Enigmativity
Nov 10 at 7:35




Are you aware of what a SQL Injection attack is?
– Enigmativity
Nov 10 at 7:35












I have no idea, I changed though.
– Caden Buckelew
Nov 10 at 9:08




I have no idea, I changed though.
– Caden Buckelew
Nov 10 at 9:08












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 at 9:18




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 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
Nov 10 at 9:22




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 at 9:22




1




1




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 at 9:24




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 at 9:24












3 Answers
3






active

oldest

votes

















up vote
1
down vote



accepted










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.






share|improve this answer






















  • now I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6:53










  • @CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'
    – S.Akbari
    Nov 10 at 6:55











  • I updated my answer
    – Caden Buckelew
    Nov 10 at 7:05










  • @CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.
    – S.Akbari
    Nov 10 at 7:05










  • @CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.
    – S.Akbari
    Nov 10 at 7:08

















up vote
1
down vote













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.






share|improve this answer




















  • I added the parameter queries and I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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.
    – Unhandled Exception
    Nov 10 at 6:55










  • I updated my question with the new code I have.
    – Caden Buckelew
    Nov 10 at 7:02










  • 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.
    – Unhandled Exception
    Nov 10 at 7:05


















up vote
0
down vote













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






share|improve this answer




















  • I now get this error: 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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 at 6:57










Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);






Caden Buckelew is a new contributor. Be nice, and check out our Code of Conduct.









 

draft saved


draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53236525%2fwhy-am-i-getting-syntax-error-while-trying-to-update-database%23new-answer', 'question_page');

);

Post as a guest






























3 Answers
3






active

oldest

votes








3 Answers
3






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
1
down vote



accepted










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.






share|improve this answer






















  • now I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6:53










  • @CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'
    – S.Akbari
    Nov 10 at 6:55











  • I updated my answer
    – Caden Buckelew
    Nov 10 at 7:05










  • @CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.
    – S.Akbari
    Nov 10 at 7:05










  • @CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.
    – S.Akbari
    Nov 10 at 7:08














up vote
1
down vote



accepted










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.






share|improve this answer






















  • now I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6:53










  • @CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'
    – S.Akbari
    Nov 10 at 6:55











  • I updated my answer
    – Caden Buckelew
    Nov 10 at 7:05










  • @CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.
    – S.Akbari
    Nov 10 at 7:05










  • @CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.
    – S.Akbari
    Nov 10 at 7:08












up vote
1
down vote



accepted







up vote
1
down vote



accepted






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.






share|improve this answer














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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 10 at 8:37

























answered Nov 10 at 6:28









S.Akbari

28.8k93368




28.8k93368











  • now I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6:53










  • @CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'
    – S.Akbari
    Nov 10 at 6:55











  • I updated my answer
    – Caden Buckelew
    Nov 10 at 7:05










  • @CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.
    – S.Akbari
    Nov 10 at 7:05










  • @CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.
    – S.Akbari
    Nov 10 at 7:08
















  • now I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6:53










  • @CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'
    – S.Akbari
    Nov 10 at 6:55











  • I updated my answer
    – Caden Buckelew
    Nov 10 at 7:05










  • @CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.
    – S.Akbari
    Nov 10 at 7:05










  • @CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.
    – S.Akbari
    Nov 10 at 7:08















now I get 'Data type mismatch in criteria expression.'
– Caden Buckelew
Nov 10 at 6:53




now I get 'Data type mismatch in criteria expression.'
– Caden Buckelew
Nov 10 at 6:53












@CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'
– S.Akbari
Nov 10 at 6:55





@CadenBuckelew Did you try this? where ID= '" + Convert.ToInt32(textBox7.Text) + "'
– S.Akbari
Nov 10 at 6:55













I updated my answer
– Caden Buckelew
Nov 10 at 7:05




I updated my answer
– Caden Buckelew
Nov 10 at 7:05












@CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.
– S.Akbari
Nov 10 at 7:05




@CadenBuckelew Also using Add instead of AddWithValue can help your more. Again check my updated answer.
– S.Akbari
Nov 10 at 7:05












@CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.
– S.Akbari
Nov 10 at 7:08




@CadenBuckelew And you should use parameters with your ID as well. Check my updated answer to see how.
– S.Akbari
Nov 10 at 7:08












up vote
1
down vote













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.






share|improve this answer




















  • I added the parameter queries and I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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.
    – Unhandled Exception
    Nov 10 at 6:55










  • I updated my question with the new code I have.
    – Caden Buckelew
    Nov 10 at 7:02










  • 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.
    – Unhandled Exception
    Nov 10 at 7:05















up vote
1
down vote













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.






share|improve this answer




















  • I added the parameter queries and I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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.
    – Unhandled Exception
    Nov 10 at 6:55










  • I updated my question with the new code I have.
    – Caden Buckelew
    Nov 10 at 7:02










  • 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.
    – Unhandled Exception
    Nov 10 at 7:05













up vote
1
down vote










up vote
1
down vote









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.






share|improve this answer












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.







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 10 at 6:33









Unhandled Exception

48925




48925











  • I added the parameter queries and I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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.
    – Unhandled Exception
    Nov 10 at 6:55










  • I updated my question with the new code I have.
    – Caden Buckelew
    Nov 10 at 7:02










  • 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.
    – Unhandled Exception
    Nov 10 at 7:05

















  • I added the parameter queries and I get 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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.
    – Unhandled Exception
    Nov 10 at 6:55










  • I updated my question with the new code I have.
    – Caden Buckelew
    Nov 10 at 7:02










  • 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.
    – Unhandled Exception
    Nov 10 at 7:05
















I added the parameter queries and I get 'Data type mismatch in criteria expression.'
– Caden Buckelew
Nov 10 at 6:53




I added the parameter queries and I get 'Data type mismatch in criteria expression.'
– Caden Buckelew
Nov 10 at 6: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.
– Unhandled Exception
Nov 10 at 6:55




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.
– Unhandled Exception
Nov 10 at 6:55












I updated my question with the new code I have.
– Caden Buckelew
Nov 10 at 7:02




I updated my question with the new code I have.
– Caden Buckelew
Nov 10 at 7:02












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.
– Unhandled Exception
Nov 10 at 7:05





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.
– Unhandled Exception
Nov 10 at 7:05











up vote
0
down vote













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






share|improve this answer




















  • I now get this error: 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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 at 6:57














up vote
0
down vote













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






share|improve this answer




















  • I now get this error: 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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 at 6:57












up vote
0
down vote










up vote
0
down vote









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






share|improve this answer












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







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 10 at 6:29









Halil İbrahim

767




767











  • I now get this error: 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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 at 6:57
















  • I now get this error: 'Data type mismatch in criteria expression.'
    – Caden Buckelew
    Nov 10 at 6: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 at 6:57















I now get this error: 'Data type mismatch in criteria expression.'
– Caden Buckelew
Nov 10 at 6:46




I now get this error: 'Data type mismatch in criteria expression.'
– Caden Buckelew
Nov 10 at 6: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 at 6:57




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 at 6:57










Caden Buckelew is a new contributor. Be nice, and check out our Code of Conduct.









 

draft saved


draft discarded


















Caden Buckelew is a new contributor. Be nice, and check out our Code of Conduct.












Caden Buckelew is a new contributor. Be nice, and check out our Code of Conduct.











Caden Buckelew is a new contributor. Be nice, and check out our Code of Conduct.













 


draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53236525%2fwhy-am-i-getting-syntax-error-while-trying-to-update-database%23new-answer', 'question_page');

);

Post as a guest














































































Popular posts from this blog

Top Tejano songwriter Luis Silva dead of heart attack at 64

政党

天津地下鉄3号線