none
ALTER TABLE ADD COLUMN to a dbf file is not allowed RRS feed

  • Question

  • Hi All,

     

    When I ried to Add Column to a DBF file using VB.NET, I got the error messageSurpriseperation not supported on a table that contains data.

    Same thing happened when I tried to "DROP COLUMN" for DBF file.

     

    Can anyone help me out in this issue?

     

    Thank you!

     

    Cindy

    Monday, April 28, 2008 5:15 PM

Answers

  • Most likely the operation is not supported.  The driver support for dbf files is limited.  What you will need to do is create a new empty table (create table ...), create all the columns plus the new one, then copy the data over into the new table.  This is what is going to have to happen anyway with DBF format data file since the record size for a DBF file is fixed.

     

    Another alternative is to do a SELECT INTO like so (assuming you want your new field to be a blank string):

     

    select *, ''  as MyNewField into newTable from oldTable

     

     

     

     

    Monday, April 28, 2008 5:32 PM

All replies

  • Most likely the operation is not supported.  The driver support for dbf files is limited.  What you will need to do is create a new empty table (create table ...), create all the columns plus the new one, then copy the data over into the new table.  This is what is going to have to happen anyway with DBF format data file since the record size for a DBF file is fixed.

     

    Another alternative is to do a SELECT INTO like so (assuming you want your new field to be a blank string):

     

    select *, ''  as MyNewField into newTable from oldTable

     

     

     

     

    Monday, April 28, 2008 5:32 PM
  • Thanks, Matt.

     

    Can I create the table using a select statement. I have too much fields in my DBF that I don't want to print out them all. Is there a way that I can loop through my fields and create the new table based on the fields in the old table?

    Futher more, can I get ride of few fields (I know the fields I want to drop) so that my new table won't have those fields?

     

    Cindy

    Monday, April 28, 2008 6:20 PM
  • Matt,

     

    I was able to build the select statement string to get ride of some fields and add my new fields. That works for me right now.

     

    Thanks!

     

    Cindy

    Monday, April 28, 2008 7:16 PM