Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#4.0
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.
 
//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. Smile | :)
Posted 17-Jan-13 4:31am
nizam15571
Comments
joshrduncan2012 at 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 at 17-Jan-13 10:36am
   
the error is pointing towards at the line
 
dbCom.ExecuteNonQuery();
 
it says "Syntax error in UPDATE statement."
Member 9581488 at 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

Rate this: bad
good
Please Sign up or sign in to vote.

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:
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.[^]
  Permalink  
Comments
nizam15 at 17-Jan-13 10:53am
   
i already parametrised them just like you suggested but it still came up with the same error.
OriginalGriff at 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 at 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 at 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 at 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 at 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 at 17-Jan-13 11:24am
   
You're welcome!

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



Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid