Adding column-name dynamically in alter table

Question

I am developing a web application and need to use ALTER Table in it. Here is my code:

ALTER TABLE [tablename] ADD [column-name] [Type] DEFAULT [default-value] NULL/NOT NULL

Now i want to change [column-name] dynamically by a text box. How can i do that? In addition i used @name in AddWithValue, but it doesn't work! Can any body help me? Thanks

here is my whole code:

SqlConnection sqlconn4 = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString);
                   SqlCommand sqlcomm4 = new SqlCommand("ALTER TABLE aspnet_Membership ADD @column int DEFAULT 0 NOT NULL", sqlconn4);

                    sqlcomm4.Parameters.AddWithValue("@column", TextBox1.Text);
                    sqlconn4.Open();
                    sqlcomm4.ExecuteNonQuery();
                    sqlconn4.Close();

you have two questions actually and should ask separately. for addwithvalue not working you should provide source code. — adt, Feb 11 '13 at 09:32
and do you wanto to just change your column name or add new columns to table. questions subject is changing but example source code is about adding new columns. — adt, Feb 11 '13 at 09:45
Ohh my god!! thank you for mentioning that. I was awake whole the night! it is about adding. — Saeid, Feb 11 '13 at 09:51
http://stackoverflow.com/questions/1351651/how-to-use-c-sharp-to-add-a-column-for-a-table-of-sql-server then use alter table add like in linked answer and get a sleep — adt, Feb 11 '13 at 09:55
But there is one more problem, I want to get the name from a text box! that was my main problem. — Saeid, Feb 11 '13 at 09:58
I have edited answer hope it helps. Somehow it didn't work with addwithvalue. so you have to sanitize input before using function — adt, Feb 11 '13 at 10:14

score 2 · Answer 1 · answered Feb 11 '13 at 09:07

2

It's possible only with dynamic SQL queries:

Declare @SQL VarChar(1000)

SELECT @SQL = 'ALTER TABLE my_table ADD ' + @column + ' INT'

Exec (@SQL)

answered Feb 11 '13 at 09:07

Roman Badiornyi

1,509
14
28

adt · Accepted Answer · 2013-02-11T10:14:08.120

with sp_rename procedure you can change column name.

sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'

http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/

public void AddNewColumn(string tableName, string columName, string dataType)
    {
        string commandText = String.Format("ALTER TABLE {0} ADD {1} {2} ", tableName,columName,dataType);

        using (var sqlConnection = new SqlConnection("===="))
        {
            var cmd = sqlConnection.CreateCommand();
            cmd.CommandText = commandText;

            sqlConnection.Open();
            cmd.ExecuteNonQuery();
        }
    }

score 0 · Answer 3 · answered Feb 11 '13 at 09:06

0

try the below thing..

dataTable.Columns["column-name"].ColumnName = "updated column-name";

answered Feb 11 '13 at 09:06

Neel

11,625
3
43
61

How can i add a column to it? – Saeid Feb 11 '13 at 10:04

score 0 · Answer 4 · answered Feb 11 '13 at 09:16

Hmmm, perhaps posting a sample of your code would help. For example, is your column-name variable a static variable? If the variable is not declared static it will be declared with its default value every time the Alter table method is called and the changes made to it by the textbox textChanged event.

On the other hand if your current problem is trying to get the dynamic portion to work are you using the textChanged event of your textbox to alter the column-name variable?

Once again more information would be greatly appreciated.

Adding column-name dynamically in alter table

4 Answers4

Linked