locked
Dynamically added columns get removed after reopening application RRS feed

  • Question

  • Hi guys, i have made a database application (vb.net) that uses three databases (items,users,transactions). My client told me that he needs to be able to add multiple columns in the items database so he can categorize things better. I used this code to add a new column to the database: 

    Me.DBDataSet.Items.Columns.Add(InputBox("Name:", "Insert Column Name"), Type.GetType("System.String"))

    I have also used this line of code (on form load) so the datagridview automatically generates the columns when a new column has been added to the database:

    Me.ItemsDataGridView.AutoGenerateColumns = True

    Problem is that everytime i reopen the app the added columns are not there. It's like they get erased on program close.

    I use Microsoft.Jet.OLEDB.4.0. as Provider and the database is an MS Access database file (.mdb)

    What am i missing?

    Thanks in advance

    Andrew


    
    Thursday, February 27, 2014 10:50 PM

Answers

  • The DBDataSet is an item in your VS application and not the table in the Access database.  You added the column locally, but not in the remote Access database.


    jdweng

    Friday, February 28, 2014 12:00 AM
  • Basically you can't. Since you want to dynamicaly add columns to the underlaying access database.

    So you will have to use sql alter table commands. Even your strongly typed dataset will have to be adjusted every time a column is added (good luck with that :-))

    You really should reconsider you strategy. Maybe already add a couple of extra columns to the table and use those, instead of adding columns at runtime


    • Edited by Rbie Friday, February 28, 2014 9:52 AM
    • Proposed as answer by Cor Ligthert Friday, February 28, 2014 9:56 AM
    • Marked as answer by i_programm_for_fun Friday, February 28, 2014 12:08 PM
    Friday, February 28, 2014 9:51 AM

All replies

  • The DBDataSet is an item in your VS application and not the table in the Access database.  You added the column locally, but not in the remote Access database.


    jdweng

    Friday, February 28, 2014 12:00 AM
  • thanks for the quick response jdweng,

    so how would i do that?

    *all of my dataset is strongly typed (i don't use sql queries or sql commands in code) so if you can tell me how to do it without sql that 'd be great :)

    Andrew

    Friday, February 28, 2014 9:29 AM
  • Basically you can't. Since you want to dynamicaly add columns to the underlaying access database.

    So you will have to use sql alter table commands. Even your strongly typed dataset will have to be adjusted every time a column is added (good luck with that :-))

    You really should reconsider you strategy. Maybe already add a couple of extra columns to the table and use those, instead of adding columns at runtime


    • Edited by Rbie Friday, February 28, 2014 9:52 AM
    • Proposed as answer by Cor Ligthert Friday, February 28, 2014 9:56 AM
    • Marked as answer by i_programm_for_fun Friday, February 28, 2014 12:08 PM
    Friday, February 28, 2014 9:51 AM
  • so if i get it right i can use the alter table commands to add the needed columns to the remote access database and then find a way to make these changes visible by my app...right?

    Friday, February 28, 2014 10:23 AM
  • Right,

    But if you are really going to add them dynamically (bad idea imo) you will also have to adjust your typed dataset to reflect those changes(this is the real horror imo).

    Which kind of negates the reason to use a strongly typed set in the first place.




    • Edited by Rbie Friday, February 28, 2014 10:42 AM
    Friday, February 28, 2014 10:34 AM