使用OleDbDataAdapter的数据集和更新Access.mdb [英] Using OleDbDataAdapter and DataSet to update Access.mdb

问题描述

我试图更新一个简单的MS Access数据库。我得到上，搜索之后，我发现 Microsoft支持某些表的例外 - 语法错误。我认为这意味着列名的人使用的保留字。这似乎是的情况下，由于除了以GUID作为列名中的一个，一个保留字的那些所有的表的更新。该页面还规定，我应该使用OleDbAdapter和数据集，这应该解决的问题。不幸的是我无法改变的列的名称。那是我无法控制的，所以我有什么给我的工作。

我没有做数据库的工作了，我知道我的一切已经从网络连接（这可能不好的）例子教训。那么，什么是使用OleDbAdapter和数据更新数据库的正确方法？

我不认为我应该用数据表或OleDbCommandBuilder，我相信解决方案有什么做参数。但我googleing技能薄弱

  OleDbConnection的康恩=新的OleDbConnection（供应商= Microsoft.Jet.OLEDB.4.0;+ 
数据周守军=+源）; 
 conn.Open（）; 
 OleDbAdapter适配器=新OleDbDataAdapter的（SELECT * FROM+表，conn）;在
 OleDbCommandBuiler cmdBuiler =新的OleDbCommandBuilder（适配器）; 
的DataSet =新DatSet（）; 
 adapter.InsertCommand = cmdBuilder.GetInertCommand（真）; //这是必要的
 adapter.Fill（DataSet中，表） ; 
 DataTable中的dataTable = dataSet.Tables [表]; //我需要一个DataTable 
的DataRow行= dataTable的
行[属性] =领域; //这样做对所有属性。/场I认为这是错误的
 dataTable.rows.Add（行）; 
 adapter.Update（dataTable的）; //<  - 语法错误INSERT INTO语句中异常
  

解决方案

该问题可能是列名（特别是那些是保留字）的名字应该用方括号括起来。该OleDbCommandBuilder，当它会创建自己的InsertCommand，不会用方括号括名称，所以解决方案是手动定义OleDbDataAdapter的的的InsertCommand：

  adapter.InsertCommand =新的OleDbCommand（的String.Format（INSERT INTO {0}（[GUID]，[字段名]）VALUES（@ GUID，@字段名）;表），康涅狄格州）; 
  

定义参数为每列，然后手动添加参数的值;

  adapter.InsertCommand.Parameters.Add（新OleDbParameter（@ GUID，行[GUID]））; 
  

所以总结起来，为此，有一个名为GUID列的表，你应该尝试像以下内容：

  OleDbConnection的康恩=新的OleDbConnection（供应商= Microsoft.Jet.OLEDB.4.0;+ 
 数据周守军=+源）; 
 conn.Open（）; 
 
 OleDbDataAdapter的适配器=新OleDbDataAdapter的（SELECT * FROM+表，康涅狄格州）; 
 OleDbCommandBuilder cmdBuilder =新的OleDbCommandBuilder（适配器）; 
 
 adapter.InsertCommand =新的OleDbCommand（的String.Format（INSERT INTO {0}（[GUID]，[字段名]）VALUES（@ GUID，@字段名）;表），康涅狄格州） ; 
 
 DataTable中的dataTable =新的DataTable（表）; 
 adapter.Fill（dataTable的）; 
的DataRow行= dataTable.NewRow（）; 
列[字段名] = fieldValue方法; 
 //如：行[GUID] = System.Guid.NewGuid（）的ToString（）; //这样做对所有的属性/领域。 
 dataTable.Rows.Add（行）; 
 
 adapter.InsertCommand.Parameters.Add（新OleDbParameter（@字段名，行[字段名]））; 
 //如：adapter.InsertCommand.Parameters.Add（新OleDbParameter（@ GUID，行[GUID]））; 
 
 adapter.Update（dataTable的）; 
  

I am attempting to update a simple ms access database. I get an Exception on certain tables that, after searching, I found Microsoft Support - Syntax Error. I believe it means that one of the column names uses a reserved word. This seems to be the case, since all the tables update except the ones with "GUID" as one of the column names, a reserved word. This page also states that I should be using a OleDbAdapter and DataSet, which should solve the problem. Unfortunately I cannot change the name of the column. That is beyond my control, so I have to work with what is given me.

I haven't had to do work with databases much, and everything I know I've learned from examples from the internet (probably bad ones at that). So what is the proper way to update a database using OleDbAdapter and dataSet?

I don't think I should be using DataTable or OleDbCommandBuilder, and I believe the solution has something to do with parameters. But my googleing skills are weak.

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
                            Data Souce=" + source);
conn.Open();
OleDbAdapter adapter = new OleDbDataAdapter("SELECT * From " + table, conn);
OleDbCommandBuiler cmdBuiler = new OleDbCommandBuilder(adapter);
DataSet = new DatSet();
adapter.InsertCommand = cmdBuilder.GetInertCommand(true); // Is this necessary?
adapter.Fill( dataSet, table);
DataTable dataTable = dataSet.Tables[table]; // Do I need a DataTable?
DataRow row = dataTable.
row [ attribute ] = field; // Do this for all attributes/fields. I think this is wrong.
dataTable.rows.Add(row);
adapter.Update(dataTable); //<--"Syntax error in INSERT INTO statement." Exception

解决方案

The problem may be that the column names (especially those whose name are reserved words) should be surrounded by square brackets. The OleDbCommandBuilder, when it creates its own InsertCommand, doesn't surround the names with brackets, so a solution is to manually define the OleDbDataAdapter's InsertCommand:

adapter.InsertCommand = new OleDbCommand(String.Format("INSERT INTO {0} ([GUID], [fieldName]) Values (@guid,@fieldName);", table), conn);

Defining parameters for each column and then manually adding the parameter's values;

adapter.InsertCommand.Parameters.Add(new OleDbParameter("@guid",row["GUID"]));

So summing up, for the tables which have a column named "GUID", you should try something like the following:

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + 
"Data Souce=" + source);                          
conn.Open(); 

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * From " + table, conn);
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapter);

adapter.InsertCommand = new OleDbCommand(String.Format("INSERT INTO {0} ([GUID], [fieldName]) Values (@guid,@fieldName);", table), conn);

DataTable dataTable = new DataTable(table);
adapter.Fill( dataTable);
DataRow row = dataTable.NewRow();
row [ fieldName ] = fieldValue;
// eg: row [ "GUID" ] = System.Guid.NewGuid().ToString(); // Do this for all attributes/fields.
dataTable.Rows.Add(row);

adapter.InsertCommand.Parameters.Add(new OleDbParameter("@fieldName",row[fieldName]));
// eg: adapter.InsertCommand.Parameters.Add(new OleDbParameter("@guid",row["GUID"]));

adapter.Update(dataTable);

这篇关于使用OleDbDataAdapter的数据集和更新Access.mdb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！