Add New Column to Database by using codes

[klover]

Hi all,

I'm wondering is it possible to create a new column to an existing database using vb .net 2008 express and SQL CE version?

I've tried with these codes below with no success

        If TextBox1.Text = "" Then
            MsgBox("Enter Column Name", MsgBoxStyle.Question, "Add Column")
        Else
            Dim str_addcolumn As String
            Dim com_addcolumn As New SqlServerCe.SqlCeCommand
            Dim adp_addcolumn As New SqlServerCe.SqlCeDataAdapter
            Dim con_addcolumn As New SqlServerCe.SqlCeConnection

            con_addcolumn.ConnectionString = My.Settings.CBDesignProgramConnectionString
            str_addcolumn = "ALTER TABLE GeneralNF ADD " & TextBox1.Text & " NVARCHAR(100)"
            com_addcolumn = con_addcolumn.CreateCommand
            com_addcolumn.CommandText = str_addcolumn
            con_addcolumn.Open()
            
            Try
                com_addcolumn.ExecuteNonQuery()
                con_addcolumn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
                con_addcolumn.Close()
            End Try
        End If

If there is something I did wrong please correct me.

Thanks in advance.

[techgnome]

How do you know it's not working? Do you get errors or something? Or what?

-tg

[klover]

How do you know it's not working? Do you get errors or something? Or what?

-tg

Thanks for your prompt reply.

Basically after those codes I have a GridView Refresh

ModifyGeneralNF.Show()        
ModifyGeneralNF.TableAdapterManager1.UpdateAll(ModifyGeneralNF.CBDesignProgramDataSet1)
ModifyGeneralNF.GeneralNFDataGridView.Refresh()

The Update was not shown. Also I check the database data also showed no update.

Not too sure whats wrong or something wrong with my codes?

Thanks again

[jmcilhinney]

Adding new columns to your database tables doesn't magically change anything about your TableAdapters. The TableAdapters were created during development based on the schema of the database you were connected to at that time. They are classes that are compiled into your application.

If you want to be able to change the database schema at run time and have that reflected in your application then you can't use a typed DataSet. You're going to have to use an untyped DataSet and DataAdapters insteda of TableAdapters. That way the DataTable schema can be created at run time instead of pre-ordained at design time.

That said, adding columns to a database is something that you should never do lightly. It's a major change that is usually accompanied by a recompilation of the application because a different data schema almost always means different logic.

You should explain to us what this column represents and why you're adding it because I think it's 99% certain that your database structure is quite wrong and you shouldn't be adding a column at all.

[klover]

Adding new columns to your database tables doesn't magically change anything about your TableAdapters. The TableAdapters were created during development based on the schema of the database you were connected to at that time. They are classes that are compiled into your application.

If you want to be able to change the database schema at run time and have that reflected in your application then you can't use a typed DataSet. You're going to have to use an untyped DataSet and DataAdapters insteda of TableAdapters. That way the DataTable schema can be created at run time instead of pre-ordained at design time.

That said, adding columns to a database is something that you should never do lightly. It's a major change that is usually accompanied by a recompilation of the application because a different data schema almost always means different logic.

You should explain to us what this column represents and why you're adding it because I think it's 99% certain that your database structure is quite wrong and you shouldn't be adding a column at all.

Ok thanks for the info..ill let the customers know.

[dozd]

I try to add column too. The problem that appears is in DataSet Updating.
I use this code:

  Ds.Tables("MyTableName").Columns.Add("NewColumnName")
        Adapter.Update(Ds, "MyTableName")

This code add new column, but when the program restarts new column despair.
Database is made in Access.

Suggestions???

[jmcilhinney]

I try to add column too. The problem that appears is in DataSet Updating.
I use this code:

  Ds.Tables("MyTableName").Columns.Add("NewColumnName")
        Adapter.Update(Ds, "MyTableName")

This code add new column, but when the program restarts new column despair.
Database is made in Access.

Suggestions???

Adding a column to a DataTable has no effect on the database whatsoever. It was never intended to. If you want to add a column to a table in a database then you have to execute an ALTER TABLE statement, as klover has shown in the first post.

I reiterate though, adding new columns to a database is NOT something that should be happening often. It is a MAJOR change to an application and would usually be accompanied by a new version of the application itself. If you feel you need to add a new column to a database table during the normal course of your application's execution then chances are that your database is incorrectly designed. The number of rows in a table can change often but the number of columns should almost never change.

[dozd]

Thank you very much for help jmcilhinney
Thanks for uderstanding too