Re: adding column at runtime

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 18:08:07 -0400

You can add the column but it's not going ot add it to the excel ***.
Remember that the source and the datatable are totally unrelated. The
DataAdapter moves the data around, but assume that you added a column that
didn't exist in the original query. It would take more than a
Insert/Update/Delete command for another adapter to make it work..it'd take
a DDL command. Moreoever, the first adapter would have to have its logic
changed even if they could make this creation happen automagically somehow
b/c it didn't originally know about any column like the one you added.
Furthermore, a DataAdapter doesn't give a hoot about who it works for, it's
decoupled from your dataset... so it doesn't know or care about that new
column as long as the Commands it given will work.

Think about how much trouble this could cause if it did work. You DBA grants
you write and read permissions but not DDL permissions. In order to allow
this he'd have to grant some other high level permissions that you certainly
don't want to just dole out. You could easily drop a table or god knows
what. I think you'll need to use interop and add this column. 

-- 
W.G. Ryan MVP Windows - Embedded
http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
"SM" <anonymous@discussions.microsoft.com> wrote in message
news:18D32138-098D-4205-9EFC-E9B62F5D9A72@microsoft.com...
> Hi All,
>
> I am reading data from excel file and storing it into existing table in
sql server. If there are new columns in the excel file I want to add new
columns in the table dynamically. I am doing following
> . reading data of excel file into excelDataSet.table1
> . loading Schema of the table into sqlDataset.table2
> . finding out any new columns in the excel file and then adding columns in
table2 of sqlDataSet as follow
> .
>
> For Each excelColumn In excelDataSet.Tables(table1).Columns
>    Dim allStockColumns As DataColumnCollection =
sqlDataSet.Tables(table2).Columns
>    Dim findThisColumn(0) As Object
>    findThisColumn(0) = Trim(excelColumn.ColumnName)
>
> Try
>     If Not (allStockColumns Is Nothing) Then
>         If Not (allStockColumns.Contains(findThisColumn(0))) Then
>            'column is missing so add it
>             Dim myColumn = New DataColumn(Trim(excelColumn.ColumnName))
>             myColumn.DataType = allStockColumns(1).DataType()
>             myColumn.AllowDBNull = True
> sqlDataSet.Tables(table2).Columns.Add(myColumn)
>          End If
>     End If
>
> Catch ex As Exception
>     logWriter.WriteLine(ex.Message)
> End Try
>
> This code segment adds new column in the table2 of the sqlDataSet but it
doesn't update table in the database. How can I update schema of the table
in the database?
>
> Thanks for help.
>
>
Quantcast