Syntax error in UPDATE statement

Question

0.00/5 (No votes)

See more:

hai there guys. I'm sorry if this questions is common. I already did my search and still couldn't find what's the problem. To me the syntax is quite ok already. Here is the code snippet for my UPDATE query.

C#

//rd.Close();
label36.Visible = false;
dbCom.CommandType = CommandType.Text;
dbCom.CommandText = "UPDATE loginDB SET password='" + confirmpass_tb.Text + "' WHERE ID= '" + textBox1.Text + "'";
dbCom.Connection = dbCon;
 //dbCon.Open();
dbCom.ExecuteNonQuery();
/*string access = string.Format("UPDATE loginDB set password='" + confirmpass_tb.Text + "' WHERE ID='" + textBox1.Text + "'");
dbCom = new OleDbCommand(access, dbCon);
 dbCom.ExecuteNonQuery();*/
MessageBox.Show("Successfully Updated");

any help would be greatly appreciated. :)

Posted 17-Jan-13 4:31am

nizam15

Add a Solution

Comments

joshrduncan2012 17-Jan-13 10:34am

What is the word-for-word text of the error message? Simply saying there is an error in the update statement doesn't really help.

nizam15 17-Jan-13 10:36am

the error is pointing towards at the line

dbCom.ExecuteNonQuery();

it says "Syntax error in UPDATE statement."

Member 9581488 17-Jan-13 10:54am

If your id is in INT in sql server then you have to convert it to int before passing it to query.
Use breakpoint and see the query. Run that query in SQL Server Management Studio. You will definitely find error there.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2013-01-17T04:42:00

Solution 1

Chances are it's the actual text of the password or user name that is causing the problem, but fiing it is easy and also pervents your users accidentally or deliberately destroying your database - use parametrised queries:

SQL

dbCom.CommandText = "UPDATE loginDB SET password=@PW WHERE ID=@ID";
dbCom.Connection = dbCon;
dbCom.Parameters.AddWithValue("@ID", textBox1.Text);
dbCom.Parameters.AddWithVlaue("@PW", confirmpass_tb.Text);
dbCom.ExecuteNonQuery();

But please, never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

Posted 17-Jan-13 4:42am

OriginalGriff

Comments

nizam15 17-Jan-13 10:53am

i already parametrised them just like you suggested but it still came up with the same error.

OriginalGriff 17-Jan-13 10:57am

Copy and paste the parametrised version here - it should work (it does here, so there has to be a difference between our codes)

OriginalGriff 17-Jan-13 11:01am

Out of interest, which version of SQL are you connecting to? Try putting '[' and ']' around the column name:
dbCom.CommandText = "UPDATE loginDB SET [password]=@PW WHERE ID=@ID";

Some DB's have the word "password" as a reserved word and that would cause a syntax error. If that is the case, change the column name to something else (and still don't store your password in clear text)

nizam15 17-Jan-13 11:01am

here is the full code of when I press the update button.
<pre lang="c#">
private void button4_Click(object sender, EventArgs e)
{
label29.Visible = false;
if (password_tb.Text == "" || confirmpass_tb.Text == "")
{
label29.Visible = true;
}
else
{
if (confirmpass_tb.Text != password_tb.Text)
{
label35.Visible = true;
}
else
{
label35.Visible = false;
label29.Visible = false;
dbCom = new OleDbCommand("SELECT password FROM loginDB WHERE password='" + password_tb.Text + "'", dbCon);
OleDbParameter par = new OleDbParameter();
par.ParameterName = password_tb.Text;
dbCom.Parameters.Add(par);
OleDbDataReader rd = dbCom.ExecuteReader();
//dbCom2.Dispose();
if (rd.HasRows)
{
label36.Visible = true;
}
else
{
//rd.Close();
label36.Visible = false;
dbCom.CommandType = CommandType.Text;
dbCom.CommandText = "UPDATE loginDB SET password=@PW WHERE ID=@ID";
dbCom.Connection = dbCon;
dbCom.Parameters.AddWithValue("@ID", textBox1.Text);
dbCom.Parameters.AddWithValue("@PW", confirmpass_tb.Text);
dbCom.ExecuteNonQuery();
MessageBox.Show("Successfully Updated");
}
rd.Close();
//dbCom2.Dispose();
password_tb.Text = "";
confirmpass_tb.Text = "";
}
}
}
</pre>

OriginalGriff 17-Jan-13 11:03am

OleDb! You should have said!
Use the '[' and ']' as I suggested. It'll work, then change the column name to prevent it happening again.

nizam15 17-Jan-13 11:20am

thank you so much man!!!
sorry for not mentioning i'm using access as the database. it is working now.
once again thank you so much!! :)

OriginalGriff 17-Jan-13 11:24am

You're welcome!