如何在Sql数据库表中添加列如果不存在 [英] How can Add Column in Sql Database Table If it not Exists

问题描述

Sir

我在运行时在sql数据库中创建表，但我想在运行时创建的那个

表中添加更多列。以下是创建我的表格的代码： -

 私人 < span class =code-keyword> Sub  Sql_Dept（）
  Dim  conStr  As  字符串 =   Server = .; Database = PMS ; Integrated Security = True; MultipleActiveResultSets = True; 
  Dim  objCon  As  新 SqlConnection（cnSettings2）
  Dim  obj  As  SqlCommand 
  Dim  strSQL  As  字符串 
  Dim  myReader  As  SqlDataReader 
 objCon.Open（） 
 obj = objCon.CreateCommand（）
 尝试 
 strSQL =   SELECT * FROM sysobjects WHERE name ='Dept' 
 obj.CommandText = strSQL 
 myReader = obj.ExecuteReader（）
 如果 不 myReader.Read（）那么 
 myReader.Close（）
 strSQL =   CREATE TABLE Dept（Dept_No INT PRIMARY KEY，Dept_Name varchar（20） ，Flag varchar（2）） 
 obj.CommandText = strSQL 
 obj.ExecuteNonQuery（）
 结束 < span class =code-keyword>如果 
  Catch  ex  As 异常
 MessageBox.Show（例如。消息）
 结束 尝试 
 objCon.Close（）
 objCon =  Nothing  
 结束  Sub  

此表是在数据库中成功创建的，如果在运行时此处不存在列，如何向此表添加更多列。我想添加三列名称，如Flg2，Flg3和Flg4等。

可以说一下我需要在上面的代码中更改这些列。请帮我编写代码。

解决方案

不，不要添加列，而是创建另一个包含dept_no及其各自图形的表，例如

表名：dept_figure

列：dept_no（dept表的外键），图

dept_no和figure列组成复合主键

此行中的行表格如下：

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

dept表和dept_figure表之间的关系是一对多。

+++++ [已添加] +++++

http://www.datanamic.com/support/ LT-dez005-介绍 - db-modeling.html [ ^ ]

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.

解决方案

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

这篇关于如何在Sql数据库表中添加列如果不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！