[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1131
  • Last Modified:

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.
0
deleyd
Asked:
deleyd
  • 2
  • 2
2 Solutions
 
lcohanDatabase AnalystCommented:
"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"
0
 
deleydAuthor Commented:
Add column to table in database.
0
 
ElrondCTCommented:
I use an OleDbCommand:

    	Friend WithEvents odbconn As System.Data.OleDb.OleDbConnection
	odbconn = New System.Data.OleDb.OleDbConnection
        odbconn.ConnectionString = "Provider=SQLOLEDB;Data Source=MySQLServer;User ID=MyID;Persist Security Info=True;Password=MyPassword;Initial Catalog=MyDatabase"
        odbconn.Open()
        Dim cmd As OleDbCommand
        cmd = New OleDbCommand("ALTER TABLE Table1 ADD NewField Varchar(50);", odbconn)
        cmd.ExecuteNonQuery()
        odbconn.Close()

Open in new window

0
 
deleydAuthor Commented:
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"?
0
 
ElrondCTCommented:
cmd = New SqlCeCommand("ALTER TABLE CFacility ADD COLUMN Sort Integer IDENTITY(1,1) NOT NULL;", dbconn)

The first 1 for Identity indicates that the numbering starts with 1; the second 1 indicates that you increment by one. Hint for finding things like this: use SQL Server Management Studio to create a script that will recreate the table (if you already have a table designed).

You've got two choices to find if a column already exists: get the schema and look through it, or just try to add a column inside TRY ... CATCH and trap the exception that happens if you try to add a duplicate column.

To get the schema for the table (using OleDb; you'll have to adjust for SqlCeConnection):

Dim schemaTable As DataTable = _
                          odbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                          New Object() {Nothing, Nothing, "MyTable", Nothing})

schemaTable.Rows(x)(3) is the column that has the column name, so you can loop through the rows to see if you get a match.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now