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

 

[GriffMG]

Hi

The VFPOLEDB driver is pretty good, but has some foibles.

The first is that parameterised updates and inserts work in the order that you want to use them, so you do not have to name them, although for access via c# maybe
you do... secondly the dbf file knows whether it allows null fields and some people (me) define their tables with NOT NULL on each field and no default value.

So, new OleDbCommand("set null off", conn).ExecuteNonQuery(); probably won't change anything. I THINK that is where your error is coming from.

If it is, you can do one of two or three things, the first is to change the table structure to allow nulls (if you have VFP to edit it with) the second is to specify a value for
each field in the table, which can be lengthy...

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. ​

 

[GriffMG]

I am not sure that there is actually a command SET NULL OFF, is it possible that is the line that is generating the error?

Have a look at these links:

https://microsoft.public.data.ado.narkive.com/Wul9Qb47/how-to-allow-null-values-using-vfpoledb

https://stackoverflow.com/questions...erts-to-foxpro-tables-through-nhibernate-usin

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]

VFP's own parameterization works with variable names, not just a naked question mark.
And then the parameter needs to be either a VFP variable predefined or an expression.

The obvious easy way out is not using parameterization but of course, you want to be safe against SQL injection.
I'm not sure whether the OleDB provider understands the way C# handles parameters even before the whole query arrives on the VFP side.
I can later check this, but for now, I could only give you a shot in the dark by guessing 'INSERT INTO transport (transid) VALUES (?transid)' could work.

From the C# side I know the AddParameter requires a parameter name for internal distinguishing parameters but substitutes them to a query by order of appearing ?, so what you try is C# standard.

I'm also not sure whether you can execute a batch, then you could try to execute m.transid =? and then the query with ?m.transid, though it just shifts the problem of substituting the ? with the parameter value to an extra VFP internal variable definition, it might help VFPs SQL engine to work as it surely does, with a VFP variable as a parameter.



Chriss

 

[Chris Miller]

And another tip I can give for now is: VFP OleDB supports EXECSCRIPT. So that is a sure way of enabling a batch of command lines, by embedding them into a single execscript call, which makes VFPOleDB only execute the one-liner "Execscript" and the multiline batch code is then executed by the VFP runtime triggered by the OleDB provider.

Side note: Look into SETRESULTCURSOR(), when you want a table result.


Chriss

 

[AnotherNovice1]

Thanks for all the replies!

@GriffMG
I only write the parameters with @<value> out of habit from working with postgreSQL and
it improves the readability for me.
I only inserted it into my code, because it was kind of a last straw.

new OleDbCommand("set null off", conn).ExecuteNonQuery();

It didn't do harm, neither did it help :/
It didn't even change the error message I get. I guess I can delete it again.
Sadly I don't have any NOT NULL constraints in the table, I have a copy of Visual FoxPro 9.0 installed, so I can look into the DBF.
That was my first hope, that I only forgot that there were constraints in it.

From the C# side @Chris Miller:
With other methods for UPDATE functions it works smoothly, but even if I declare it without parameterization, like

"INSERT INTO transport (transid) VALUES ('0123456789')"

It shows the exactly same error.
I use the same construct of usings for all my DBF interactions.

Thanks for the suggestion, but I only write into the DBF once every 10min to 40min, so it would not bring more performance.

 

[GriffMG]

I use the VFPOLEDB in my online systems, and they all reliably insert using the syntax you have quoted.

That said, I specify every field and give it a value of some kind.
You might do well to try that with a hand crafted simple table of your own - perhaps make one with just the TRANSID field?
You might also check your specified '0123456789' does not already exist as a unique index?

INSERT INTO transport (transid,descrip,counter) VALUES ('0123456789',"THIS IS A DESCRIPTION",75)

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]

Now I could test with VS Code, and your code works, adapted to the sample Northwind database.

Looking back, you wrote:

private static readonly string CONNECTION = @"Provider=VFPOLEDB;Data Source=C:\<path_to_existing_dbf-file>";

