How to create a .dbf file using c#?

Question

I am trying this to make a select and it works just fine

string str = 
    "SELECT * FROM FREE RIGHT JOIN TestTest ON FREE.DOCNO = TestTest.DOCNO";
DataTable dt = new DataTable();
OdbcDataAdapter da = new OdbcDataAdapter(str, odbconn);
da.Fill(dt);

I am trying this to create a .dbf and i get this OdbcException :

string str0 = "Create Table Persons (Name char(50), City char(50), Phone char(20), Zip decimal(5))";
OdbcCommand cmd = new OdbcCommand(str0, odbconn);
cmd.ExecuteNonQuery();

ERROR [42000] [Microsoft][ODBC dBase Driver] Syntax error in field definition.

Rob · Accepted Answer · 2010-09-04 09:51:29Z

3

Your problem is caused by Zip decimal(5), as the ODBC dBase driver doesn't like it. Off the top of my head, and after a quick google, I couldn't come up with a syntax it would tolerate. It does accept it quite merrily if you use the OleDb provider instead, as follows:

using (var dBaseConnection = new OleDbConnection(
    @"Provider=Microsoft.Jet.OLEDB.4.0; " +
    @" Data Source=C:\Users\RobertWray\Documents\dBase; " +
    @"Extended Properties=dBase IV"))
{
    dBaseConnection.Open();

    string createTableSyntax = 
        "Create Table Person " +
        "(Name char(50), City char(50), Phone char(20), Zip decimal(5))";
    var cmd = new OleDbCommand(createTableSyntax, dBaseConnection);
    cmd.ExecuteNonQuery();
}

One question though: Are you sure that you want to create the Zip column as a decimal? Not being a US resident I'm not 100% confident on this information, but, according to Wikipedia ZIP codes can start with a 0. Storing them as a numeric datatype won't allow you to accurately represent that.

My code for creating the table via ODBC:

using (var dBaseConnection = new OdbcConnection(@"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\Users\RobertWray\Documents\dBase;"))
{
    dBaseConnection.Open();

    string str0 = "Create Table Person2 (Name char(50), City char(50), Phone char(20))";
    var cmd = new OdbcCommand(str0, dBaseConnection);
    cmd.ExecuteNonQuery();
}

edited Sep 4, 2010 at 9:51

answered Sep 4, 2010 at 9:18

Rob

45.9k24 gold badges126 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jane Over a year ago

i did that zip decimal(5) just for testing. i still got an issue with that OleDbConnection, the way it is working for me is string pathFiles = Path.Combine(Application.StartupPath, "DB"); string strconn = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;" + "Dbq="+pathFiles+";"; OdbcConnection odbconn = new OdbcConnection(strconn);

Rob Over a year ago

@jane, sorry, I don't understand what you mean by your comment. Your table creation SQL works a treat for me, both via OleDb and Odbc. It works in its entirety via OleDb and with the Zip decimal(5) removed for Odbc. It might be a good idea to increase the size of the code sample you show in your question! =)

Rob Over a year ago

@jane, there's something else wrong/different then. I've copied the code I used to create the table via ODBC into my answer - can you try that and see if it works for you (obviously change the path! =), and then perhaps tweak it into your code?

wattostudios · Accepted Answer · 2012-11-12 01:15:00Z

0

Use vfpoledb provider and field type NUMERIC instead of DECIMAL.

string ConnStr1 = @"Provider =vfpoledb; Data Source = C:\MyDataSource; Collating Sequence =machine;

edited Nov 12, 2012 at 1:15

wattostudios

8,78613 gold badges45 silver badges58 bronze badges

answered Sep 14, 2011 at 10:31

Tural

211 bronze badge

Collectives™ on Stack Overflow

How to create a .dbf file using c#?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related