访问SQL语法错误使用OleDbCommandBuilder时 [英] Access SQL syntax error when using OleDbCommandBuilder

问题描述

我要插入C＃中使用OleDbDataAdapter的Access数据库中的数据，但我的消息得到一个错误的语法错误INSERT INTO命令

  BackgroundWorker的工人=新的BackgroundWorker（）;
OleDbDataAdapter的dbAdapter新OleDbDataAdapter的（）;
OleDbConnection的的DbConnection =新的OleDbConnection（供应商= Microsoft.Jet.OLEDB.4.0;数据源= E：\\\\ PMS.mdb）;
工人=新的BackgroundWorker（）;
worker.WorkerReportsProgress = TRUE;
worker.DoWork + = InsertJob;
worker.ProgressChanged + = InsertJobCompleted;
worker.RunWorkerAsync（参数）;
 

和InsertJob功能是：

 私人无效InsertJob（对象发件人，DoWorkEventArgs E）
{
     变参=（InsertJobArgs）e.Argument;
     尝试
        {
            dbAdapter.SelectCommand =新的OleDbCommand（SELECT * FROM表的DbConnection）;
            dbAdapter.Fill（args.DataTable）;
            VAR建设者=新的OleDbCommandBuilder（dbAdapter）;
            VAR行= args.DataTable.NewRow（）;            行[用户名] = args.Entry.UserName;
            行[密码] = args.Entry.Password;
            args.DataTable.Rows.Add（行）;            dbAdapter.InsertCommand = builder.GetInsertCommand（）;
            dbAdapter.Update（args.DataTable）;
            builder.Dispose（）;
        }
        赶上（异常前）
        {
            args.Exception =前;
            worker.ReportProgress（0，参数）;
            返回;
        }
        worker.ReportProgress（100，参数）;
}
 

我收到就行错误： dbAdapter.Update（args.DataTable）;

我试图与Visual Studio调试它，发现所有的InsertCommand参数的值空

和我想这个code调用 dbAdapter.Update（args.DataTable）之前手动插入;

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

解决方案

试试这个：

行后，立即

  VAR建设者=新的OleDbCommandBuilder（dbAdapter）;
 

添加两行

  builder.Quote preFIX =[;
builder.QuoteSuffix =];
 

这将告诉 OleDbCommandBuilder 包裹在方括号表和列名，如

产生INSERT命令

  INSERT INTO [表名] ...
 

而不是默认的形式

  INSERT INTO表名...
 

方括号是必需的，如果任何表或列名包含空格或搞笑的字符，或者如果他们碰巧在访问SQL保留字。 （在你的情况，我怀疑你的表中有一个名为[密码]列，密码是访问SQL保留字。）

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);

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);
}

I recieve Error on line : dbAdapter.Update(args.DataTable);

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

And I tried to insert it manually by this code before call to dbAdapter.Update(args.DataTable);

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

解决方案

Try this:

Immediately after the line

var builder = new OleDbCommandBuilder(dbAdapter);

add the two lines

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

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

INSERT INTO [TableName] ...

instead of the default form

INSERT INTO TableName ...

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.)

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