update tabledefinition using dataadapter and datacolumn

3 May 2006 8:17 AM

Binder

Hi everybody!

I'm trying use the datacolumn class to create new columns in a datatable
object and submit the new created column to the database (Access 2000).

Here's the code:
    Sub Main()
        Dim oconn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;
Data Source=C:\test.mdb")
        Dim adapter As New OleDbDataAdapter("SELECT * FROM Cities", oconn)

        Dim mydt As New DataTable
        Dim nCol As New DataColumn

        adapter.Fill(mydt)

        With nCol
            .Caption = "Newcolumn"
            .DataType = OleDbType.Boolean.GetType
        End With

        'Add column to Datatable
        mydt.Columns.Add(nCol)

        'Write changes to database
        adapter.Update(mydt)
        ReadLine()
    End Sub

I know I could use "ALTER TABLE ALTER COLUMN" commands as well, but is there
any way to create/update columns to a table in a database using ADO.NET
(without using DAO or ADOX)

Thanks for your efforts!

wkr binder

5 May 2006 6:54 PM

Mary Chipman [MSFT]

The DataAdapter will only write changes to the data to the database,
it won't create new columns in a table. You'd need to execute SQL DDL
via a command object or use DAO/ADOX in a separate operation.

--Mary

On Wed, 3 May 2006 10:17:25 +0200, "Binder"
<savingprivater***@uboot.com> wrote:

Show quote

>Hi everybody!
>
>I'm trying use the datacolumn class to create new columns in a datatable
>object and submit the new created column to the database (Access 2000).
>
>Here's the code:
>    Sub Main()
>        Dim oconn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;
>Data Source=C:\test.mdb")
>        Dim adapter As New OleDbDataAdapter("SELECT * FROM Cities", oconn)
>
>        Dim mydt As New DataTable
>        Dim nCol As New DataColumn
>
>        adapter.Fill(mydt)
>
>        With nCol
>            .Caption = "Newcolumn"
>            .DataType = OleDbType.Boolean.GetType
>        End With
>
>        'Add column to Datatable
>        mydt.Columns.Add(nCol)
>
>        'Write changes to database
>        adapter.Update(mydt)
>        ReadLine()
>    End Sub
>
>I know I could use "ALTER TABLE ALTER COLUMN" commands as well, but is there
>any way to create/update columns to a table in a database using ADO.NET
>(without using DAO or ADOX)
>
>Thanks for your efforts!
>
>wkr binder
>