CODEWITHSUNDEEP
c#visual-studio-2010
wendy
wendy

Reputation: 161

Syntax error in INSERT statement (access database)

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

    }

Upvotes: 1

Views: 131

Answers (1)

Steve
Steve

Reputation: 216353

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

Upvotes: 2

Related Questions