Insert a Date field into a Clipper Database using OLE

Question

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?

Answer 1

First, it appears you are trying to add 'Text values' for the date columns regardless of them being in a date format. Additionally, if used in web-based applications where you would be using variables, you'd be best to use parameterized queries.

String sql = "insert into YourTable ( fld1, fld2, DateFld1, DateFld2 ) "
  + "value ( ?, ?, ?, ? )";

OleDbCommand myCommand = new OleDbCommand(sql); 

OleDbParameter NewParm = new OleDbParameter( "parmFld1", 1 );
NewParm.DbType = DbType.Int32;
myCommand.Parameters.Add( NewParm );

NewParm = new OleDbParameter( "parmFld2", 2 );
NewParm.DbType = DbType.Int32;
myCommand.Parameters.Add( NewParm );

NewParm = new OleDbParameter( "parmDate1", DateTime.Now );
NewParm.DbType = DbType.DateTime;
myCommand.Parameters.Add( NewParm );

NewParm = new OleDbParameter( "parmDate2", DateTime.Now );
NewParm.DbType = DbType.DateTime;
myCommand.Parameters.Add( NewParm );

Then continue with your connection, open, execute and close...

Answer 2

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.

Answer 3

DSN-less connection : (Note the driver and FIL string changed to what called in Windows 7) It use Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). The code use the .Net Framework Data Provider for ODBC (System.Data.Odbc), Not the .Net Framework Data Provider for OLEDB (System.Data.OleDb). Clipper type N-> OdbcType Double

 "Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties=\"CollatingSequence=ASCII;DBQ=F:\\Folder;DefaultDir=F:\\Folder;Deleted=1;DRIVER=Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx);DriverId=21;FIL=dBASE III;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;\";Initial Catalog=F:\\Folder";