添加行与OLEDB SQL数据库

Question

我写了一个代码来添加从文本框中获取值的行

我写了一个代码，但它不能正常工作。当idebug它我得到这个错误:"语法错误在INSERT INTO命令"我不知道该怎么做。下面是代码:

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

向助手致敬!

Answer 1

这可能是在抱怨，因为您没有引用任何值。但是，无论如何都不应该在SQL中直接包含这些值—应该使用参数化语句。这种方式:

• 将代码与数据分开，这总是一件好事。
• 避免SQL注入攻击。
你不需要担心日期和数字的转换格式。

参见OleDbCommand.Parameters的文档以获得完整的示例。你的代码可能会变成这样:

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