Click here to Skip to main content
14,327,477 members
Rate this:
Please Sign up or sign in to vote.
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:-

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
Updated 7-Sep-15 18:29pm
v2
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.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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[^]
   
v3

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100