How can Add Column in Sql Database Table If it not Exists

Question

0.00/5 (No votes)

See more:

Sir
I am creating table in sql database at runtime, but i want to add more columns to that
table which i created at runtime. Below is the code for creating my table:-

VB

Private Sub Sql_Dept()
        Dim conStr As String = "Server=.;Database=PMS;Integrated Security = True;MultipleActiveResultSets=True;"
        Dim objCon As New SqlConnection(cnSettings2)
        Dim obj As SqlCommand
        Dim strSQL As String
        Dim myReader As SqlDataReader
        objCon.Open()
        obj = objCon.CreateCommand()
        Try
            strSQL = "SELECT * FROM sysobjects WHERE name='Dept'"
            obj.CommandText = strSQL
            myReader = obj.ExecuteReader()
            If Not myReader.Read() Then
                myReader.Close()
                strSQL = "CREATE TABLE Dept (Dept_No INT PRIMARY KEY, Dept_Name varchar(20), Flag varchar(2))"
                obj.CommandText = strSQL
                obj.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        objCon.Close()
        objCon = Nothing
    End Sub

This table was created successfully in database, how can add more columns to this table if columns are not exists here at runtime. I want to add three columns name like Flg2,Flg3 and Flg4 etc.
Can some one tell were i need to change in above code for this addition of columns.Please help me with codes.

Posted 7-Sep-15 18:20pm

hspl

Updated 7-Sep-15 18:29pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 8-Sep-15 0:30am

Creating and altering schema items at run time is generally not a good idea; I suggest you find a better technique to achieve your goals.

hspl 8-Sep-15 3:02am

Can you please help me with your codes.....i want to add more columns in existing table
i try with below code but it not works.....

Private Sub AddCol_ToDept_Tbl()
Dim conStr As String = "Server=.;Database=PMS;Integrated Security = True;MultipleActiveResultSets=True;"
Dim objCon As New SqlConnection(cnSettings2)
Dim obj As SqlCommand
Dim strSQL As String
Dim myReader As SqlDataReader
objCon.Open()
obj = objCon.CreateCommand()
Try
strSQL = "SELECT * FROM sys.columns WHERE object_id= OBJECT_ID(N'[dbo].[Dept]') AND name='Flag2'"
'strSQL = "SELECT * FROM sys.tables WHERE name='Dept'"
' strSQL = "SELECT * FROM master.dbo.systables WHERE name='Dept'"
obj.CommandText = strSQL
myReader = obj.ExecuteReader()
If Not myReader.Read() Then
myReader.Close()
strSQL = "ALTER TABLE Dept ADD Flag2 varchar(2) not null"
' Execute
obj.CommandText = strSQL
obj.ExecuteNonQuery()
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
objCon.Close()
objCon = Nothing
End Sub

Peter Leow 8-Sep-15 7:16am

You have started off on the wrong foot, read solution 1.

Member 15985998 30-May-23 13:14pm

thank u

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2015-09-07T18:47:00

No, do not add columns, instead create another table that contains the dept_no and its respective fig's in rows, e.g.
Table name: dept_figure
columns: dept_no (foreign key to dept table), figure
both dept_no and figure columns make up the composite primary key
The rows in this table look like this:

dept_no       figure
  1             fig1
  1             fig2
  2             fig3
  ...           ...

The relationship between the dept table and the dept_figure table is one to many.
+++++[Added]+++++
http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html[^]