easiest way to add a SQL column through VB

Question

What i want to do is first check if a certain column already exists in a table and if not add it. I want to implement this through visual basic. If somebody took a little time to comment and briefly explain each step i would greatly appreciate it.

Answer 1

There are two ways to determine if a column exists: either try to use it and catch the error if it doesn't exist, or read the metadata from the database see SQL Server: Extract Table Meta-Data (description, fields and their data types)

Once you know that you need to add the column you use the ALTER TABLE command to add the column to the table.

Answer 2

Here is vb.net script to check if column exist, if not, create it..

''' summary ''' Checks to see if a table exists in Database or not. ''' ''' Table name to check ''' Connection String to connect to ''' Works with Access or SQL '''

Public Function DoesTableExist(ByVal tblName As String, ByVal cnnStr As String) As Boolean
    ' For Access Connection String,
    ' use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    ' accessFilePathAndName

' Open connection to the database
Dim dbConn As New OleDbConnection(cnnStr)
dbConn.Open()

' Specify restriction to get table definition schema
' For reference on GetSchema see:
' http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx

Dim restrictions(3) As String
restrictions(2) = tblName
Dim dbTbl As DataTable = dbConn.GetSchema("Tables", restrictions)

If dbTbl.Rows.Count = 0 Then
    'Table does not exist
    DoesTableExist = False
Else
    'Table exists
    DoesTableExist = True
End If

dbTbl.Dispose()
dbConn.Close()
dbConn.Dispose()

End Function

''' ''' Checks to see if a field exists in table or not. ''' ''' Table name to check in ''' Field name to check ''' Connection String to connect to ''' '''

Public Function DoesFieldExist(ByVal tblName As String, _ ByVal fldName As String, _ ByVal cnnStr As String) As Boolean ' For Access Connection String, ' use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ' accessFilePathAndName

' Open connection to the database
Dim dbConn As New OleDbConnection(cnnStr)
dbConn.Open()
Dim dbTbl As New DataTable

' Get the table definition loaded in a table adapter
Dim strSql As String = "Select TOP 1 * from " & tblName
Dim dbAdapater As New OleDbDataAdapter(strSql, dbConn)
dbAdapater.Fill(dbTbl)

' Get the index of the field name
Dim i As Integer = dbTbl.Columns.IndexOf(fldName)

If i = -1 Then
    'Field is missing
    DoesFieldExist = False
Else
    'Field is there
    DoesFieldExist = True
End If

dbTbl.Dispose()
dbConn.Close()
dbConn.Dispose()

End Function

Answer 3

    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