adding row with OLEDB SQL database

Question

I wrote a code to add row that getting the values from textboxes

i wrote a code but it doesn't work propatly. when Idebugg it i get this error:"Syntax error in the INSERT INTO command" i don't know how to make it works. Heres the code:

        private void addRow_Click(object sender, EventArgs e)
    {
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Projects_2012\\Project_Noam\\Access\\myProject.accdb";
        OleDbConnection myConnection = new OleDbConnection(connectionString);
        string myAddingQuery = string.Format("insert into tblCodons(codonsCodon1, codonsCodon3, " +
        "codonsTriplet1, codonsTriplet2, codonsTriplet3, codonsTriplet4, " +
        "codonsTriplet5, codonsTriplet6, codonsFullName" +
        ") values ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", 
        codon1.Text, codon3.Text, triplet1.Text, triplet2.Text,
         triplet3.Text, triplet4.Text, triplet5.Text, triplet6.Text,
        fullName.Text);
        OleDbCommand myCommand = new OleDbCommand(myAddingQuery);
        myCommand.Connection = myConnection;
        myConnection.Open();
        myCommand.ExecuteNonQuery();
        myCommand.Connection.Close();

    }

TNX to the helpers!

Answer 1

It's probably complaining because you're not quoting any of the values. However, you shouldn't be including the values directly in the SQL anyway - you should use a parameterized statement. That way:

• You separate code from data, which is always a good thing.
• You avoid SQL injection attacks.
• You don't need to worry about conversion formats for things like dates and numbers.

See the docs for OleDbCommand.Parameters for a full example. Your code would probably become something like:

private void addRow_Click(object sender, EventArgs e)
{
    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + 
        "Data Source=C:\\Projects_2012\\Project_Noam\\Access\\myProject.accdb";
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        string sql = "insert into tblCodons(codonsCodon1, codonsCodon3, " +
            "codonsTriplet1, codonsTriplet2, codonsTriplet3, codonsTriplet4, " +
            "codonsTriplet5, codonsTriplet6, codonsFullName" +
            ") values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        using (OleDbCommand command = new OleDbCommand(sql, connection))
        {
            connection.Open();
            command.Parameters.AddWithValue("codon1", codon1.Text);
            command.Parameters.AddWithValue("codon3", codon3.Text);
            command.Parameters.AddWithValue("triplet1", triplet1.Text);
            command.Parameters.AddWithValue("triplet2", triplet2.Text);
            command.Parameters.AddWithValue("triplet3", triplet3.Text);
            command.Parameters.AddWithValue("triplet4", triplet4.Text);
            command.Parameters.AddWithValue("triplet5", triplet5.Text);
            command.Parameters.AddWithValue("triplet6", triplet6.Text);
            command.Parameters.AddWithValue("fullName", fullName.Text);
            command.ExecuteNonQuery();
        }
    }
}