Click here to Skip to main content
15,882,209 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


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