消息:system.data.oledb.oledbexception:Query表达式中的语法错误(缺少操作员)

Question

public static void WriteData(string colName,string data)
{
    using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Constants.FileName + ";Extended Properties="Excel 8.0;HDR=NO;IMEX=3;READONLY=FALSE""))
    {
        // string query = String.Format("INSERT INTO  [DataSet$]({0}) VALUES ({1})",colName, data);
        string query = String.Format("INSERT INTO  [DataSet$]({0}) VALUES ({1})", colName, data);
        cn.Open();
        OleDbCommand cmd = new OleDbCommand(query, cn);
        cmd.Parameters.AddWithValue("@colName", colName);
        cmd.Parameters.AddWithValue("@data", data);
        cmd.ExecuteNonQuery();
        cn.Close();
        cn.Dispose();
    }
}

Answer 1

您要做的是混合动态查询生成和查询磁头。您无法使用命令参数在查询中填充列名。参数仅用于将值传递到查询。

因此，您需要做的就是使用string.Format仅在查询中放置列名并使用命令参数仅将数据传递到查询。

public void WriteData(string colName, string data)
{
    using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Constants.FileName + ";Extended Properties="Excel 8.0;HDR=NO;IMEX=3;READONLY=FALSE""))
    {
        string query = string.Format("INSERT INTO [DataSet$] ({0}) VALUES (@data)", colName);
        // The query variable will have following value assuming colName=token
        //INSERT INTO [DataSet$] (token) VALUES (@data)
        cn.Open();
        //Now use OleDbCommand to pass value of @data to the query.
        OleDbCommand cmd = new OleDbCommand(query, cn);
        cmd.Parameters.AddWithValue("@data", data);
        cmd.ExecuteNonQuery();
        cn.Close();
    }
}

这应该帮助您解决问题。

Answer 2

public static void UpdateData(string keyName,string colName,string data)
    {
        using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Constants.FileName + ";Extended Properties="Excel 8.0;HDR=NO;IMEX=3;READONLY=FALSE""))
        {
            // string query = String.Format("INSERT INTO  [DataSet$]({0}) VALUES ({1})",colName, data);
                connection.Open();
                string commandString = String.Format("UPDATE [DataSet$] SET colName ='{0}' WHERE keyName = '{1}'",@data,@keyName);
                OleDbCommand cmd = new OleDbCommand(commandString, connection);
            cmd.Parameters.AddWithValue("@data", data);
            cmd.Parameters.AddWithValue("@keyName", keyName);
            connection.Close();
                connection.Dispose();
            }
        }
    }