Click here to Skip to main content
15,881,600 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I would like to write code to add a DataColumn to a DataTable, but when I save the DataTable, it does not include the new DataColumn.

It saves any new DataRows I add, but not the DataColumns.

Can somebody please tell me what I am doing wrong?
C#
public partial class Form1 : Form
{
    MyDatabase DB;
    DataTable Products;
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        DB = new MyDatabase();
        DB.Open(@"C:\Users\Grant\Documents\Database.accdb");
        Products = DB.GetTable("Products");
        AddColumn();
        AddRow();
        DB.Save(Products);
    }

    private void AddColumn()
    {
        DataColumn Column = new DataColumn();
        Column.DataType = Type.GetType("System.String");
        Column.ColumnName = "TestColumn";
        Products.Columns.Add(Column);
    }

    private void AddRow()
    {
        DataRow Row;
        Row = Products.Rows.Add(1, "B", "C");
    }
}

class MyDatabase
{
    // The following program has to be installed on the computer
    // http://www.microsoft.com/downloads/en/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    private String provider = "Microsoft.ACE.OLEDB.12.0";
    private String source;
    private OleDbConnection connection;
    private String connectionString;
    private DataSet dataSet = new DataSet();
    private OleDbDataAdapter adapter;
    private OleDbCommandBuilder commandBuilder;

    public String Provider
    {
        get { return provider; }
        set { provider = value; }
    }
    public String Source
    {
        get { return Source; }
        set { source = value; }
    }
    public void Open(String Filename)
    {
        connectionString = @"Provider=" + provider + @";Data Source=" + Filename;
        connection = new OleDbConnection(connectionString);
        connection.Open();
        adapter = new OleDbDataAdapter();
    }
    public void BuildStrings()
    {
        commandBuilder = new OleDbCommandBuilder(adapter);
        adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
        adapter.InsertCommand = commandBuilder.GetInsertCommand();
        adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
    }
    public DataTable GetTable(String TableName)
    {
        adapter.SelectCommand = new OleDbCommand("SELECT * From " + TableName, connection);
        BuildStrings();
        adapter.Fill(dataSet, TableName);
        return dataSet.Tables[TableName];
    }
    public void Save(DataTable Table)
    {
        adapter.Update(Table);
        adapter.Update(dataSet, "Products");
    }
}
Posted

1 solution

You can not add new column/field to database table using dataset or datatable you might need to use "ALTER TABLE" with ADO.NET commands. Check below links


How Can I Insert New Column Into A Database Table Using SqlDataAdapter and DataTable?[^]

adding a column to a SQL table in VB using ADO.NET commands[^]
 
Share this answer
 
v2
Comments
Grant Mc 15-Sep-13 15:25pm    
I really appreciate this, thank you very much.
Mahesh Bailwal 16-Sep-13 5:21am    
you're welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900