update tabledefinition using dataadapter and datacolumn

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

Mary
Fri May 05 13:54:57 CDT 2006

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"
<savingprivateryan@uboot.com> wrote:

>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
>