C # how to write to .dbf (foxpro) correctly

Question

I am trying to insert new data into an old .dbf database created using foxpro. There are many columns in the database, and I do not need to fill each one.

The connection itself works. But now I get the exception "The XY field does not allow null values" for each individual I do not add to my insert statement. But the database is configured to allow null values.

I am using the following code:

OleDbConnection dbfcon = new OleDbConnection("Provider=VFPOLEDB.1;" + "Data Source=" + Application.StartupPath + "\\Daten;"); dbfcon.Open(); String query = "INSERT INTO TB_KUVG (KDNR, Kuvg_id) " + "VALUES(?,?)"; OleDbCommand cmd = new OleDbCommand(query, dbfcon); cmd.Parameters.AddWithValue("@KDNR", 1); cmd.Parameters.AddWithValue("@Kuvg_id", 1); cmd.ExecuteNonQuery(); dbfcon.Close(); 

So what am I doing wrong? Is it better to use a different way to write to .dbf from C #?

Answer 1

You do almost everything right. Please note that parameter names are not important and will be positioned (i.e.: @KDNR is added first so that it matches the first? Placeholder). What are you missing if the fields that you do not pass do not accept NULL values, then you should notify the connection about it, instead you want "empty" values ​​for these fields ("for string, // for date, 0 for numeric and vice versa.) To notify the driver, you execute "SET NULL OFF" on the same connection.

When adding, I revised your existing code a bit:

 string dataFolder = Path.Combine(Application.StartupPath, "Daten"); String query = @"INSERT INTO TB_KUVG (KDNR, Kuvg_id) VALUES (?,?)"; using (OleDbConnection dbfcon = new OleDbConnection("Provider=VFPOLEDB;Data Source=" + dataFolder)) { OleDbCommand cmd = new OleDbCommand(query, dbfcon); cmd.Parameters.AddWithValue("@KDNR", 1); cmd.Parameters.AddWithValue("@Kuvg_id", 1); dbfcon.Open(); new OleDbCommand("set null off",dbfcon).ExecuteNonQuery(); cmd.ExecuteNonQuery(); dbfcon.Close(); } 

PS: Application.StartupPath may not be a good idea, as it may be in the "Program Files" section, which is read-only.

PS2: It would be better if you added the tag "VFP" instead of "DBF".