Click here to Skip to main content
15,077,494 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,I'm trying do something like this:
I have a table name Container that have 2 colums by default

Column[key] Column2
|-------------| silos1|
|-------------| silos2|
|-------------| silos3|
|-------------| silos4|
|-------------| silos5|


I want when a click a button to add news columns foreach rows in Column 2 and check if the columns already exist.
Column[key] Column2 silos1 silos2 silos3 silos4 silos5
|-------------| silos1|
|-------------| silos2|
|-------------| silos3|
|-------------| silos4|
|-------------| silos5|

Until now to add news colums i use :



C#
OleDbCommand cmd = new OleDbCommand("ALTER TABLE Container ADD COLUMN silos1 NUMBER", con);
cmd.ExecuteNonQuery();


I'm using a datatable to fill a datagridview
C#
sda = new OleDbDataAdapter("Select * From Container  ", con);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(sda);
           



            dt = new DataTable();
            sda.Fill(dt);

            dataGridView1.DataSource = dt;


What I have tried:

----------------------------------------------------------------
Posted
Updated 8-Oct-17 22:42pm
v2

1 solution

Hi Arthuro

Wouldn't want to let my ui alter my database structure personally but then that's not the question, your alter statement appears wrong, the data type number is an olap type. In SQL you could choose int typically or numeric or something like that:
Data types (Transact-SQL) | Microsoft Docs[^]

then you should consider if the field can be nulled which when adding dynamically to a table with data is more or less mandatory. If you don't want it to be, you'll update the table with valid values afterwards and alter it not not allow.

Now we get

From
OleDbCommand cmd = new OleDbCommand("ALTER TABLE Container ADD COLUMN silos1 NUMBER", con);
cmd.ExecuteNonQuery();

To
OleDbCommand cmd = new OleDbCommand("ALTER TABLE Container ADD COLUMN silos1 int null", con);
cmd.ExecuteNonQuery();

Not too certain on the OleDbCommand, i personally always use the System.Data.SqlClient namespace and the SqlCommand

oh you're using MS Access, that brings memories. In that case the change is to use NUMERIC and you'll get

To
OleDbCommand cmd = new OleDbCommand("ALTER TABLE Container ADD COLUMN silos1 numeric", con);
cmd.ExecuteNonQuery();

And you won't have to consider if the field can be nulled

Then to add them dynamically i'd suggest using the datatable you're filling anyway as it's rather handy

C#
var dt = new DataTable();
            sda.Fill(dt);
            var nameOfList = new List<string>(dt.Columns.Count);
            foreach (DataColumn clmn in dt.Columns)
            {
                nameOfList.Add(clmn.Caption);
            }

            var toAddList = new List<string>();

            foreach(DataRow row in dt.Rows)
            {
                if (!nameOfList.Contains(row["Column[key]"]))
                {
                    toAddList.Add(row["Column[key]"].ToString());
                }
            }

            foreach(string nameOfKey in toAddList)
            {
                //TODO: Execute add column
            }
   
v2
Comments
Arturo Vianni 9-Oct-17 5:00am
   
How Convert all the rows in Conteiner in new colums ?
Thomas Nielsen - getCore 9-Oct-17 7:47am
   
Please see updated suggestion

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