Accessing DBF files - INSERT INTO question 1

[AnotherNovice1]

Hello there,

I'm developing an App, to replace some of our FoxPro based systems, due to changing requirements and spaghetti code from my predecessor.
I didn't see an DBF related Forum in here, so it's my best guess to post it here.

What I want to do:
Inserting records into a DBF table. (plain simple, huh?)

What I already tried:
Selecting and Updating records works fine in C#, with an OleDb adapter.
I did a post on SO too, in the hope there would be enough people on it who know VFP.

https://stackoverflow.com/questions/66103752/c-sharp-insert-into-dbf-table

The DBF File 'transport' contains some fields, including transid, a char field with a length of 10.
My code:

using System.Data.OleDb;

private static readonly string CONNECTION = @"Provider=VFPOLEDB;Data Source=C:\<path_to_existing_dbf-file>";
private void Test()
{
    using (OleDbConnection conn = new OleDbConnection(CONNECTION))
        {
            conn.Open();
            using (OleDbCommand cmd = new OleDbCommand("INSERT INTO transport (transid) VALUES (?)", conn))
            {
                cmd.Parameters.AddWithValue("@transid", "0123456789");
                new OleDbCommand("set null off", conn).ExecuteNonQuery();
                cmd.ExecuteNonQuery();
            }
            conn.Close();
        }
}

Has anyone a clue where I have an error?
In Visual FoxPro9.0 I can execute the query in the command window without any issue:
SELECT * FROM transport (returns everything, no problem)
INSERT INTO transport (transid) values('0123456789') (inserts the value without any issue)

Any help would be much appreciated.

My error message:

Der Befehl enthielt mindestens einen Fehler(below my translation).

The command contained at least one error

The stack trace of the exception(with shortened paths, because it's not relevant):

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at ARKPromot.VFPInteraktion.ErstelleTransportauftrag() in C:<path>\VFPInteraktion.cs:line 252

string viewError = JsonConvert.SerializeObject(ex);
returns:

{"oledbErrors":[{"Message":"Der Befehl enthielt mindestens einen Fehler.","NativeError":0,"Source":"Microsoft OLE DB Provider for Visual FoxPro","SQLState":""}],"ClassName":"System.Data.OleDb.OleDbException","Message":"Der Befehl enthielt mindestens einen Fehler.","Data":null,"InnerException":null,"HelpURL":null,"StackTraceString":" bei System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)\r\n bei System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)\r\n bei System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n bei System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n bei System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n bei System.Data.OleDb.OleDbCommand.ExecuteNonQuery()\r\n bei .ErstelleTransportauftrag(ARKPlatz quelle, String lkeyQuelle, ARKPlatz ziel, String lkeyZiel, String text, Meldung& meldung) in C:\VFPInteraktion.cs:Zeile 254.","RemoteStackTraceString":null,"RemoteStackIndex":0,"ExceptionMethod":"8\nExecuteCommandTextErrorHandling\nSystem.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Data.OleDb.OleDbCommand\nVoid ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)","HResult":-2147217900,"Source":"Microsoft OLE DB Provider for Visual FoxPro","WatsonBuckets":null}

Edit: Link to the DBF file.

Spoiler

Link

 

[Chris Miller]

This table is okay without an FPT. I said, when the table has an fpt you have to keep it, if there is no type like memo, blob or general, which require an fpt, then there is no fpt. But that doesn't mean fpt is optional to copy, if such field types exist and you only copy the dbf, then the table can't be opened.

Rule of thumb #1 is: copy all files tablename.* to copy a table.

This can still be insufficient when the table is part of a dbc (then a couple of dbc related files is necessary). And there can be multiple cdx, additional idx, that obviously can't all have the tablename prefix, so tablename.* isn't necessarily catching all files belonging to a the table. You always have the dbf, but it's not sufficient to copy if there are more related files, ie the dbf header points out the necessary fpt and/or dbc and/or cdx.

Therefore, rule of thumb #2: Simply copy the database folder to get your test database, then you can't miss anything unless the dbc refers to files outside of the database folder, but that would be bad coding style.

Chriss

 

[AnotherNovice1]

OK somehow I cannot edit my posts...
It just shows "Post edited"...
In my post this morning,
11 Feb 21 06:48
is a working link.
Does anyone know if I have to give another parameter for allowing writing on that DBF file?
Maybe I have to explicitly state it.

 

[Chris Miller]

DBF access is only determined by file permissions.

But you don't have an error that points in that direction.


Chriss

 

[GriffMG]

I know we are well down the line with this, but I just downloaded the transport1 files and took the code supplied
and ran it with my modified path and it threw an error saying STAPELNR does not accept null values.

So I modified the structure to accept them and bingo I could use your code to add them

this is the code I used

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private static readonly string CONNECTION = @"Provider=VFPOLEDB;Data Source=d:\$incoming\finedata\";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (OleDbConnection conn = new OleDbConnection(CONNECTION))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand("INSERT INTO transport1 (transid) VALUES (?)", conn))
                    {
                        cmd.Parameters.AddWithValue("@transid", "0123456789");
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }

        }
    }
}

Which is the exact solution I suggested at comment No. 1 above.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[AnotherNovice1]

Ok...Seriously...
I dropped nearly all hope...
Long story short: your example didn't work on my machine.
BUT then I remembered that I have another path to my testing directoy than on my workstation.
And guess what:
Somehow the OleDbAdapter can connect to that file, but it cannot insert data, because the directory contains my name.
It's a normal german last name, but on my machine where I run my tests lately its <the first letter of the first name>.<last name>
It was the DOT that broke everything...
Days of many people wasted because of this...So much hair got lost to the grey color these days...


Thanks a lot for your time you two!
I can't thank you guys enough!

 

[GriffMG]

Glad you are happy, I just renamed my folder from finedata to finedata.com and it still works...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

I don't think a dot makes a difference. But nevermind, something got it working.

Just for sake of completeness and relating back to why I was splitting hairs though I said I don't want to: If you specify transport.dbf as Data Source, VFP will take it as a directory name. Directories are allowed to have dots in their names, too. And since VFP drivers only expect a directory or a DBC file, specifying any other file name (full path) will just be seen as no DBC, therefore it must be a directory. Not finding a directory with the name of a DBF because it actually is a DBF file then confuses the VFP driver.

Only specify the directory.

Chriss