syntax error in insert into statement. c#

Question

Syntax error in insert into statement happens while I try to update my MS Access database.

• Database name = student
• Table name = student table

Code:

private void button1_Click(object sender, EventArgs e)
{           
        connection.Open();
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        command.CommandText = "INSERT INTO student table([Name],[Class])" + "values('" + textBox1 + "','" + textBox2 + "')";
        connection.Close();
        command.ExecuteNonQuery();
        MessageBox.Show("Data Saved");           
}

Answer 1

If your table name is more than one word, you need to use it with square brackets like [student table]

But much more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

And use using statement to dispose your connections and commands automatically instead of calling Close or Dispose methods manually.

private void button1_Click(object sender, EventArgs e)
{
    using(var connection = new OleDbConnection(connection))
    using(var command = connection.CreateCommand())
    {
        command.CommandText = @"INSERT INTO [student table]([Name],[Class]) 
                                VALUES(?, ?)";
        command.Parameters.AddWithValue("?", textBox1);
        command.Parameters.AddWithValue("?", textBox2);

        connection.Open();
        int count = command.ExecuteNonQuery(); 
        if(count > 0)
           MessageBox.Show("Data Saved");  
    }        
}

By the way, I suspect if these textBox1 and textBox2 are TextBox not a variable, you need to use them with their .Text properties.

Answer 2

• 1st you need to use textBox1.Text and textBox2.Text instead of textBox1 and textBox2 respectively.

• 2nd don't close the connection before executing the command.

• 3rd use parameters.

So to sum up:

OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = "INSERT INTO [student table] ([Name],[Class]) values(@par1,@par2)";
command.Paramaters.Add("@par1",textBox1.Text);  
command.Paramaters.Add("@par2",textBox2.Text);    
command.ExecuteNonQuery();
connection.Close();