Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
551 views
in Technique[技术] by (71.8m points)

c# - Using OleDbDataAdapter and DataSet to update Access.mdb

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...