使用 OleDbCommandBuilder 时访问 SQL 语法错误

问题描述

我将使用 C# 中的 OleDbDataAdapter 在 Access 数据库中插入数据，但出现错误消息INSERT INTO 命令中的语法错误

I am going to INSERT data in Access Database using OleDbDataAdapter in C# but i got an error with message Syntax Error in INSERT INTO Command

BackgroundWorker worker = new BackgroundWorker();
OleDbDataAdapter dbAdapter new OleDbDataAdapter();
OleDbConnection dbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\PMS.mdb");
worker = new BackgroundWorker();
worker.WorkerReportsProgress = true;
worker.DoWork += InsertJob;
worker.ProgressChanged += InsertJobCompleted;
worker.RunWorkerAsync(args);

而 InsertJob 函数是:

And InsertJob Function is:

private void InsertJob(object sender, DoWorkEventArgs e)
{
     var args = (InsertJobArgs)e.Argument;
     try
        {
            dbAdapter.SelectCommand = new OleDbCommand("SELECT * FROM Sheet", dbConnection);
            dbAdapter.Fill(args.DataTable);
            var builder = new OleDbCommandBuilder(dbAdapter);
            var row = args.DataTable.NewRow();

            row["UserName"] = args.Entry.UserName;
            row["Password"] = args.Entry.Password;
            args.DataTable.Rows.Add(row);

            dbAdapter.InsertCommand = builder.GetInsertCommand();
            dbAdapter.Update(args.DataTable);
            builder.Dispose();
        }
        catch (Exception ex)
        {
            args.Exception = ex;
            worker.ReportProgress(0, args);
            return;
        }
        worker.ReportProgress(100, args);
}

我在线收到错误:dbAdapter.Update(args.DataTable);

我尝试用visual studio调试它，发现所有的InsertCommand参数值都是null

I tried to debug it with visual studio and found that All the InsertCommand Parameters Values are null

在调用 dbAdapter.Update(args.DataTable);

dbAdapter.InsertCommand.Parameters[0].Value = args.Entry.UserName;
dbAdapter.InsertCommand.Parameters[1].Value = args.Entry.Password;

推荐答案

试试这个:

就在该行之后

var builder = new OleDbCommandBuilder(dbAdapter);

添加两行

builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";

这将告诉 OleDbCommandBuilder 将表名和列名括在方括号中，生成一个 INSERT 命令，如

That will tell the OleDbCommandBuilder to wrap table and column names in square brackets, producing an INSERT command like

INSERT INTO [TableName] ...

代替默认表单

INSERT INTO TableName ...

如果任何表或列名称包含空格或有趣"字符，或者它们恰好是 Access SQL 中的保留字，则需要方括号.(就您而言，我怀疑您的表有一个名为 [Password] 的列，而 PASSWORD 是 Access SQL 中的保留字.)

The square brackets are required if any table or column names contain spaces or "funny" characters, or if they happen to be reserved words in Access SQL. (In your case, I suspect that your table has a column named [Password], and PASSWORD is a reserved word in Access SQL.)

这篇关于使用 OleDbCommandBuilder 时访问 SQL 语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！