Dynamically Add Columns to Existing DataTable - C# Discussion Boards

Re: Dynamically Add Columns to Existing DataTable [modified]

29-Jul-08 2:20

Hi Guys. I'm trying to add a column to an existing DataTable. When running the code I don't get no error message or anything but when checking the Table the column has not been added. Any ideas why?

Below the test code.

conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = T:\Nasmeea\PSSZ.mdb";
conn.Open();
DataAdapter = new OleDbDataAdapter("Select * From [DivInfo]", conn);
DataTable dataTable = new DataTable("DivInfo");
DataColumn dValue = new DataColumn();
DataColumn dMember = new DataColumn();
dValue.ColumnName = txtboxColumns.Text;
dValue.DataType = Type.GetType("System.String");
dataTable.Columns.Add(dValue);
dataTable.Columns.Add(dMember);
conn.Close();
MessageBox.Show("Column Added");

Excellence is doing ordinary things extraordinarily well.

paas29-Jul-08 3:02

paas

29-Jul-08 3:02

Edit: On second thought, I suspect it is far more complicated than what I initially wrote, and will now remove. Since this is an Access MDB, you need to add permanent tables and columns to the database using ActiveX data objects; I am reasonably certain you can not permanently add tables or columns to Access MDBs via OLEDB. I know there have been some posts on this subject recently in this forum, a search for those posts should show the way.

modified on Tuesday, July 29, 2008 9:15 AM

MumbleB29-Jul-08 3:08

29-Jul-08 3:08

Hi pass. It didn't. I still don't get the Column created. I have changed the code as follows. I'm not sure if I am selecting the column.

private void btnAddColumn_Click(object sender, EventArgs e)
{
    conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = T:\Nasmeea\PSSZ.mdb";
    conn.Open();
    DataAdapter = new OleDbDataAdapter("Select * From [DivInfo]", conn);
    DataColumn dValue = new DataColumn();
    DataTable dataTable = new DataTable("[DivInfo]");
    dValue.ColumnName = txtboxColumns.Text.ToString();
    dValue.DataType = Type.GetType("System.String");
    dataTable.Columns.Add(dValue);
    dataTable.AcceptChanges();
    conn.Close();
    MessageBox.Show("Column Added");
}

Now, within the Database PSSZ.MDB i have an existing table called DivInfo. Am I connecting correctly to the database and dataTable in the above code? Sorry, should probably have also said that I have taken the code from a sample on the web but they don't mention any of the connection strings etc.

Excellence is doing ordinary things extraordinarily well.

Re: Dynamically Add Columns to Existing DataTable [modified]

paas29-Jul-08 3:10

paas

29-Jul-08 3:10

Please see my edits to my original post.

I apologize for my misdirection.

Tuwing.Sabado29-Jul-08 3:07

29-Jul-08 3:07

Hi,

These are my review points.

1. You don't have a variable for DataAdapter.
2. What is the purpose of the DataAdapater if your codes are not filling this.
3. You don't have a columnName and data Type for dMember Variable.
4. Opening and Closing of Connection are not necessary for DataAdapter. Data
Adapter will automatically do this for you.

Here are the correct codes.

conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = T:\Nasmeea\PSSZ.mdb";
conn.Open(); // This is Optional because DataAdapter will automatically open the given connection.
DataAdapter da = new DataAdapter("Select field1, field2 From [DivInfo]", conn);
DataTable dataTable = new DataTable("System_Setting"); // Creating Untype Datatable. Expected count of fields are 2.
da.Fill(dataTable); // Fill the datatable

//Adding two fields.
DataColumn dValue = new DataColumn();
DataColumn dMember = new DataColumn();
dValue.ColumnName = "dValue";
dValue.DataType = Type.GetType("System.String");
dMember.ColumnName = "dMember";
dMember.DataType = Type.GetType("System.String");
dataTable.Columns.Add(dValue);
dataTable.Columns.Add(dMember);
conn.Close(); // This is Optional because DataAdapter will automatically close the given connection.

MessageBox.Show("Column Count:" + dataTable.Columns.Count.ToString());
MessageBox.Show("Row Count:" + dataTable.Rows.Count.ToString());

Regards,

modified on Tuesday, July 29, 2008 9:26 AM

MumbleB29-Jul-08 3:23

29-Jul-08 3:23

Hi AMrk. Thanks for the information. I have tried the changes above and it still doesn't work. D'Oh! | :doh:

Excellence is doing ordinary things extraordinarily well.

Tuwing.Sabado29-Jul-08 3:35

29-Jul-08 3:35

Hi,

I wondering what went wrong!

I've tried on my oracle database and it is working perfectly before posting my solution to your problem.

Here are my exact code which almost the same with your codes.

