Using OleDbDataAdapter and DataSet to Update Access.mdb

Question

I am trying to update a simple ms access database. I get an exception in some tables that after searching I found Microsoft Support - syntax error . I believe this means that one of the column names uses a reserved word. This is similar to the fact that all tables are updated, except for those for which the "GUID" is one of the column names reserved by the word. This page also indicates that I should use the OleDbAdapter and DataSet, which should solve the problem. Sorry, I cannot change the name of the column. This is beyond control, so I have to work with what is given to me.

I didn’t have to work much with databases, and I learned everything that I know from examples from the Internet (possibly bad ones). So what is the correct way to update the database using OleDbAdapter and dataSet?

I don’t think I should use DataTable or OleDbCommandBuilder, and I believe the solution has something to do with the 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 

Answer 1

The problem may be that column names (especially those whose name is reserved) must be surrounded by square brackets. OleDbCommandBuilder, when it creates its own InsertCommand, does not surround the names with parentheses, so the solution is to manually define the OleDbDataAdapter 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 parameter values;

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

So, for tables that 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); 

Answer 2

As for problem # 1. Try to fully qualify the column name, that is, table.columnName (which fixes the problem in MySQL, so maybe it is in Access), try putting [] around the column name.

Choosing * is usually a bad option for specifying column names and using aliases. For example, select Column1 as "Column1", Column2 - "Column2" .... this will greatly simplify the work with your dataset and data, since you can access a column by its alias instead of column indices.

I believe that the DataAdapter is much more useful to populate datasets than to actually modify the database. I recommend something like:

 string updateQuery = "Update ..... Where ...."; //do your magic here OldDbcommand command = new OleDbCommand(updateQuery); command.Connection = conn; conn.Open(); con.ExecuteNonQuery(); conn.Close(); 

You can populate your data set with an adapter, and then do as I just did to execute your update commands in the database.

Answer 3

A good place to start would be to use DataSetDesigner and Typed DataSets to run try going through: http://msdn.microsoft.com/en-us/library/ms171893(v=vs.80).aspx

A good long-term approach is to use Sql Server Express instead, then you will have the choice of using: Entity Framework, Linq To Sql or Still continue to use DataSetDesigner and Typed DataSets.