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!

Jon Skeet · Accepted Answer · 2011-10-30 19:07:43Z

2

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

edited Oct 30, 2011 at 19:07

answered Oct 30, 2011 at 19:02

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

7 Comments

user1017315 Over a year ago

well, thank u @Jon Skeet for your responding,but it doesnt allow me to leave some of the values empty.there is any way to leave also some empty censors?

Jon Skeet Over a year ago

@user1017315: Just use an empty string. If the schema allows it, that should be fine.

user1017315 Over a year ago

i didn't understand. when i try to add new row from the access software'it allows me.

Jon Skeet Over a year ago

@user1017315: Well you haven't said what happens when you try it using this code...

user1017315 Over a year ago

when i'm using this code,and doesn't fill all the texbox(but i do fill some) I get this error:System.Data.OleDb.OleDbException (0x80004005): field tblCodons.codonsTriplet2 'can not be a zero-length string.

|

Collectives™ on Stack Overflow

adding row with OLEDB SQL database

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related