none
VS2008 Data Adapter will not Create Insert Command RRS feed

  • Question

  • I am using Visual Basic 2008. I have several OleDbDataAdpaters connecting to an MS Access 2003 database. I recently added a new field to one of the tables and when I used the Data Adapter Configuration Wizard to add that field, it failed to create an Insert command. The only change I made was to add one Boolean field to the table and then, try to add it to the Adapter. Everything worked fine before I made this change.

    I have no idea why the Insert command was not created, so if anyone has any suggestions, I would greatly appreciate it.

    Sunday, May 26, 2013 10:45 PM

Answers

  • Thanks. I did that and it sort of worked. Anyway, I have it working now, so I'll explain what I did in excessive detail.

    Since the last time I used the wizard to configure the data, I had added two new fields. I had added a boolean field called "DelMe" a while back. I was using that in Access to flag bad records that I wanted to delete from the database. Since that work was all being done directly in Access, there was no need to add it to the data adapter.

    Last night, I added a new field "WebStoreItem", also a boolean field to the database. I did need this in my program, so I ran the Data Adapter Configuration Wizard to add it. When I did that, I didn't notice that it failed to create the Insert command. Based on subsequent attempts, I can see why I missed that - there is no error message, in fact, the message says the configuration was successful. It lists the commands that were created, but the Insert command is not among them. It's absence is my only clue that there's anything wrong.

    So, I deleted the last two boolean fields. The configuration wizard worked then, creating the Insert command just as it should. I added a new numeric field and again, the wizard worked just fine. I deleted that numeric field and added a boolean field and the wizard again failed to create an Insert command. This was true whether I added the field to the adapter or not. It's mere presence in the table was enough to cause the failure.

    I already have several boolean fields in my table, so I'm not sure why this one is a problem. Maybe there's a maximum number of boolean fields allowed? If so, I'd be surprised if I set the record for most boolean fields in a table. As far as I can see, this has to be a bug in VS.

    Anyway, I have successfully added the field I need. I just had to create it as a numeric instead of a boolean.

    BTW, I also tried doing this with a Table Adapter instead of a Data Adapter and I ran into the same problem.

    Monday, May 27, 2013 4:46 AM

All replies

  • I probably should add that the Delete, Update and Select commands were all generated properly.
    Sunday, May 26, 2013 10:58 PM
  • Try removing the new column, see if the insert statement will generate or not. If this works try adding the column back in again, do you get the same problem?

    Also, was there an error message?

    What is the name of the new column?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Monday, May 27, 2013 1:40 AM
  • Thanks. I did that and it sort of worked. Anyway, I have it working now, so I'll explain what I did in excessive detail.

    Since the last time I used the wizard to configure the data, I had added two new fields. I had added a boolean field called "DelMe" a while back. I was using that in Access to flag bad records that I wanted to delete from the database. Since that work was all being done directly in Access, there was no need to add it to the data adapter.

    Last night, I added a new field "WebStoreItem", also a boolean field to the database. I did need this in my program, so I ran the Data Adapter Configuration Wizard to add it. When I did that, I didn't notice that it failed to create the Insert command. Based on subsequent attempts, I can see why I missed that - there is no error message, in fact, the message says the configuration was successful. It lists the commands that were created, but the Insert command is not among them. It's absence is my only clue that there's anything wrong.

    So, I deleted the last two boolean fields. The configuration wizard worked then, creating the Insert command just as it should. I added a new numeric field and again, the wizard worked just fine. I deleted that numeric field and added a boolean field and the wizard again failed to create an Insert command. This was true whether I added the field to the adapter or not. It's mere presence in the table was enough to cause the failure.

    I already have several boolean fields in my table, so I'm not sure why this one is a problem. Maybe there's a maximum number of boolean fields allowed? If so, I'd be surprised if I set the record for most boolean fields in a table. As far as I can see, this has to be a bug in VS.

    Anyway, I have successfully added the field I need. I just had to create it as a numeric instead of a boolean.

    BTW, I also tried doing this with a Table Adapter instead of a Data Adapter and I ran into the same problem.

    Monday, May 27, 2013 4:46 AM
  • First off thanks for sharing these steps as others may find them useful. I doubt there is a max number of Boolean fields that may be used, instead it sounds like a bug yet unlikely there is a fix for this as VS2008 has no more updates.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Monday, May 27, 2013 4:51 AM