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
– Jon Skeet, Commented Jan 22, 2014 at 18:48

Community · Accepted Answer · 2017-05-23 12:03:46Z

2

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();
 }

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Jan 22, 2014 at 18:47

Steve

217k22 gold badges242 silver badges296 bronze badges

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

4 Comments

wendy Over a year ago

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?

Steve Over a year ago

Always use a parameterized query and leave the job of quoting to the framework code. It knows better

wendy Over a year ago

It helps Steve and also Jon Skeet, next time I'm gonna use parameterized query. Thank you very much for the trouble.

David R Tribble Over a year ago

Quick fix: string q = ... + "'" + x.ToString().Replace("'","''") + "'," + .... But it's still better to parameterized calls.

Collectives™ on Stack Overflow

Syntax error in INSERT statement (access database)

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related