Why do I get "type mismatch" when I put a date value in SQL?

Question

I am working on a search query function in Delphi 7 (working with a Paradox database) and I keep getting a type mismatch error when selecting between two dates. If I use the date type I get

Project Project1.Exe raised exception class EDBEngineError with message 'Type mismatch in expression.'. Process stoped.'

If i use a dateTime type I get

Project Project1.Exe raised exception class EDBEngineError with message 'Invalid use of keyword. Token : 13? AND Line Number: 8'. Process stoped.'

where 13 is the first digit of the time.

Here's my code:

procedure TForm1.Button1Click(Sender: TObject);
var
  Search1 :string;
  Search2 :string;
  outputveld : string;
  datum : TDateTime;
  datumZoek: TdateTime;
  countmails : integer;
  outfile: textfile;
  Zoek6MaandenTerug: Double;
begin
  Zoek6MaandenTerug := 182.621099;
  datum := tdate(now);
  datumZoek := datum - Zoek6MaandenTerug;
  ShowMessage(DateTimeToStr(Datum));
  ShowMessage(DateTimeToStr(datumZoek));
  Memo1.Lines.Add(DateTimeToStr(Datum));
  //datum := datum- StrToDate('21-4-2004');
  {radio button date controll}

  {//radio button date controll}
  Search1 := Edit1.Text;
  Search2 := Edit2.Text;

  assignfile(outfile,'text\Emails.txt');
  rewrite(outfile);
  outputveld := '';
  countmails := 0;

  {sets up  and executesSQL query(Query1)}
  Query1.close;
  Query1.SQL.Clear;
  memo1.Clear;
  if Search1 <> EmptyStr then
  begin
    //Query1.SQL.add('SELECT * FROM Verkoop');
    Query1.SQL.add('SELECT DISTINCT Verkoophandelingen.Klantnr, Verkoophandelingen.Type, verkoop.Klantnr, Verkoop.Artikelnr, Artikels.Nummer, Artikels.artikelgroep, Verkoophandelingen.Datum, Klanten.Email');
    Query1.SQL.add('FROM Verkoop');
    Query1.SQL.add('full Join Artikels ON Verkoop.Artikelnr = Artikels.Nummer');
    Query1.SQL.add('full Join Klanten ON Verkoop.Klantnr = Klanten.Nummer');
    Query1.SQL.add('full Join Verkoophandelingen ON Verkoop.verkoophandelingnr = Verkoophandelingen.nummer');
    Query1.SQL.add('WHERE Verkoophandelingen.Type = "Bestelling" ');
    Query1.SQL.add('AND Verkoop.Artikelnr = '+Search1+'');
    //Query1.SQL.add('AND Verkoophandelingen.Datum = '+ DateToStr(Date1) +'');
    Query1.SQL.add('AND Verkoophandelingen.Datum BETWEEN '+DateTimeToStr(datum)+'');
    Query1.SQL.Add('AND '+DateToStr(datumzoek)+'');
    Query1.SQL.add('ORDER BY Datum');

    Query1.RequestLive := true;
    Query1.open;
  end
  else if Search2 <> EmptyStr then
  begin
    Query1.SQL.add('SELECT DISTINCT Verkoophandelingen.Klantnr, Verkoophandelingen.Type, verkoop.Klantnr, Verkoop.Artikelnr, Artikels.Nummer, Artikels.artikelgroep, Verkoophandelingen.Datum, Klanten.Email');
    Query1.SQL.add('FROM Verkoop');
    Query1.SQL.add('full Join Artikels ON Verkoop.Artikelnr = Artikels.Nummer');
    Query1.SQL.add('full Join Klanten ON Verkoop.Klantnr = Klanten.Nummer');
    Query1.SQL.add('full Join Verkoophandelingen ON Verkoop.verkoophandelingnr = Verkoophandelingen.nummer');
    Query1.SQL.add('WHERE Verkoophandelingen.Type = "Bestelling" ');
    Query1.SQL.add('AND Artikels.ArtikelGroep = '+Search2+'');
    Query1.SQL.add('AND Verkoophandelingen.Datum BETWEEN '+DateToStr(datum)+'');
    Query1.SQL.Add('AND '+DateToStr(datumZoek)+'');
    Query1.SQL.add('ORDER BY Datum');
    Query1.RequestLive := true;
    Query1.open;
  end;

  while not Query1.Eof do
  begin
    if Query1.FieldByName('Email').AsString <>  EmptyStr then
    begin
      memo1.Lines.Add(Query1.FieldByName('Email').AsString + ';');
      writeln(outfile, Query1.FieldByName('Email').AsString+ ';');
      Query1.next;
      inc(countmails);
    end
    else
    begin
      Query1.next;
    end;
  end;

  if Query1.Eof then
  begin
    CloseFile(outfile);
    memo1.lines.add('totaal aantal valid email adressen = ' + IntToStr(countmails));
  end;
end;

I hope im posting in the right place. This is my code after adding parameters for my query still getting 'Type mismatch in expression.'.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, DBTables, DBCtrls;

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    Query1: TQuery;
    DBGrid1: TDBGrid;
    Button1: TButton;
    ComboBox1: TComboBox;
    Memo1: TMemo;
    Edit1: TEdit;
    Edit2: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Button2: TButton;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    procedure Button1Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure ComboBox1Change(Sender:TObject);
    procedure Edit1Change(Sender: TObject);
    procedure Edit2Change(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

uses ComObj;

{$R *.dfm}




procedure TForm1.FormActivate(Sender: TObject);

 var
i : integer;
mystringlist : tstringlist;
 datum: TDateTime;
   Zoek6MaandenTerug : Double;
begin
        Zoek6MaandenTerug := 182.621099;
        datum := tdate(now);
        datum := datum - Zoek6MaandenTerug;
        ShowMessage(DateToStr(datum));
        Memo1.Lines.Add(DateTimeToStr(Datum));
        Memo1.Lines.Add(DateToStr(datum));
        //datum := datum- StrToDate('21-4-2004');

   MyStringList := TStringList.Create;
    {
    memo1.Clear;
    Edit1.Clear;
    Edit2.Clear;
    }

  try
    Session.GetAliasNames(MyStringList);
    { fill a list box with alias names for the user to select from }
    for I := 0 to MyStringList.Count - 1 do begin
      combobox1.Items.Add(MyStringList[I]);
    end
    finally
    MyStringList.Free;
    end;

   end;

   procedure TForm1.ComboBox1Change(Sender: TObject);
begin

try
      Query1.SQL.Clear;
            Query1.Databasename := string(combobox1.items[combobox1.ItemIndex]);

        except
        with Application do
        begin
            NormalizeTopMosts;
            MessageBox(' wrong database ', 'fout..', MB_OK);
            RestoreTopMosts;
            combobox1.SetFocus;
        Exit;
        end;

end;
 end;



procedure TForm1.Button1Click(Sender: TObject);
var

  Search1 :String;
  Search2 :String;
  outputveld : string;
  datum : TDateTime;
  datumZoek: TDateTime;
  countmails : integer;
  outfile: textfile;
  Zoek6MaandenTerug: Double;

begin

        Zoek6MaandenTerug := 182.621099;
        datum := tdate(now);
        datumZoek := datum - Zoek6MaandenTerug;
        ShowMessage(DateTimeToStr(Datum));
        ShowMessage(DateTimeToStr(datumZoek));
        Memo1.Lines.Add(DateToStr(datum));
        Memo1.Lines.Add(DateToStr(datumZoek));

        //datum := datum- StrToDate('21-4-2004');



{//radio button date controll}
      Search1 := Edit1.Text;
      Search2 := Edit2.Text;


      assignfile(outfile,'text\Emails.txt');
      rewrite(outfile);
      outputveld := '';
      countmails := 0;


        {sets up  and executesSQL query(Query1)}
            Query1.close;
      Query1.SQL.Clear;
      memo1.Clear;
       if Search1 <> EmptyStr then
          begin


             //Query1.SQL.add('SELECT * FROM Verkoop');
            Query1.SQL.add('SELECT DISTINCT Verkoophandelingen.Klantnr, Verkoophandelingen.Type, verkoop.Klantnr, Verkoop.Artikelnr, Artikels.Nummer, Artikels.artikelgroep, Verkoophandelingen.Datum, Klanten.Email');
            Query1.SQL.add('FROM Verkoop');
            Query1.SQL.add('full Join Artikels ON Verkoop.Artikelnr = Artikels.Nummer');
            Query1.SQL.add('full Join Klanten ON Verkoop.Klantnr = Klanten.Nummer');
            Query1.SQL.add('full Join Verkoophandelingen ON Verkoop.verkoophandelingnr = Verkoophandelingen.nummer');
            Query1.SQL.add('WHERE Verkoophandelingen.Type = "Bestelling" ');
            Query1.SQL.add('AND Verkoop.Artikelnr = :Search1');
            //Query1.SQL.add('AND Verkoophandelingen.Datum = '+ DateToStr(Date1) +'');
           Query1.SQL.add('AND Verkoophandelingen.Datum BETWEEN :datum AND :datumzoek');


            Query1.SQL.add('ORDER BY Datum');


            Query1.ParamByName('datumzoek').Value := datumzoek;
            Query1.ParamByName('datum').Value := datum;
            Query1.ParamByName('Search1').Value := Search1;

                Query1.RequestLive := true;
                  Query1.open;

          end
          else if Search2 <> EmptyStr then
          begin

            Query1.SQL.add('SELECT DISTINCT Verkoophandelingen.Klantnr, Verkoophandelingen.Type, verkoop.Klantnr, Verkoop.Artikelnr, Artikels.Nummer, Artikels.artikelgroep, Verkoophandelingen.Datum, Klanten.Email');
            Query1.SQL.add('FROM Verkoop');
            Query1.SQL.add('full Join Artikels ON Verkoop.Artikelnr = Artikels.Nummer');
            Query1.SQL.add('full Join Klanten ON Verkoop.Klantnr = Klanten.Nummer');
            Query1.SQL.add('full Join Verkoophandelingen ON Verkoop.verkoophandelingnr = Verkoophandelingen.nummer');
            Query1.SQL.add('WHERE Verkoophandelingen.Type = "Bestelling" ');
            Query1.SQL.add('AND Artikels.ArtikelGroep = :Search2');
            //Query1.SQL.add('AND Verkoophandelingen.Datum BETWEEN '+DateToStr(datum)+'');
            //Query1.SQL.Add('AND '+DateToStr(datumZoek)+'');

            Query1.SQL.add('AND Verkoophandelingen.Datum BETWEEN :datum AND :datumzoek');

            Query1.SQL.add('ORDER BY Datum');

            Query1.ParamByName('datumzoek').Value := datumzoek;
            Query1.ParamByName('datum').Value := datum;
            Query1.ParamByName('Search2').Value := Search2;

            Query1.RequestLive := true;
                  Query1.open;

        end;




      while not Query1.Eof do
 begin
      if Query1.FieldByName('Email').AsString <>  EmptyStr then
        begin
          memo1.Lines.Add(Query1.FieldByName('Email').AsString + ';');
          writeln(outfile, Query1.FieldByName('Email').AsString+ ';');
          Query1.next;
          inc(countmails);
        end

        else
          begin
            Query1.next;
          end;
 end;

  if Query1.Eof then
 begin
  CloseFile(outfile);
  memo1.lines.add('totaal aantal valid email adressen = ' + IntToStr(countmails));

 end;

end;





procedure TForm1.Edit1Change(Sender: TObject);
begin
Edit2.Text := '';
end;

procedure TForm1.Edit2Change(Sender: TObject);
begin
Edit1.Text := '';
end;


 end.

after adding this

        ... 
        Query1.ParamByName('datumzoek').DataType := ftDate;
        Query1.ParamByName('datum').DataType := ftDate;
        Query1.ParamByName('Search1').DataType := ftInteger;

        Query1.ParamByName('datumzoek').Value := datumzoek;
        Query1.ParamByName('datum').Value := datum;
        Query1.ParamByName('Search1').Value := Search1;
        ...

the query gets run but with no results, after showing the query,text it seems the parameters have a "?" value ?

...
SELECT DISTINCT    Verkoophandelingen.Klantnr, Verkoophandelingen.Type, verkoop.Klantnr, Verkoop.Artikelnr, Artikels.Nummer, Artikels.artikelgroep, Verkoophandelingen.Datum, Klanten.Email
FROM Verkoop
full Join Artikels ON Verkoop.Artikelnr = Artikels.Nummer
full Join Klanten ON Verkoop.Klantnr = Klanten.Nummer
full Join Verkoophandelingen ON Verkoop.verkoophandelingnr = Verkoophandelingen.nummer
WHERE Verkoophandelingen.Type = "Bestelling"
AND Verkoop.Artikelnr = ?
AND Verkoophandelingen.Datum BETWEEN ? AND ?
ORDER BY Datum
...

(A side note) Do the two edit change handlers work for you? I would expect them to trigger each other. — Andriy M, Jan 30 '13 at 12:44
@AndriyM They work a the first input clears the box after it types — Gis, Jan 30 '13 at 12:58

score 8 · Answer 1 · edited Jan 30 '13 at 20:44

8

Perhaps these lines cause the issue:

Query1.SQL.add('AND Verkoophandelingen.Datum BETWEEN '+DateTimeToStr(datum)+'');
Query1.SQL.Add('AND '+DateToStr(datumzoek)+'');

Here you are inserting dates as returned by DateTimeToStr and DateToStr, but you are not delimiting the inserted values in any way, and so the resulting query will look something like this:

...
AND Verkoophandelingen.Datum BETWEEN 21-04-2004
AND 22-04-2004
...

I'm not sure what delimiter Paradox uses for date constants, but I'm almost sure it does use some. Perhaps, it should be ':

...
AND Verkoophandelingen.Datum BETWEEN '21-04-2004'
AND '22-04-2004'
...

Check with the manual for the correct one and fix your code accordingly.

On the other hand, it would be a much better idea to use parametrised queries, as @Rob Kennedy has correctly suggested. In a parametrised query, you use placeholders like :name where argument values should go. So, in your case it might look like this:

...
Query1.SQL.add('WHERE Verkoophandelingen.Type = "Bestelling" ');
Query1.SQL.add('AND Verkoop.Artikelnr = :Search');
Query1.SQL.add('AND Verkoophandelingen.Datum BETWEEN :date1');
Query1.SQL.Add('AND :date2');
...

Before running the query, you'll need to set up the parameters using the TQuery.Params property, something like this:

Query1.Params.CreateParam(ftInteger, 'Search', ptInput).AsInteger := StrToInt(Search1);
Query1.Params.CreateParam(ftDateTime, 'date1', ptInput).AsDateTime := datum;
Query1.Params.CreateParam(ftDateTime, 'date2', ptInput).AsDateTime := datumzoek;

Or, if the Query component auto-fills the Params collection when assigning the SQL statement:

Query1.Params.ParamByName('Search').AsInteger := StrToInt(Search1);
Query1.Params.ParamByName('date1').AsDateTime := datum;
Query1.Params.ParamByName('date2').AsDateTime := datumzoek;

That way you won't need to worry about delimiting values: the component will take care of that.

edited Jan 30 '13 at 20:44

Remy Lebeau

555,201
31
458
770

answered Jan 30 '13 at 12:52

Andriy M

76,112
17
94
154

9

If you just use a parameterized query, you don't need to worry about what the date delimiters are. – Rob Kennedy Jan 30 '13 at 12:54
Oops! You edited while I was writing mine. Mine deleted, and +1 for the addition of the parameterized query solution. – Ken White Jan 30 '13 at 13:34
3

+1 for parameters. We should have a big warning sign somewhere for newbie devs: "READ THIS FIRST IF YOU DON'T WANT TO GET YOUR ASS WHOOPED BY SQL INJECTION SCRIPT KIDDIES" – whosrdaddy Jan 30 '13 at 14:18
Agreeing with @whosrdaddy. If you're building queries using concatenation instead of parameters, **you are doing it wrong. Period.** Unfortunately, it's very simple and obvious to do it the wrong way, which is why it's so common. :( – Mason Wheeler Jan 30 '13 at 17:10
@whosrdaddy: you know, there are apps that are not exposed to SQL injection, simply because they have an embedded database... Paradox in effect is one of them. – Marjan Venema Jan 30 '13 at 18:17
@MarjanVenema: whatever reason there might be, it's a bad coding practice, period. Applications tend to get ported to other databases and WHAM WHOOPASS.. Devs should learn from the start how to do this... – whosrdaddy Jan 30 '13 at 19:10
Thanks for your replies , i tried both your suggestions but i still seem to get : "Project Project1.Exe raised exception class EDBEngineError with message 'Type mismatch in expression.'. Process stoped.' " Could it be anything else ? – Gis Jan 31 '13 at 08:58
@Gis: Could you please show what your code looks like in each case? – Andriy M Jan 31 '13 at 11:45

Why do I get "type mismatch" when I put a date value in SQL?

1 Answers1