[2005] Add column to SQL database

[ghall426]

I have figured out how to add a column to an existing database in VB code by using a SQL statement like this:

Dim mycmd As New SqlCommand("ALTER TABLE myTable ADD NewCol int", conMain)
conMain.Open() : mycmd.ExecuteNonQuery() : conMain.Close()

But shouldn't I be able to do it via VB code? There is an add column function:

mydataset.Tables(0).Columns.Add("NewCol", GetType(Integer))

It appears to add the column to the database in memory but I can't get it to update the actual database. I've tried various update commands but nothing works.

[techgnome]

Nope... doesn't work that way... adding a filed to a dataset doesn't add it to the database, nor should it. You have to run the proper DDL commands (ALTER TABLE, etc...) in order to do that.

-tg

[ghall426]

Thanks for the reply. What is the purpose of the dataset.tables.columns.add function then if you can't really add a column with it?

[jmcilhinney]

The purpose of adding a column to a DataTable is exactly that: adding a column to a DataTable. How do you think that DataTable's get created when you retrieve data from the database? The schema of the result set is read and a DataTable is created with a matching schema. How is that done? Columns are added to it. How is that done? By calling the Columns.Add method of the DataTable.

Apart from that, not every DataTable will correspond directly with a table in a database. What if you were to retrieve data into a DataTable and then you wanted to add a column that was the sum of two other columns? How would you do that? The same way.