How to add column to .DBF file?

3
votes

One of the issues I have is that, I don't think the table has a name... its just a .dbf file

So I've been trying this: 

public void SQLAlter(string dbffile, string ColumnName )
{
   //dbffile is "C:\MAPS\WASHINGTON\TLG_ROADS_L.DBF"
   //ColumnName is "State"
   if (File.Exists(dbffile))
   {
        System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection();
        conn.ConnectionString = @"DSN=dBase Files";
        conn.Open();
        System.Data.Odbc.OdbcCommand comm = new System.Data.Odbc.OdbcCommand();
        comm.CommandText = "ALTER TABLE " + dbffile + " ADD COLUMN " + ColumnName + " VARCHAR(1024)";
        comm.ExecuteNonQuery();
    }
 }

The error is :

base {System.Data.Common.DbException} = {"ERROR [42S02] [Microsoft][ODBC dBASE Driver] Cannot find table or constraint."}

3
c#sqldbase
what is the value of the dbffile and does it exist? – Rob Allen
@Rob A I updated my question, short answer yes. – patrick
@marc_s - ODBC translates the command to function calls or vendor-specific SQL depending on the value of DSN in the connection string. – Anders Marzi Tornblad
@marc_s, you can actually use SQL on DBF files, there's this "little" Microsoft program called Visual Fox Pro that can do far more than it should ever have been able to. If anyone suggests you use it then scream and run a mile! Plus Python etc can interact with DBFs almost identical to SQL DBs so I don't see why C# shouldn't be able to - not that I know how. – Ben

3 Answers

2
votes

I believe the table name is supposed to be the filename, and the connection string should point to the folder containing the dbf file.

var path = Path.GetDirectoryName(dbffile);
var tableName = Path.GetFileName(dbffile);
// ...
conn.ConnectionSTring = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + path;
comm.CommandText = "ALTER TABLE " + tableName + //...

Check connectionstrings.com: http://connectionstrings.com/dbf-foxpro

1
votes

This is actually the correct syntax 

comm.CommandText = "ALTER TABLE " + dbffile + " ADD COLUMN " + ColumnName + " VARCHAR(1024)";

However if your filename is longer than 8 characters it will not find it. Even though I tried it will an appropriate length file name, the "Operation [is] not supported on a table that contains data."

Various Internet links seem to indicate that one has to create a new table, and copy all the fields over.

0
votes

Try another Provider.

It worked for me with the Visual Foxpro Provider conn.ConnectionString = @"Provider=VFPOLEDB.1; Data Source=Themes.dbf" + @"\;Extended Properties=dBase IV";

If the driver is not installed on your machine, you get it here : http://www.microsoft.com/en-us/download/details.aspx?id=14839