There is a bug in VFPOLEDB handling in VFP that you cannot insert or update a datetime value using insert/update command directly. I have confirmed the bug in SP1 OLEDB driver (9.0.0.3504). I wanted a friend of mine who has SP2 driver to test it and the result is the same.

To reproduce:
-Create a test database and a test table that has at least one datetime field

md c:\BugTest
cd c:\BugTest
create database BugTest
Create table Test (f1 i, f2 t, f3 i autoinc)
close data all

-Try using Insert/update command to update that field via ADO

Local loCon As Adodb.Connection,;
  loCmd As Adodb.Command, ;
  loParm As Adodb.Parameter

loCon = Createobject('AdoDb.Connection')
loCmd = Createobject('Adodb.Command')

loCon.ConnectionString='Provider=VFPOLEDB;Data Source=c:\Bugtest\BugTest.dbc'
loCon.Open()

loCmd.ActiveConnection=loCon
loCmd.CommandText='insert into Test (f1, f2) values (SECONDS(),?)'

loParm = loCmd.CreateParameter('f2')
loParm.Type = 135  && adDBTimeStamp
loParm.Value = Datetime()
loCmd.Parameters.Append( loParm )

loCmd.Execute()

loCon.Close

Use test
Go Bottom
Browse
Use

I don't understand what I am missing, if I use a cursoradapter with RS or create a clientside RS and AddNew it works right. Or it works right if I do it from C#. ie:

using System;
using System.Data;
using System.Data.OleDb;

class test
{
  static void Main()
  {
   OleDbConnection cn = new OleDbConnection(@"Provider=VFPOLEDB;Data source=C:\BugTest\bugtest.dbc;");
   OleDbCommand cmdInsert = new OleDbCommand("insert into test (f1,f2) values (999,?)",cn);

    cmdInsert.Parameters.AddWithValue("p2",DateTime.Now);
    cn.Open();

    cmdInsert.ExecuteNonQuery();
    cmdInsert.Parameters["p2"].Value = new DateTime(2010,1,31,10,20,30);
    cmdInsert.ExecuteNonQuery();

    Console.WriteLine( "DbType: {0}\nOleDbType: {1}",
      (int)cmdInsert.Parameters["p2"].DbType,
      (int)cmdInsert.Parameters["p2"].OleDbType );
  }
}

Is there something that you can see that I am missing?
I don't want to create a .Net wrapper to insert/update data to a VFP table from within VFP:(
Thanks in advance

PS: Please don't ask why would I use ADO from within VFP for VFP tables, I have good reasons myself.

Cetin