Add New Column to Database by using codes

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

Code:

---

        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.:confused:

Thanks in advance.

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

-tg

Quote:

---

Originally Posted by techgnome

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

Code:

---

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? :confused:

Thanks again

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.

Quote:

---

Originally Posted by 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.

---

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

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

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???

Quote:

---

Originally Posted by dozd

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

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.

Thank you very much for help jmcilhinney
Thanks for uderstanding too