且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

通过VB添加SQL列的最简单方法

更新时间:2022-05-24 09:07:21

    Dim connString As String = "Data Source=NameOfMachine\InstanceofSQLServer;Initial Catalog=NameOfDataBase;Integrated Security=True"
    Dim MyCol As String = "NameOfColumn"
    Dim MyTable As String = "[NameOfTable]" ' or "[Name Of Table]" use brackets if table name contains spaces or other illegal Characters
    Dim MySql As String = "IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS" & vbCrLf &
"WHERE TABLE_NAME = '" & MyTable & "' AND COLUMN_NAME = '" & MyCol & "')" & vbCrLf &
"BEGIN" & vbCrLf &
"ALTER TABLE [dbo]." & MyTable & " ADD" & vbCrLf & "[" & MyCol & "] INT NULL ;" & vbCrLf & "END"

    Try
        ' MsgBox(MySql)- this msg box shows the Query so I can check for errors- Not required for code.
        Dim dbConn = New SqlConnection(connString)' Note ConnString must be declared in the form class or within this Sub. Connstring is your connection string
        Dim dbCmd = New SqlCommand(MySql, dbConn)
        dbConn.Open()
        dbCmd.ExecuteNonQuery()
        'MessageBox.Show("Ready To Load Addendums")

        dbConn.Close()

    Catch ex As Exception
        MsgBox("We've encountered an error;" & vbCrLf & ex.Message)

    End Try