How to handle a syntax error in an Access INSERT statement and a .NET DataGridView control?

Question

I'm working with Access (.accdb), and a Windows Forms application, written in C#.
I get this error when I'm trying to insert data into a database table.

System.Data.OleDb.OleDbException:Sintax error in INSERT INTO statement. adaptador.InsertCommand.ExecuteNonQuery();

I have tried to go through the direct route and update method but coming up with the same error!

My code:

private void btnCronograma_Click(object sender, EventArgs e)
{
    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=mydatabase.accdb");
    string sql;
    int idProyecto, idMes, meta, real;

    OleDbDataAdapter adaptador = new OleDbDataAdapter();

    //for (int i = 0; i < this.dataGridView8.Rows.Count - 1; i++)
    //{
    foreach (DataGridViewRow row in dataGridView8.Rows)
    {
        DataGridViewComboBoxCell combo3 = row.Cells["idProyecto"] as DataGridViewComboBoxCell;
        DataGridViewComboBoxCell combo4 = row.Cells["idMes"] as DataGridViewComboBoxCell;

        MessageBox.Show(combo3.Value.ToString());
        MessageBox.Show(combo4.Value.ToString());

        idProyecto = int.Parse(combo3.Value.ToString());
        idMes = int.Parse(combo4.Value.ToString());

        meta = int.Parse(dataGridView8.Rows[0].Cells[3].Value.ToString());
        real = int.Parse(dataGridView8.Rows[0].Cells[4].Value.ToString());
        MessageBox.Show(meta.ToString());
        MessageBox.Show(real.ToString());

        //for (int i = 0; i < this.dataGridView8.Rows.Count - 1; i++)
        //{
        sql = "INSERT INTO IndicadorProyecto (idProyecto, idMes, meta, real) VALUES('" + idProyecto + "' , '" +
                idMes + "' , '" + meta + "' , '" + real + "') ";

        //sql = "INSERT INTO IndicadorProyecto (idMes, meta, real) VALUES('" + idMes + "' , '" + meta + "' , '" + real + "') ";

        if (combo3 == null)
        {
            MessageBox.Show("No se pudo convertir");
        }
        else if (combo4 == null)
        {
            MessageBox.Show("No se pudo convertir");
        }
        else
        {

        }

        try
        {
            conn.Open();
            adaptador.InsertCommand = new OleDbCommand(sql, conn);
            adaptador.InsertCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            conn.Close();
        }
        //}
    }
}

SLaks · Accepted Answer · 2010-11-21 01:23:51Z

2

You should not concatenate strings into SQL statements.
By doing so, you break your code and create a SQL injection vulnerability.

Instead, you should use parameters.

For example:

using (var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=mydatabase.accdb"))
using (var command = new OleDbCommand(@"INSERT INTO IndicadorProyecto (idProyecto, idMes, meta, real) VALUES(?, ?, ?, ?") {
    command.Parameters.AddWithValue("a", idProyecto);
    command.Parameters.AddWithValue("b", idMes);
    command.Parameters.AddWithValue("c", meta);
    command.Parameters.AddWithValue("d", real);

    conn.Open();
    command.ExecuteNonQuery();
}

answered Nov 21, 2010 at 1:23

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

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

1 Comment

RolandTumble Over a year ago

I've always considered concatenating strings to be acceptable if you're in control of the strings based on, for instance, an option choice.

Collectives™ on Stack Overflow

How to handle a syntax error in an Access INSERT statement and a .NET DataGridView control?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related