CODEWITHSUNDEEP
c#databasesyntax
Ralph Ignacio
Ralph Ignacio

Reputation: 43

Syntax error in UPDATE statement (MS Access Database)

This is the image

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.

Upvotes: 0

Views: 48

Answers (2)

mybirthname
mybirthname

Reputation: 18137

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.

Upvotes: 1

Mostafiz
Mostafiz

Reputation: 7352

Check out your command spacing after username and password

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

Upvotes: 0

Related Questions