Search code examples
odbcoledbfclipper

Insert a Date field into a Clipper Database using OLE

We're trying to insert data into a clipper database file (DBF file with NTX index file).

For a variety of reasons, we cannot change the database format.

We are currently seeing two issues:

  1. With our existing code, we are not able to update or utilize the NTX index file (I believe). We would like to be able to do this. Do you know of an OLE or ODBC driver that can do this?

  2. We are able to insert a row into the clipper database file (DBF) as long as we do not include a date. If we include a date, in any format, we get an error.

Some example code:

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=dBASE IV");
string sql = "insert into TABLE (E, J, DATE, STARTTIME, ENDTIME) values ('1', '2', '2010-01-13' ,'08:12:12', '18:12:12')";

  OleDbCommand myCommand = new OleDbCommand(sql);
  myCommand.Connection = con;
  con.Open();
  myCommand.ExecuteNonQuery();
  myCommand.Connection.Close();

and the exception is something like:

01/15/2010 12:50:31 {ERROR} ASITranslator.GUI.ASITranslatorGUI.insertSCH - Error in: Syntax error in INSERT INTO statement.-- StackTrace:    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()

Again, without the DATE column, it works fine.

Is there a better provider to use for Clipper files (that provider works great for other DBF files).

Any ideas?

Solution

  • Seems the issue is primarily related to the OLE DBF / dbase driver is unable to write to the Clipper native format, which is a modified version of dbase III.

    To write to the Clipper format, this string needs to be used:

    Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties="CollatingSequence=ASCII;DBQ=C:\DATA\8110FULL;DefaultDir=C:\DATA\8110FULL;Deleted=1;Driver={Microsoft dBase Driver (*.dbf)};DriverId=21;FIL=dBase III;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\test.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;";Initial Catalog=C:\DATA\8110FULL

    This will allow one to write to the file, including the DATE format.

    However, this does NOT use the NTX index files (nor does it update them). For that, it would appear that we would need to use the CodeBase (or similar) Clipper driver.