CODEWITHSUNDEEP
c#ms-accesssyntax-erroroledbsql-update
Ariel Seah
Ariel Seah

Reputation: 65

Why I get syntax error in this update statement?

I wanted to update a table in my m/s access database where my the user entered a new password in order to replace the old one but i have syntax error in the update statement. Please help!

public partial class resetPassword : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void SubmitButton_Click(object sender, EventArgs e)
        {
            string userName = (string) Session["username"];

        string str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\JetStar\database\JetstarDb.accdb";
        var con = new OleDbConnection(str);
        con.Open();

        string pwd = Request.Form["conPassword"];
        OleDbCommand cmd = new OleDbCommand("UPDATE [users] SET password = '" + pwd + "' WHERE username = '" + userName + "'", con);

        try
        {
            cmd.ExecuteNonQuery();
            MessageBox.Show("Your password has been changed successfully."); 
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            con.Close();
        }
    }
}

Upvotes: 3

Views: 271

Answers (2)

Soner Gönül
Soner Gönül

Reputation: 98868

Probably this happends because password is a reserved keyword on Microsoft Access. You should use it with square brackets as [password]

But more important

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Don't store your passwords as a plain text. Read: Best way to store password in database

Use using statement to dispose your OleDbConnection and OleDbCommand.

using(OleDbConnection con = new OleDbConnection(str))
using(OleDbCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "UPDATE [users] SET [password] = ? WHERE username = ?";
    cmd.Parameters.Add("pass", OleDbType.VarChar).Value = pwd;
    cmd.Parameters.Add("user", OleDbType.VarChar).Value = userName;
    con.Open();
    try
    {
        cmd.ExecuteNonQuery();
        MessageBox.Show("Your password has been changed successfully."); 
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
}

Upvotes: 6

paxdiablo
paxdiablo

Reputation: 882626

92.3% (a) of all DB problems become obvious if you just print the command before you use it, and read the error message.

So replace:

OleDbCommand cmd = new OleDbCommand("UPDATE [users] SET password = '" + pwd + "' WHERE username = '" + userName + "'", con);

with something like:

String s = "UPDATE [users] SET password = '" + pwd + "' WHERE username = '" + userName + "'";
Console.WriteLine(s);
OleDbCommand cmd = new OleDbCommand(s, con);

Then post the results of:

Response.Write(ex.Message);

for all to see, and examine what it tells you very carefully.

(a) A statistic I just plucked out of nowhere - actual value may be wildly different.

Upvotes: 2

Related Questions