Convert rows in a column in new colums access C#

Question

0.00/5 (No votes)

See more:

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 8-Oct-17 22:13pm

Arturo Vianni

Updated 8-Oct-17 22:42pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Thomas Nielsen - getCore · Accepted Answer · 2017-10-08T22:42:00

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
            }