?
Solved

Add Column to Database

Posted on 2014-04-01
5
Medium Priority
?
1,021 Views
Last Modified: 2014-04-05
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
Comment
Question by:deleyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39970042
"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
 

Author Comment

by:deleyd
ID: 39970876
Add column to table in database.
0
 
LVL 20

Assisted Solution

by:ElrondCT
ElrondCT earned 2000 total points
ID: 39972357
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
 

Author Comment

by:deleyd
ID: 39972688
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
 
LVL 20

Accepted Solution

by:
ElrondCT earned 2000 total points
ID: 39972891
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question