I have table abc

In taht I want to add colunm at run time. I have done some code but there is an error

SqlConnection cn = new SqlConnection() ;
            cn.ConnectionString  = "";
            cn.Open();

            SqlCommand cmd = new SqlCommand("alter table abc add" + textBox1.Text.ToString() + "varchar(500)",cn);
            cmd.ExecuteNonQuery();

            SqlCommand cm = new SqlCommand("insert into abc" + textBox2.Text.ToString() + "values(@a)");
            cm.Parameters.AddWithValue("@a", textBox2.Text);
            cm.ExecuteNonQuery();
            cn.Close();

I have error in alter table : invalid column name textbox1.text.

and also How can i store data only in that column till last row in the table.

Recommended Answers

All 13 Replies

Just out of curiosity, did you craft your "alter" statement in a SQL Query window, then copy it and adjust it in your C# dev environment? I have found that to be the most effective way to get stuff into code in the past, in many languages, especially when you're replacing parts of the SQL with variables from your forms.

Okay, so, that being said, what was the value in textBox1.Text when you executed this? A lot of IDE's set the default text in a control to the control's name, and you have to clear it out or replace it with something else either at design time or run time. It is highly likely that it is using that default value to assemble your SQL statement, resulting in an invalid column name, hence the error.

Assuming that you're using MS SQL Server, you can surround a column name with square brackets to tell SQL Server that any old combination of characters should be treated as an object. So, modify your code to look like this:

SqlCommand cmd = new SqlCommand("alter table abc add [" + textBox1.Text.ToString() + "] varchar(500)",cn);

That should let you add a column name.

For your second question, I didn't really understand what you're after. Can you clarify?

Yes I want to create column run time. when user enter the column name in the textbox. the name enter in the texbox the column gentrate in datatable in database of sql server2005. and second think is the coulmn genetate in the datatable it should save some data provided by the textbox2. The data enter in the textbox2 that must be save in that paricular entire column only. like update but only for that column. I dont know how to save the data in only one column.

add a space after add before ending quotes in line 5

SqlCommand cmd = new SqlCommand("alter table abc add " + textBox1.Text.ToString() + " varchar(500)",cn);

otherwise your code is right also

SqlCommand cm = new SqlCommand("insert into abc " + textBox2.Text.ToString() + " values(@a)");

but this line save the data only in that row. I want to save the data in entire column which is created from textbox1. how to do that.

You mean put the same data in every row?
The use the SQL statement

SqlCommand cm = new SqlCommand("update abc set " + textBox1.Text.ToString() + " = '" + textBox2.Text.ToString() + "'");

In This queary how I use where condition. and where i use this line:
cm.Parameter.AddValueWith(a,textbox.text);
cm.ExecuteNonQueary();
or this is not needed. but how data will update.

you can't use insert query here you have to use update query dear.

SqlCommand cmd = new SqlCommand("alter table abc add " + textBox1.Text.ToString() + " varchar(500)",cn);
SqlCommand cm = new SqlCommand("update abc set "+textBox1.Text+"='"+value+"' where condition'

It show error message i.e value not decleare in current context.

I have make some changes in this queary.

but there is an error incorrect synter near 'addr1'// column name written in the textbox1.

sqlcommand cm = new sqlcommand("update abc set"+textbox1.text.tostring+"=@"+textbox1.text.tostring+""+"where"+textbox1.text.tostring+"'="+textbox1.text.tostring+"'",cn);
cm.parameter.AddValueWith("@"+textbox1,textbox2);

I have use this code i dont know weather it is right or wrong.

can you please make it correct.

look update query is like "update table_name set column_name=value_you_want where condition" and in your statement column_name and value you want to add and the condition all are the same?

but here we dont know the column name. then how can we write the column name in the where condition.
The queary in like this if we know the column name:
update abc set name=@name where id = 90
but here i want to save data in that column till the last row data in the table. and here column is added at run time and data is also adding at run time.

I think Now you know the problem.

Sorry for the lots of confusion.

Hey, its start working now. It was my mistake sorry.

Because I am putting it in where condition. and there was no need for where condition.

Here is code: as you told me.

SqlCommand cm = new SqlCommand("update abc set"+textbox1.text+"='"+textbox2.text);
cm.ExecuteNonQueary();

How full i am.

I am so sorry for giving you trouble.

Thank You for reply. Thank you.

that's ok :) if your problem is solved please mark this thread as solved

my problem is solved but just for knowledge can you tell me how to put where condition when column name is not known if in future i need it. please

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.