日期:2014-05-18  浏览次数:21178 次

OleDbCommandBuilder无法创建合适的Access数据库语句
我需要对一个Access数据库的表中数据进行插入、修改和删除,想通过把DataGridView的数据源设置为该数据库,自动实现这些操作。

代码如下:
C# code

public partial class EditAttributeTableData : Form
    {
        private string m_strMdbFilePath = "";
        private string m_strAttrTableName = "";
        private ITable m_table = null;

        private OleDbDataAdapter m_odba = null;
        private DataSet m_ds = null;

        /// <summary>
        /// 构造函数,保存了需要修改的属性表
        /// </summary>
        /// <param name="table">需要修改的属性表</param>
        public EditAttributeTableData(string strMdbFilePath, string strAttrTableName, ITable table)
        {
            this.m_strMdbFilePath = strMdbFilePath;
            this.m_strAttrTableName = strAttrTableName;
            this.m_table = table;

            InitializeComponent();

        }

        /// <summary>
        /// 窗口加载消息处理函数
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void EditAttributeTableData_Load(object sender, EventArgs e)
        {
            // 清除原数据
            this.dataGridView1.Columns.Clear();
            this.dataGridView1.Rows.Clear();

            // 设置连接字符串
            string connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin",
                m_strMdbFilePath);

            // 创建数据源
            OleDbConnection conn = new OleDbConnection(connString);
            m_odba = new OleDbDataAdapter(String.Format("SELECT * FROM {0} WHERE OBJECTID >= 0", m_strAttrTableName), conn);
            m_ds = new DataSet();
            m_odba.Fill(m_ds, m_strAttrTableName);
            
            // 设置DataGridView数据源
            dataGridView1.DataSource = m_ds;
            dataGridView1.DataMember = m_strAttrTableName;
        }


        /// <summary>
        /// 确认写入数据库按钮命令处理函数
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (m_ds.HasChanges()) {
                    // 如果数据源有改动, 更新数据库
                    OleDbCommandBuilder scb = new OleDbCommandBuilder(m_odba);
                    System.Console.WriteLine(scb.GetInsertCommand().CommandText);
                    m_odba.Update(m_ds, m_strAttrTableName);
                } 
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message, "数据编辑");
                return;
            }
            MessageBox.Show("操作成功", "数据编辑");
            this.Close();
        }
    }



问题是scb.GetInsertCommand().CommandText的结果为:INSERT INTO 共用宗使用权 (Field ID, 标识码) VALUES (?, ?),
VALUES均为?,不是有效的数据。

导致出现插入异常:OleDbException {"INSERT INTO 语句的语法错误。"} 

请各位大神指教,谢谢!

------解决方案--------------------
也可以使用
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";

http://dotnet.aspx.cc/file/Insert-DataSet-Into-Access.aspx