OleDb异常

Question

经过5个小时的搜寻，我找不到我的错误。我明白这个例外。怎么啦？

System.Data.OleDb.OleDbException类型的未处理异常发生在MyDictionary.exe中 附加信息: INSERT INTO语句中的语法错误。

我的代码：

public void Insert(Word word)
{
    string language=FindLanguage();
    try
    {
        command.CommandText ="INSERT INTO "+language+" ( Native , Foreign , Definition , AddingDate)  values ( '" + word.Native + "' , '" + word.Foreign + "' , '" + word.Definition + "' ,'" + word.AddingDate + "')";
            command.CommandType = System.Data.CommandType.Text;
            connection.Open();

            command.ExecuteNonQuery();
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        if (connection != null)
        {
            connection.Close();
        }
    }
}

Answer 1

您应该在插入statement.Also中使用参数，看起来像是缺少了command.Connection = connection;。请注意，您的SQL易于使用SQL注入。

command.CommandText ="INSERT INTO "+language+"([Native],[Foreign],[Definition],[AddingDate]) VALUES (@Native,@Foreign,@Definition,@AddingDate)";

command.Parameters.AddWithValue("@Native", word.Native);
command.Parameters.AddWithValue("@Foreign",word.Foreign);
command.Parameters.AddWithValue("@Definition",word.Definition);
command.Parameters.AddWithValue("@AddingDate",word.AddingDate);

command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
connection.Open();

command.ExecuteNonQuery();

Answer 2

在OleDb中，INSERT INTO语句的正确语法涉及SELECT子句的使用，即使您是在追加静态值。因此，您需要像下面的示例一样更改查询。

此外，如果不实际处理引发的异常，则不要构造try...catch..finally。为了便于处理，使用using() { }块代替。所以这里是：

public void Insert(Word word)
{
    string language=FindLanguage();

    using (var connection = new OleDbConnection("connection string goes here"))
    using (var command = new OleDbCommand...)
    {
        command.CommandText = @
            "INSERT INTO " + language + "(Native, Foreign, Definition, AddingDate)" +
            "SELECT '"
                + word.Native + "' AS Native, '" 
                + word.Foreign + "' AS Foreign, '" 
                + word.Definition + "' AS Definition, '"
                + word.AddingDate + "' AS AddingDate"
        ;

        connection.Open();

        command.ExecuteNonQuery();

        connection.Close();
    }
}