Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi,

I want to update my password in database ,I am using following code but getting exception "UPDATE query syntax error".
Pls help me out.
protected void  Button1_Click(object sender, EventArgs e)
{

    int flag = 0;
  OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\shreedharmanagementsystem\\App_Data\\my1.mdb");
    OleDbCommand com = new OleDbCommand("select * from Login", con);
    con.Open();
    if (con.State == ConnectionState.Open)
    {
        OleDbDataReader dtr;
        dtr = com.ExecuteReader();
        while (dtr.Read())
        {

            if (dtr[1].ToString().Equals(TextBox1.Text))
            {
                flag = 1;
                //break;
                if (TextBox2.Text == TextBox3.Text)
                {
                    string update = "UPDATE Login SET Password = '" + TextBox2.Text + " ' WHERE UserId = '" + dtr[0].ToString() + "'";
                    OleDbCommand cmd = new OleDbCommand(update, con);
                    cmd.ExecuteNonQuery();                     // EXCEPTION 
                    Response.Redirect("feemaster.aspx");
                }
            }
        }

        if (flag == 1)
        {
            HttpCookie uname = new HttpCookie("username");
            HttpCookie upass = new HttpCookie("userpass");
            HttpCookie rights = new HttpCookie("rights");
            uname.Value = TextBox1.Text;
            upass.Value = TextBox2.Text;
            //rights.Value = dtr[2].ToString();
            Response.Cookies.Add(uname);
            Response.Cookies.Add(upass);
            //Response.Cookies.Add(rights);
            Response.Redirect("feediposite.aspx");
        }
    }
    con.Close();

}


Thanks
Abhishek
Posted
Updated 21-May-11 8:12am
v2
Comments
CS2011 21-May-11 14:18pm    
What is the exception you are getting?
abhiamity86 22-May-11 0:18am    
Thanks for reply
Exception is "Syntax error in UPDATE statement".
R. Hoffmann 21-May-11 15:01pm    
I just tried the update statement in Management Studio, and it executes fine, although the syntax highlighting does recognize "Login" as a reserved word. However, I used SQL Server 2008 R2, and you are connecting to Access, so that is perhaps why it doesn't work on your side.

Have you tried CS2011's suggestion of enclosing the table name in square brackets? I think Prem Shanker Verma and CS2011 are correct with their diagnosis. Don't know who voted them down.
R. Hoffmann 21-May-11 15:04pm    
Another thing, this won't fix the problem you're getting but is important nevertheless: your UPDATE statement inserts an additional space at the end of the password you're getting from the textbox, so what is stored in the database is different to what the user entered. This is probably a typo.

string update = "UPDATE Login SET Password = '" + TextBox2.Text + "-->Remove space here<--' WHERE UserId = '" + dtr[0].ToString() + "'";
abhiamity86 22-May-11 0:20am    
Thanks for reply..

I tried suggestion's of both but these are not helpful.

Try following

C#
string update = "UPDATE [Login] SET Password = '" + TextBox2.Text + " ' WHERE UserId = '" + dtr[0].ToString() + "'";
 
Share this answer
 
Use OleDbParameter[^], Configuring Parameters and Parameter Data Types[^].

C#
// NOTE: The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC do not support named parameters for passing parameters to an SQL statement
string update = "UPDATE [Login] SET Password = ? WHERE UserId = ?";

OleDbCommand cmd = new OleDbCommand(update, con);

// The order of Parameters is important.
cmd.Parameters.AddWithValue("@textbox2", TextBox2.Text);
cmd.Parameters.AddWithValue("@id", dtr[0].ToString());

cmd.ExecuteNonQuery();


The SQL UPDATE will fail, if TextBox2.Text has value my'newPass;Word
 
Share this answer
 
I just created a small console app that reproduces your problem, and saw that CS2011 and Prem Shanker Verma had the right solution, but wrong keyword :)

It's actually "Password" that's causing the problem in your UPDATE statement. Wrap that in square brackets ("[]") and the problem goes away!

So, your updated statement should look like this:
string update = "UPDATE Login SET [Password] = '" + TextBox2.Text + "' WHERE UserId = '" + dtr[0].ToString() + "'";


And remember to remove the extraneous space that is being appended to the password after the contents of TextBox2 (the above statement is correct) :)
 
Share this answer
 
You have keep your table name "Login" which is a reserve keyword, so this is throwing exception. Change your table name.

I think this will help you.
 
Share this answer
 
Comments
abhiamity86 21-May-11 3:16am    
Thanks For Reply..

But it is still showing error in UPDATE statement

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900