Check if column exists in DataTable

Question

In order to check if a column exists in a table in my Microsoft Access database, I wrote this code using VB.net:

       Dim conCreate As New OleDb.OleDbConnection(strCnn)
       conCreate.Open()
       Dim dbSchemaData As DataTable = conCreate.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, "Data", "TABLE"})
       conCreate.Close()

       Dim row As DataRow
       row = dbSchemaData.NewRow()

                For arrItem As Integer = 0 To arrLine.Length - 1

                    If arrLine(arrItem) = "Image Filename with Full Path" And row.Table.Columns.Contains("Tif") = False Then

                        Dim cmdCol As New OleDb.OleDbCommand("ALTER TABLE [Data] ADD COLUMN [Tif] VARCHAR(255)", conCreate)
                        conCreate.Open()
                        cmdCol.ExecuteNonQuery()
                        conCreate.Close()

                    ElseIf arrLine(arrItem) = "Image side" And row.Table.Columns.Contains("Voorkant") = False Then

                        Dim cmdCol As New OleDb.OleDbCommand("ALTER TABLE [Data] ADD COLUMN [Voorkant] BIT", conCreate)
                        conCreate.Open()
                        cmdCol.ExecuteNonQuery()
                        conCreate.Close()

                    ElseIf arrLine(arrItem) = "Image size (bytes)" And row.Table.Columns.Contains("Bestandsgrootte") = False Then

                        Dim cmdCol As New OleDb.OleDbCommand("ALTER TABLE [Data] ADD COLUMN [Bestandsgrootte] NUMERIC", conCreate)
                        conCreate.Open()
                        cmdCol.ExecuteNonQuery()
                        conCreate.Close()

                    ElseIf row.Table.Columns.Contains(arrLine(arrItem)) = False Then

                        Dim cmdCol As New OleDb.OleDbCommand("ALTER TABLE [Data] ADD COLUMN [" & arrLine(arrItem) & "] VARCHAR(255)", conCreate)
                        conCreate.Open()
                        cmdCol.ExecuteNonQuery()
                        conCreate.Close()

                    End If

                Next    

But running this while for example the first column (named Tif) already exists, it still tries to add it and proceeds to give the exception 'System.Data.OleDb.OleDbException' in System.Data.dll ("Field 'Tif' already exists in table 'Data'."), stopping the loop.

Any help would be appreciated!

Answer 1

I eventually solved the problem myself. All I needed was, apparantly, to use a DataRow to fill an array and see if the current item was contained in there.

Here's the code for anyone who's interested:

 conCreate.Open()
 Dim schemaColumn As DataTable = conCreate.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, "Data"})
 conCreate.Close()               

 For arrItem As Integer = 0 To arrLine.Length - 1

    Dim colCheck As Boolean
    Dim rowArr As New ArrayList()

    For Each row As DataRow In schemaColumn.Rows

        rowArr.Add(row("COLUMN_NAME"))

    Next

    If rowArr.Contains(arrLine(arrItem)) Then

        colCheck = False

    Else

        colCheck = True

    End If

    If colCheck = True Then

        Dim cmdCol As New OleDb.OleDbCommand("ALTER TABLE [Data] ADD COLUMN [" & arrLine(arrItem) & "] VARCHAR(255)", conCreate)
        conCreate.Open()
        cmdCol.ExecuteNonQuery()
        conCreate.Close()

    End If

Next