Trouble with Insert Into .dbf file

Question

This code does not save any data in the dbf file. What is wrong here? Here is the code with the recommended changes. Thank you.

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP;Extended Properties=dBase IV"; 
        using (OleDbConnection connection = new OleDbConnection(connectionString)) 
        using (OleDbCommand command = connection.CreateCommand()) 
        { 
            connection.Open(); 
            command.CommandText = @"CREATE TABLE TEST (Id Text, Changed Text, Tacos Text)"; 
            command.ExecuteNonQuery(); 
        }
        using (OleDbConnection connection2 = new OleDbConnection(connectionString))
        using (OleDbCommand command2 = connection2.CreateCommand())
        {
            connection2.Open();
            command2.CommandText = @"Insert into TEST (Id, Changed, Tacos) VALUES ('One','Two','Three')";

            try
            {
                command2.ExecuteNonQuery();
            }
            catch (Exception ee) 
            {
                MessageBox.Show(ee.ToString());
            }
        }

Igloo

Did you build this using Visual Studio? Are you running it in the debugger? Is your .dbf file in the VS solution file? What's its "Copy to Output Directory" property? What is the path to the .dbf? What is the path to the file you are opening that you think doesn't have any data saved? Put a breakpoint on your new OleDbConnection(...) and check if the path is really what you think it is. – Barrybarrymore

Answer 1

Not positive, but looking at your connection, you are connecting to the root of C:.... bad... It could just be a matter of permissions and not able to write at the root directory.'

I would suggest creating your connection to SOME sub folder, even if its just

C:\MyDBFTesting\

After that, see if the "CREATE TABLE" result actually worked and you can see the "test.dbf".

Finally, put your executeNonQuery() in a try/catch

try
{
   command2.ExecuteNonQuery()
}
catch( Exception e )
{
   string x = e.Message;
}

put a break point at the catch and step into... see what OTHER element / messages may be in the "e" exception object to provide more possible answers for you.

Seeing the context of ".dbt" (which indicates more free-form text rather than fixed / standard types), i would change your create to explicitly identify CHARACTER of fixed size capacity... something like

create table Test ( ID C(10), Changed C(10), Tacos C(10) )

C = Character and 10 is the max size that would be allowed in the column. Other data types, such as I = int, D = date only, T = date/time, L = logical, etc. These are "known" types which provide a strict structure. The TEXT is freeform and its content goes into the .dbt file, but there SHOULD be a corresponding record, but otherwise not appearing to show up.

Then, your insert command would still work identically.

Cheerful

The table (file) gets created. Also the column headers are there but no row. No exception is generated with the try catch. – Igloo

@NewsReader, then I would still run the executenonquery in a try catch and see if / what error may be reported. It COULD be as simple as exclude the .DBF reference to the insert command as that would be implied since you are connected to the path, it would assume the .DBF extension. -- one FINAL thought, "NAME" is probably a RESERVED word... change your create table to something OTHER than name... – Cheerful

Thank you for your reply. I changed the column 'Name' to 'Tacos' and the new column header name shows as Tacos. But still no row. Maybe the insert syntax is wrong? I updated the code. – Igloo

It looks like a .dbt file gets created along with the .dbf file. The row appears to be in this file and not in the .dbf file – Igloo

@NewsReader, see revised answer explaining column types and what you MAY be running into. Although I've personally not used DBASE drivers, but instead Microsoft Visual Foxpro OleDb provider, I've had great responsiveness with that... – Cheerful