Error from OleDbCommandBuilder query expression when updating Access database integer value

Question

Using C#, I had a problem with a database I use directly in Access. I used an OleDbDataAdapter, OleDbCommandBuilder and DataTable. The update command was generated by the OleDbCommandBuilder. Using this update command would generate a syntax error in the UPDATE statement.

As a test, I created a real simple Access database with 2 fields: Name and Job. Using the exact code from what I described above (except for obvious changes), the update command worked fine with this. Then I created another database with 5 fields: Name, Job, Id, Year and Country but this got the same syntax error as above. I commented out different field assignments until I found that it is the "Year" field that will causes the error. The Id and Year fields are both integers and the others are text. It will update the Id field and text fields, but if I include the Year field it will generate the error. It makes no sense!

Can any one make sense of this and help please??? Is there a way to see the SQL update expression that the OleDbCommandBuilder is using?

Thanks!

Valhalla

Can you please post the code that you are having an issue with? — SnareChops
– SnareChops, Commented Oct 9, 2014 at 23:17

user3806621 · Accepted Answer · 2014-10-10 00:10:54Z

2

'Year' is a reserved word in MS Access DB, see http://support.microsoft.com/kb/286335 . You can rename field Year into something else like '_Year'.

answered Oct 10, 2014 at 0:10

user3806621

2981 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Noémie Lord · Accepted Answer · 2014-10-10 01:28:39Z

0

You must put field names that correspond to reserved names in MS Access (like Year) in bracket (like this : [Year]).

answered Oct 10, 2014 at 1:28

Noémie Lord

7794 silver badges22 bronze badges

9 Comments

valhalla Over a year ago

Ahhhhhh.....relief alas!!!! :) lol Man...I guess I will have to stop and ask people for directions when driving too now.... Loool! I wasted so much time from overlooking something basic.... Arrggggggg! :) Thank you! Thank you ! Thank you!!!! This is probably the problem with the original database I was working with as I had field names like Type Length etc...... It's funny though that these names, including "Year" did not cause a problem when using this directly in Access... Yes brackets I forgot about that...Thanks to all of you that answered this!

valhalla Over a year ago

This is what I get for getting our of programming for the last 8 years.

valhalla Over a year ago

Oooops.....I was relieved too soon. I changed all the fields in the original db that were in question and ran the program with the changed version but it still gets a syntax error if I change even one text field. Here is the what it is showing in the error dialog box that pops up:

valhalla Over a year ago

Syntax error (missing operator) in query expression '((MOVIE = ?) AND ((? = 1 AND Genre IS NULL) OR (Genre = ?)) AND ((? = 1 AND Box Name IS NULL) OR (Box Name = ?)) AND ((? = 1 AND _Type IS NULL) OR (_Type = ?)) AND ((? = 1 AND _LENGTH IS NULL) OR (_LENGTH = ?)) AND ((? = 1 AND Viewed IS NULL) OR (Viewed ='.

Noémie Lord Over a year ago

What are all the '?' you put in there ?

|

Collectives™ on Stack Overflow

Error from OleDbCommandBuilder query expression when updating Access database integer value

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related