System.Data.OracleClient.OracleConnection conn = new OracleConnection();
conn.ConnectionString = "Data Source=testDB;User ID=Claims;Password=claim2be;";
OracleDataAdapter da = new OracleDataAdapter("Select Key,Value From System_Setting", conn);
DataTable dataTable = new DataTable("System_Setting");
da.Fill(dataTable);
DataColumn dValue = new DataColumn();
DataColumn dMember = new DataColumn();
dValue.ColumnName = "dValue";
dValue.DataType = Type.GetType("System.String");
dMember.ColumnName = "dMember";
dMember.DataType = Type.GetType("System.String");
dataTable.Columns.Add(dValue);
dataTable.Columns.Add(dMember);

The output of my codes for column count is 4 my columns are Key,Value,dMember and dValue.

Please be specific with regards of actual and expected output, errors and othere information which will help us to resolve your problem.

Thanks,

MumbleB29-Jul-08 9:50

29-Jul-08 9:50

HI mate. I think it has to do with OLEDB. Apparently you can not create extra columns with it. When trying to create the Column, specifying dValue and dMember to have the same Column names it throws an error saying that it can not create as it already exist but when checking the Table the column doesn't exist. One can apparently not create a permanent column with OLEDB.

Thanks for the help and advice.

Excellence is doing ordinary things extraordinarily well.

J a a n s29-Jul-08 3:12

J a a n s

29-Jul-08 3:12

How you are checking whether the columns are added or not? From your code there should be two columns in the dataTable before it goes of of scope. Also I could not see the use of dataadapter/connection, Is there any which is affecting the existing variable?

*jaans

MumbleB29-Jul-08 3:29

Re: Dynamically Add Columns to Existing DataTable [modified]

29-Jul-08 3:29

This is the modified code I have now. I only connect to the DB on button click even. From the count at the bottom of the code it picks up the correct table etc but it for some reason doesn't add the new column. I currently have 29 columns in the DB with approximately 2000 rows of data. It returns all that showing that I am successfully connecting and it is reading the correct table.

private void btnAddColumn_Click(object sender, EventArgs e)
{
    conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = T:\Nasmeea\PSSZ.mdb";
    conn.Open();
    OleDbDataAdapter da = new OleDbDataAdapter("Select * From [DivInfo]", conn);
    DataTable dataTable = new DataTable("DivInfo");
    da.Fill(dataTable);
    DataColumn dValue = new DataColumn();
    dValue.ColumnName = txtboxColumns.Text.ToString();
    dValue.DataType = Type.GetType("System.String");
    dataTable.Columns.Add(dValue);
    dataTable.AcceptChanges();
    conn.Close();
    MessageBox.Show("Column Count:" + dataTable.Columns.Count.ToString());
    MessageBox.Show("Row Count: " + dataTable.Rows.Count.ToString());
}

Excellence is doing ordinary things extraordinarily well.

Tuwing.Sabado29-Jul-08 3:43

29-Jul-08 3:43

Hi,

Please add these codes after the da.Fill(dataTable);

MessageBox.Show("Column Count:" + dataTable.Columns.Count.ToString());            
MessageBox.Show("Row Count: " + dataTable.Rows.Count.ToString());

You have 4 messagebox. 2 before adding the dynamic columns and another 2 after the adding.

Let us know what are the outputs of the messagebox

modified on Tuesday, July 29, 2008 9:53 AM

MumbleB29-Jul-08 9:53

29-Jul-08 9:53

Hi Mark. Apparently I have to use ActiveX objects to create the Columns as OLEDB can not add the columns on the "fly" permanently. I will have to readup on this.

Thanks for the help and advice. The code is correct and should work on any other type of database except for Access Databases.

Once I have found a solution I will post again.

Excellence is doing ordinary things extraordinarily well.

MumbleB29-Jul-08 19:21

auto select, insert, update, delete ... option in winforms

29-Jul-08 19:21

Hi MArk. I added the MessageBox stuff in after the da.Fill and it pops up with 28 columns. After the conn.Close() statement the count is 29 which menas that it is creating the Column but for some reason it is not saving the change which I find very strange. I have tried to see if I could find something on ADO to do this but everything I have come across does exactly the same thing. Nothing different and most of the code samples and articles refer to DATAGRID and not the DB itself.

Excellence is doing ordinary things extraordinarily well.

dream_liner_7e729-Jul-08 1:56

dream_liner_7e7

29-Jul-08 1:56

hello, hru? I want to know is it possible in winforms c# to add sqldatasource like control that gives all the options of select, insert, update, delete, stored procedures etc etc .... OR any other alternate solution to make my development simple.

thanks in advance.

Re: auto select, insert, update, delete ... option in winforms

Tuwing.Sabado29-Jul-08 3:16