Ms Access Syntax Error in Update Statement

This may help: "UPDATE Customer SET [Name] = 'John Smith', ShortName = 'John', [UserId] = 'johnsmith', [Password] = 'jspass', [Type] = 'Person' WHERE CustKey = 1" The whole point is in the square brackets.

Access treats many words are reserved words. Just for fun, try changing your "password" and "type" fireld names to something else....

The exact text of my update command in the dataadapter is:- "UPDATE Customer SET Name = 'John Smith', ShortName = 'John', UserId = 'johnsmith', Password = 'jspass', Type = 'Person' WHERE CustKey = 1" The message that appears in the exception is "Syntax error in UPDATE statement." The code statements I examined are:- OleDbDataAdapter da = new OleDbDataAdapter(); try { da.UpdateCommand = new OleDbCommand(UpdateString, this.OpenDataSource()); da.UpdateCommand.ExecuteNonQuery(); } catch (Exception e) { throw e; }

Did you insert handler for the rowupdated event of the dataAdapter.update method? Or do you wait until that method call return's and look in an exception object's message property? Do you look in the DataRow for the HasError and ErrorMessage properties? You may find a more specific message in the event or the DataRow gets replaced by a more generic message on return from the Update method. Anyway, if that don't work, shove a commandbuilder on and see what it generates and how that works, compare its generated SQL to your SQL.

I do not use the CommandBuilder I create my SQL string dynamically at run time. I have used the debugger to examine the resulting SQL command just before it is executed. I can see nothing wrong with it and when I use the exact same command in Access it works perfectly. I have no objects with spaces in their names. My SQL looks like this:- UPDATE Customer SET Name = 'davemac', ShortName = 'dave', Type = 'person' WHERE CustKey = 1 Name, Shortname and Type are all text fields and CustKey is numeric. The commands SELECT * FROM Customer and SELECT * FROM Customer WHERE CustKey = 1 both work fine. The message I get is "Syntax error in UPDATE statement" I'm still lost!

You need to get the generated command and the exception message. One cause of this problem can be that you have object names that have spaces in them such as table name "order details". In that case you need auto-generated database update commands to put brackets around it like so - [Order Details]. You do that by use of CommandBuilder's QuotePrefix and QuoteSuffix properties, which you set to [ and ] respectively. So are you using the commandbuilder object to generate you database update/insert/delete commands for you? The DataAdapter object generates rowupdating and rowupdated events just before and after sql execution per row, so you can put in methods to handle those events and print off the sql, and further you can look at the associated DataRow, and after execution you will get DataRow error fal g set and error message set. So you can get this info programmatically or you can watch in teh debugger.