asked on

Add Column to Database

I have a Visual Basic .Net Winforms project which has a database with a table and somehow it uses SQL Compact 3.5. The form has:

DataSet1
BindingSource
TableAdapter
TableAdapterManager
BindingNavigator

I want to add a column to the table in the database so I can store another piece of information.

I would like to deploy a new version of the program, and the program will update the user's existing *.sdf database file and add a new column to it.

Can't figure out how to do that from Visual Basic VB.Net

I have added the column to my DataSet1.xsd

and I added the column to Server Explorer > Data Connections > Tables > mytable > Columns

Program runs fine, uses default value for the new column, updates file *.sdf database file with other information. But the database file itself still doesn't have the new column added to it. That's the part I need to figure out a way to do.

Somehow I need to get my VB.Net program to issue a "SQL ALTER table ADD newcolumn" statement to add a column to the database file.

lcohan

"Add Column to Database"

You can add a Table to a Database
OR
You can add a Column to a Table

but not to "Add Column to Database"

deleyd

ASKER

Add column to table in database.

SOLUTION

ElrondCT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deleyd

ASKER

Thank you I was able to get it to work (with a slight modification switching to a SqlCeConnection)

        Dim dbconn As SqlCeConnection = Me.CFacilityTableAdapter.Connection
        dbconn.Open()
        Dim cmd As SqlCeCommand
        cmd = New SqlCeCommand("ALTER TABLE CFacility ADD COLUMN Sort Integer NOT NULL;", dbconn)
        cmd.ExecuteNonQuery()
        dbconn.Close()

Open in new window

Is it possible to add auto incrementing? I tried adding AUTO_INCREMENT, AUTOINCREMENT, AUTOINC, but it just complains it doesn't recognize that.

Also, is it possible to test to determine if the table already has a column named "Sort"?

ASKER CERTIFIED SOLUTION

ElrondCT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial