Syntax error in UPDATE statement (MS Access Database)

Question

private void button1_Click(object sender, EventArgs e)
{
    if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || textBox6.Text == "")
    {
        MessageBox.Show("Please Complete all Field");
    }
    else
    {
        if ((textBox3.Text == textBox4.Text) && (textBox5.Text == textBox6.Text))
        {
            connect.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "update Table2 set username ='" + textBox2.Text + "', password ='" + textBox6.Text + "' where AID='" + aid + "'";
            command.ExecuteNonQuery();
            MessageBox.Show("Admin account update complete!");
            connect.Close();
        }
        else
        {
            MessageBox.Show("Field dont match each other!");
        }
    }
}
private void button2_Click(object sender, EventArgs e)
{
    if (textBox7.Text == "" || textBox8.Text == "" || textBox9.Text == "")
    {
        MessageBox.Show("Please Complete all Field");
    }
    else
    {
        if (textBox8.Text == textBox9.Text)
        {
            connect.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "insert into Table2 (username,password) values('" + textBox7.Text + "','" + textBox9.Text + "')";
            command.ExecuteNonQuery();
            MessageBox.Show("Admin account add complete!");
            connect.Close();
            textBox7.Text = "";
            textBox8.Text = "";
            textBox9.Text = "";
        }
    }
}

I'm getting Syntax error in UPDATE statement on command.ExecuteNonQuery(); on both buttons. I've already created new table on my database but still the same. Also double check the spelling and its all good.

mybirthname · Accepted Answer · 2016-10-19 06:07:56Z

1

Use Parameterized queries and you will not have this problem. Also you will be protect from SqlInjection.

command.CommandText = @"update Table2 set username=@UserName, password=@Password where AID=@ID";

command.Parameters.AddWithValue("@UserName", textBox2.Text);
command.Parameters.AddWithValue("@Password", textBox6.Text);
command.Parameters.AddWithValue("@ID", aid);

Here the second CommandText

command.CommandText = @"insert into Table2 (username,password) Values (@UserName,@Password)";

command.Parameters.AddWithValue(@UserName, textBox7.Text);
command.Parameters.AddWithValue(@Password, textBox9.Text);

The parameters should be in same order like you write them in the query for OleDb. Also you should not worry about ' when you use parameters, your queries looks better and easy to read. Write text box names in the future it will be easier to understand for other users.

edited Oct 19, 2016 at 6:07

answered Oct 19, 2016 at 5:09

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mybirthname Over a year ago

@RalphIgnacio you should do that for all your queries, is the name of the table Table2 ?

Ralph Ignacio Over a year ago

Its all working now, the problem is in the MS Access, I created a new column on the same table and just transfer the data. Thanks :)

mybirthname Over a year ago

@RalphIgnacio you can mark the answer as correct if helped you.

Mostafiz · Accepted Answer · 2016-10-19 05:21:45Z

0

Check out your command spacing after username and password

command.CommandText = "update Table2 set username='" + textBox2.Text + "', password='" + textBox6.Text + "' where AID='" + aid + "'";

edited Oct 19, 2016 at 5:21

answered Oct 19, 2016 at 5:08

Mostafiz

7,3523 gold badges30 silver badges42 bronze badges

2 Comments

Mostafiz Over a year ago

@RalphIgnacio cross check table name, column names and input textbox text

Ralph Ignacio Over a year ago

Its all working now, I created a new column on the same table and transfer the data and its working now. Thanks :)

Collectives™ on Stack Overflow

Syntax error in UPDATE statement (MS Access Database)

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related