Type of mismatch in expression in Delphi 7 on SQL append
I have some code that checks the current date when the form is loaded, does a simple calculation and adds SQL to Delphi. It works on Windows 7 with Delphi 7, and on another computer with Xp, but not 3 other computers with Xp. When the form is loaded, it shows "type mismatch in expression" and points to the line after it was added. What could be the problem?
procedure TfmJaunumi.FormCreate(Sender: TObject);
var d1, d2: TDate;
begin
d1:= Date;
d2:= Date-30;
With qrJaunumi do
begin
Open;
SQL.Append('WHERE Sanem_datums BETWEEN' + #39 + DateToStr(d1) +
#39 + 'AND' + #39 + DateToStr(d2) + #39);
Active := True;
end;
end;
source to share
Unfortunately, none of the above worked, but the solution was to replace Format with "yy.mm.dd." instead of "yyyy.mm.dd." and adding single quotes. Strange, he says the "yyyy.mm.dd" format is everywhere. Now the code looks like this:
procedure TfmJaunumi.FormCreate(Sender: TObject);
var d1, d2: TDate; d3, d4, atd: String;
begin
d1:= Date;
d3:= FormatDateTime('yy.mm.dd.',d1);
d2:= Date-30;
d4:= FormatDateTime('yy.mm.dd.',d2);
atd := '''';
With qrJaunumi do
begin
Open;
SQL.Append('WHERE Sanem_datums BETWEEN'+ atd+d4+atd +'AND'+ atd+d3+atd+';');
Active := True;
end;
end;
source to share
As robsoft says, these are probably internationalization settings. You can use parameterized queries instead - they tend to be easier if you use dates and times.
Also, Open
after start is not required - in fact, it will open the query without the WHERE clause you add.
procedure TfmJaunumi.FormCreate(Sender: TObject);
var d1, d2: TDate;
begin d1:= Date; d2:= Date-30; With
qrJaunumi do
begin
SQL.Append('WHERE Sanem_datums BETWEEN :StartDate AND :EndDate');
// exact expression will vary according to DB connection type.
// Example is for TADOQuery.
Parameters.ParamByName('StartDate').Value := d1;
Parameters.ParamByName('EndDate').Value := d2;
Active := True;
end;
end;
source to share
You can use a prepared expression to solve any localization problems with time dates. DateToStr is client-side dependent. FormatDateTime may fail if the server localization does not accept the date format.
procedure TfmJaunumi.FormCreate(Sender: TObject);
var
d1, d2: TDate;
begin
d1:= Date;
d2:= Date - 30;
//qrJaunumi.SQL.Clear; removed because it would remove the "SELECT ... FROM ..." part
qrJaunumi.SQL.Add('WHERE Sanem_datums BETWEEN :StartDate AND :StopDate ');
qrJaunumi.Prepared := True;
qrJaunumi.ParamByName('StartDate').AsDateTime := d1;
qrJaunumi.ParamByName('StopDate').AsDateTime := d2;
qrJaunumi.Open; // = qrJaunumi.Active := True;
end;
The space after ": StopDate" is important because Delphi has a bug in the parameter parser if they haven't fixed it in newer versions.
source to share
This is almost certainly due to the local internationalization settings on those computers - DateToStr will return a string in the local date format (perhaps MM / DD / YYYY or DD / MM / YYYY) - and depending on where you are, it may not be what you are expecting.
I suspect that you will find that the computers it doesn't work on think they are in a different country / use a different internationalization for the computers it runs on.
The best solution would be to use FormatDateTime to get the date in the standard format your SQL Server installation will accept, so there is no chance that any local internationalization settings could interfere with this.
source to share