Update database related issue

Question

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 20-May-11 19:42pm

abhiamity86

Updated 21-May-11 8:12am

Sandeep Mewara

v2

Add a Solution

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.

NuttingCDEF 22-May-11 10:15am

Just one thought (although not strictly relevant to the specific problem you have) . . . why are you storing a plain text password in your database? This introduces an unnecessary security weakness - one of the ones that one suspects Sony might have learnt about the hard way ;-)

Hash it (with a nice strong hash algorithm - so not MD5!) and store the hash. And preferably add a salt before you do the hash to protect against rainbow table attacks (and store the salt as well). To check a submitted password, salt it / hash it and compare the hash values.

4 solutions

Add a Solution

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

CS2011 · Answer 1 · 2011-05-21T08:21:00

Solution 2

Try following

C#

string update = "UPDATE [Login] SET Password = '" + TextBox2.Text + " ' WHERE UserId = '" + dtr[0].ToString() + "'";

Posted 21-May-11 8:21am

CS2011

Kim Togo · Answer 2 · 2011-05-22T00:31:00

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

R. Hoffmann · Answer 3 · 2011-05-22T02:04:00

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

prem shanker verma · Answer 4 · 2011-05-20T20:13:00

Solution 1

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.

Update database related issue

4 solutions

Solution 2

Solution 3

Solution 4

Solution 1

Add your solution here

Preview 0

Update database related issue

4 solutions

Solution 2

Solution 3

Solution 4

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us