Delphi update DateTime column in SQL Server 2008 R2

Question

I am trying to post a date to a dateTime column in a SQL Server 2008 R2 database, but I have run into many problems that I don't know what are the causes.

First, I used this code but I got the error:cannot convert string to date.

ADOOF.SQL.Text:='UPDATE OFTab SET CA='''+ CA.Text + ''', demandeClient=''' + DateTimeToStr(demandeClient.DateTime) + ''' WHERE ID='''+ ADOOF.FieldByName('ID') + '''';
ADOOF.ExecSQL;

Second, I have used parameters:

ADOOF.SQL.Text:='UPDATE OFTab SET CA='''+ CA.Text + ''', demandeClient=:demande_client WHERE ID='''+ ADOOF.FieldByName('ID') + '''';
ADOOF.Parameters.ParamByName('demande_client').Value:= demandeClient.Date;
ADOOF.ExecSQL;

But I got the error: Parameter (demande_client) not found.

I googled this problem and I found a suggestion by Embarcadero saying that the parametres sould be created before calling the ADOQuery like this:

ADOOF.SQL.Text:='UPDATE OFTab SET CA='''+ CA.Text + ''', demandeClient=:demande_client WHERE ID='''+ ADOOF.FieldByName('ID') + '''';
ADOOF.Parameters.ParseSQL(ADOOF.SQL.Text, True);    
ADOOF.Parameters.ParamByName('demande_client').Value:= demandeClient.Date;
ADOOF.ExecSQL;

Finely I removed the connection Persist Security Info but always the same problem. Please, any suggestions.

INFO: I am using MICROSOFT OLE DB Provider For SQL Server.

Answer 1

in your first example use

FormatDateTime('YYYYMMDD hhmmss',demandeClient.DateTime)

instead of

DateTimeToStr(demandeClient.DateTime)

This is because DateTimeToStr without formatsettings uses your localized machine settings and your database just might or might not like the format. Using FormatDateTime also gets rid of ambiguities: consider 01/02/03, for some people on the world this is january 2nd 2003 but for others 1st of februari 2003 and even some will say 2001, februari 3rd. YYYYMMDD is universal. 20030201 is always 1st of februari 2003.