I am using VB.NET within .NET framework V1.1 to write a windows application to Access XP database (using JET OLE DB 4.0 connection) and can not edit the records.
I used the data form wizard to generate a form to see/edit the tables. After creating the table I can "LOAD" the table to the DataGrid (also tried seperate text boxes). I can navigate and see all the records and sometimes able to delete them. However, when I try to update (change) or add a new record, I get error messages. The first mesage I get is "Syntax error in INSERT INTO Satatement", just after changing a letter of a field and clicking "update" button. I thought the update statement that the wizard generated is somehow errorenous. I then changed the field names by adding a letter "z" in front of them and got "Update requires a valid UpdateCommand when passed DataRow collection with modified rows" error. I further played with the table and reduced it to two colum table, one with autonumber id field and one text description field. When I regenerated the form, the wizard could not generate update or delete command. This was also reported by OleDbAdapter configuration. I then changed the autonumber colum to text and removed the primary key. This time update and delete commands could not be created but I can neither insert a row. It gives me a similar error.
Every time I perform a test, I delete the data set and the form I created previousy and start from scratch. I make the design changes to the access tables within access. My test application has only two forms, one main from which calls the dataform under test.
I have been through few books and could not find any particular subject regarding to this issue, and worse, almost all the examples are on MS SQL server, not on Access database. My gut feeling is that something is utterly wrong in data adapter or the wizard handling the Jet Engine V4.0. I also checked the Microsoft site to see if there is any patch or Service Pack but there is none.
After looking for some information like a mad for almost 2 days, I found a bug in JET4 data adapter.
For some reason, update command gives wrong syntax error message when you try to update your table if you have one of your colums labeled as "Work" and that field is not autonumber (I tested number and text fields). When I change column label to "Workx" or make it autonumber field, it works OK. I also tested it with JET3.51 engine and it does not generate any error in any condition (at least the ones I tested). I installed the VS2003 into another computer and had the same problem.
If anyone is interested in, I included the VS files and the two databases in the attachment. You may regenerate the problem yourself by simply creating a data form, only following the defauls on the data form wizard.
Do you know the mechanizim to report this bug to Microsoft, and an official known issues list at Microsoft web site?
Reply With Quote
