C# insert into DBF table

Question

I'm using VS2019, creating a Windows Forms app. Normally I only do updates on DBF files, but this time, I need to INSERT some data.

I already looked at the examples here on SO, MSDN and other sites.

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

Here's my snippet:

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 command without any issue: SELECT * FROM transport INSERT INTO transport (transid) values('0123456789').

I have a try-catch around it, don't worry.

My error message:

Der Befehl enthielt mindestens einen Fehler.
The command contained at least one error

The stack trace of the exception:

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}

Answer 1

Ok, it was solved.

It was a very dumb mistake. I had a .(dot) in my directory.

This broke the INSERT and UPDATE query somehow. But not the SELECT query. Link to tek-tips, who also helped me and a minimum working example from GriffMG there: (https://www.tek-tips.com/viewthread.cfm?qid=1808490)

Below the minimum working example. Also additionally a field was not nullable, which was found out in that process. Sorry for wasting the time of you all.

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();
                }

        }
    }
}