Well, that's wrong. Data Source either is a DBC file (database) or a folder, not a single DBF. And by the way, in case you have a DBC, you can also use the folder when the DBFs are all in the same folder as the DBC.

using System;
using System.Data.OleDb;

namespace Test
{
    class Program
    {
        private static readonly string CONNECTION = @"Provider=VFPOLEDB.1;Data Source=C:\Program Files (x86)\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind\;Exclusive=false;Nulls=false";
        static void Main(string[] args)
        {
	    using (OleDbConnection conn = new OleDbConnection(CONNECTION))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(@"INSERT INTO customers (customerID) VALUES (?)", conn))
                {
                    cmd.Parameters.AddWithValue("@customer", "XYZ");
                    new OleDbCommand("set null off", conn).ExecuteNonQuery();
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
    }
}

Notice, the Northwind Database coming with the OleDB Provider is read-only within the Program Files system folder. You have to give write permissions or copy the data elsewhere to test.

Chriss

 

[Chris Miller]

Also see

https://weblog.west-wind.com/posts/2009/Mar/12/OleDb-Parameters-to-access-FoxPro-Data-from-NET.

And then one more thing to check is whether you build for x86 (32 bit) CPUs.


Chriss

 

[GriffMG]

I think the VFPOLEDB can access a free table as well as a database... so his C:\<path_to_existing_dbf-file> could be correct

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]

Griff, no, it has to be a path, not a dbf file. I don't want to split hairs, but I assume AnotherNovice1 meant something like "C:\data\transpport.dbf". Then it should just be "C:\data\".

Chriss

 

[AnotherNovice1]

Well I indeed use only the DBF files and have no DBC file.
I guess as far as I read it as a kind of index file of all the other DBFs.
@Chris Miller Funny enough your sample code works.
I guess something must be really broken on my tables...
Edit: I also set the x86 building in my project.

Edit 2: Well what also comes to my mind...
Normally there are more files for a table. Do I need all files for a table?
CDX - really needed?
DBF - of course for the data
FPT - really needed?
Also I seem I can only read, nothing like successfull UPDATES on a table...
I feel dumb, but I have to wait 12hrs until I can get the other files for further testing.
If that was the missing stuff then I'm truly sorry...

 

[GriffMG]

Chris, I wasn't saying it didn't have to be a path, the OP said 'path to existing dbf' not 'path to and name of existing dbf'
B-)

To the OP

.dbf - the data in a structured form with a header in it to define the structure
.cdx - the structural index file (opens automatically with the dbc)
.fpt - a memo file, must be there if memo type fields are defined or you can't access the .dbf reliably


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]

@AnotherNovice1

When the table has a CDX and FPT those are never optional. There can be tables without memo, blob, or general fields having no FPT file, there can be no compound index, there can be two or more idx and additional CDXes. However a table is structured, you need all files belonging to it.

Chriss

 

[AnotherNovice1]

So this could be the reason why I get these errors, right?

 

[GriffMG]

Quite possibly.
B-(

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 would expect other errors, then.

Another hint is the HRESULT value of -2147217900, that's 80040E14 in hex and points out a simple syntax error, maybe simply because the string is Unicode and not ANSI.


Chriss

 

[AnotherNovice1]

Well - I got some good and some bad news.
I got my CDX file, but our program in production runs without an FPT file.

I attached a copy of my dbf file.
It has no entries so far.
There is also no FPT file created.

@Chris Miller But how can I switch the encoding?
The OleDb adapter should handle it or am I wrong?

Edit: I appended it in my initial post, if someone in the future stumbles upon it.

Edit2: Ok - UPDATE doesn't work either.
I guess I can only read.

 

[GriffMG]

There is no link to a file, just a label saying there is...

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]

Well, from VFP to OleDB you can set conversion generally, by default there is no conversion.

C# String encoding:

https://docs.microsoft.com/en-us/dotnet/api/system.text.encoding?view=net-5.0

Chriss