ACCESS的System.Data.OleDb.OleDbException: INSERT INTO 语句的语法错误

使用ADO.net将使用于SQL Sever的程序移植到单机的Access数据库里老是发生异常，提示OleDataException，说是缺少一个参数~~莫非是system.data.oleDb和system.data.sqlclient中实现数据更新的方法不一致？？试了用Command来代替InsertCommand实现也是发生同样的错误~~

这两天一直在寻思这个问题，直到今天才在网络上找到了解决的方法，觉得有点不可思议~~在网络上看了看，也是学习ADO.net的人常犯的错误。所以写此日志，以提醒自己与各位路人切记切记~~

数据库：

    EmpID 字符串型

    Last    字符串型

    Dept 字符串型

    Salary 整型数值

实现在此数据库中加入新行的代码

string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=D:\我的文档\数据库文件\Bonus.mdb;";
            OleDbConnection connection = new OleDbConnection(strCon);
            string strEmplist = "SELECT * FROM EmpList";
            string strBonus = "SELECT * FROM Bonus";
            OleDbDataAdapter adEmplist = new OleDbDataAdapter(strEmplist, connection);
            OleDbDataAdapter adBonus = new OleDbDataAdapter(strBonus, connection);
            DataSet dataset = new DataSet("Emplist");
            DataTable emplist = new DataTable("emplist");
            DataTable bonus = new DataTable("bonus");
            try
            {
                connection.Open();
                adEmplist.SelectCommand = new OleDbCommand(strEmplist, connection);
              

                adEmplist.Fill(emplist);
                adBonus.Fill(bonus);
                dataset.Tables.Add(emplist);
                dataset.Tables.Add(bonus);
               

            }
            catch (SystemException e)
            {
                Console.WriteLine(e.Message.ToString());
            }
            DataRow row = emplist.NewRow();
            row["EmpID"] = "A005";
            row["Last"] = "Smith";
            row["Dept"] = "Marketing";
            row["salary"] = 5000;
            emplist.Rows.Add(row);
            try
            {
                adEmplist.InsertCommand = new OleDbCommand("insert into [EmpList]([EmpID],[Last],[Dept],[Salary]) VALUES('A060','Smith','Marketing','5000')", connection);
                adEmplist.InsertCommand.CommandType = CommandType.Text;

                Console.WriteLine(adEmplist.InsertCommand.CommandText);
               // adEmplist.InsertCommand.ExecuteNonQuery();
                adEmplist.Update(dataset.Tables["EmpList"]);

                Console.WriteLine("数据库更新成功");
                connection.Close();
            }
            catch (OleDbException e)
            {
                Console.WriteLine(e.Message.ToString());
            }

总结：在System.Data.oleDb 命名空间下使用SQL语句要在字段名与表名加“【】”

insert into [EmpList]([EmpID],[Last],[Dept],[Salary]) VALUES('A060','Smith','Marketing','5000')"nsert into [EmpList]([EmpID],[Last],[Dept],[Salary]) VALUES('A060','Smith','Marketing','5000')"

下载说明