Syntax error in INSERT statement (access database)

Question

I think I got the insert syntax but I always got this error. When I try different projects that are similar, it works just fine. Can you help me?

private void addbtn_Click(object sender, EventArgs e)

    {
        if (idkaryawantxt.Text != "")
        {
            string q = "insert into Table1 (Nama,No_Identitas,Alamat,Lahir,Tanggal_Lahir,Telepon,Divisi,Aktif,Password) values ('" + namakaryawantxt.Text.ToString() + "','" + identitastxt.Text.ToString() + "','" + alamattxt.Text.ToString() + "','" + lahirtxt.Text.ToString() + "','" + tgllahirtxt.Text.ToString() + "','" + tlpntxt.Text.ToString() + "','" + divisitxt.Text.ToString() + "','" + aktiftxt.Text.ToString() + "','" + passwordtxt.Text.ToString() + "')";

            dosomething(q);

        }
    }

    private void dosomething(String q)
    {
        try
        {

            connect.Open();
            command.CommandText = q;
            command.ExecuteNonQuery();
            connect.Close();


            loaddata();
        }
        catch (Exception e)
        {
            connect.Close();
            MessageBox.Show(e.Message.ToString());
        }
    }


    //REFRESH
    private void loaddata()
    {
        datakaryawan.AllowUserToAddRows = false;

        datakaryawan.Rows.Clear();
        datakaryawan.Refresh();


        connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\C# Project\minor\Karyawan.accdb;Persist Security Info=False;";

        connect.Open();



        command.Connection = connect;
        command.CommandText = "SELECT * FROM Table1";

        OleDbDataReader reader = command.ExecuteReader();


        while (reader.Read())
        {
            datakaryawan.Rows.Add();

            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["ID_Karyawan"].Value = reader[0].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["Nama_Karyawan"].Value = reader[1].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["No_Identitas"].Value = reader[2].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["Alamat"].Value = reader[3].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["PoB"].Value = reader[4].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["DoB"].Value = reader[5].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["Telepon"].Value = reader[6].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["Divisi"].Value = reader[7].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["Aktif"].Value = reader[8].ToString();
            datakaryawan.Rows[datakaryawan.Rows.Count - 1].Cells["Password"].Value = reader[9].ToString();
        }




        connect.Close();

        idkaryawantxt.Text = datakaryawan.Rows[0].Cells[0].Value.ToString();
        namakaryawantxt.Text = datakaryawan.Rows[0].Cells[1].Value.ToString();
        identitastxt.Text = datakaryawan.Rows[0].Cells[2].Value.ToString();
        alamattxt.Text = datakaryawan.Rows[0].Cells[3].Value.ToString();
        lahirtxt.Text = datakaryawan.Rows[0].Cells[4].Value.ToString();
        tgllahirtxt.Text = datakaryawan.Rows[0].Cells[5].Value.ToString();
        tlpntxt.Text = datakaryawan.Rows[0].Cells[6].Value.ToString();
        divisitxt.Text = datakaryawan.Rows[0].Cells[7].Value.ToString();
        aktiftxt.Text = datakaryawan.Rows[0].Cells[8].Value.ToString();
        passwordtxt.Text = datakaryawan.Rows[0].Cells[9].Value.ToString();

    }

First thing to do: stop putting the values straight into your SQL. Use parameterized SQL instead. That *may* fix the problem, and it will certainly make it easier to see the problem. — Jon Skeet, Jan 22 '14 at 18:48

score 1 · Accepted Answer · edited May 23 '17 at 12:03

The word PASSWORD is reserved for MS-Access databases.
If you want to use it you need to encapsulate it in square brackets

 string q = @"insert into Table1 (Nama,No_Identitas,Alamat,Lahir,Tanggal_Lahir,
              Telepon,Divisi,Aktif,[Password]) values (.....)";

said that keep in mind that string concatenation to form a sql command is a bad practice and should be avoided at all costs using a parameterized query.

The worst problem with string concatenation to build sql commands is the possibility of Sql Injection attacks, but also strings that contain single quotes, dates and floating point values are a problem when you need to use their values to build a string concatenated query text.

For example, what happens if one of your text fields contains a single quote typed by your user?. Another syntax error because when you concatenate a string containing a quote you break the required syntax for the command.

So there is no other acceptable way than use a parameterized query

 string q = @"insert into Table1 (Nama,No_Identitas,Alamat,Lahir,Tanggal_Lahir,
              Telepon,Divisi,Aktif,[Password]) values (?,?,?,?,?,?,?,?,?,?)";
 using(OleDbConnection connect = new OleDbConnection(.....))
 using(OleDbCommand cmd = new OleDbCommand(q, connect)
 {
     connect.Open()
     cmd.Parameters.AddWithValue("@p1", namakaryawantxt.Text);
     ... so on for the other 8 parameters
     ... REMEMBER TO ADD THEM IN THE SAME ORDER OF THE PLACEHOLDERS ...
     cmd.ExecuteNonQuery();
 }

ah my bad I forgot to add, password in here is text type. But Tanggal_Lahir(dateofbirth) and Aktif are in date and time type, Do you think I got wrong in there? — wendy, Jan 22 '14 at 18:56
Always use a parameterized query and leave the job of quoting to the framework code. It knows better — Steve, Jan 22 '14 at 19:00
It helps Steve and also Jon Skeet, next time I'm gonna use parameterized query. Thank you very much for the trouble. — wendy, Jan 22 '14 at 19:36
Quick fix: `string q = ... + "'" + x.ToString().Replace("'","''") + "'," + ...`. But it's still better to parameterized calls. — David R Tribble, Jan 22 '14 at 23:04

Syntax error in INSERT statement (access database)

1 Answers1