Board index » delphi » SQL and date fields in Paradox tables

SQL and date fields in Paradox tables

G'day all,
Using D7 Pro, ADO, SQL and ADODataSets from legacy Paradox tables.
The Paradox table has a field 'Received' which is a date field.
The SQL statement is built up in the following way in a TStringList, after
the user enters a valid date (checked) in an Edit box named SearchItem1...

SQLStrings:=TStringList.Create;
SQLStrings.Add('SELECT P.surname,P.forename,P.sex,P.dob,P.UR,P.received');
SQLStrings.Add('FROM Patients P');
SQLStrings.Add(' WHERE P.received = "'+SearchItem1.Text+'" ');

When I make this query active I get a "Data type mismatch in criteria
expression".
Why?
Cheers
Bill C

 

Re:SQL and date fields in Paradox tables


On Sat, 26 Apr 2003 11:48:09 +0930, Bill Carey <wca...@senet.co~m.au>
wrote:

Quote
> Using D7 Pro, ADO, SQL and ADODataSets from legacy Paradox tables.
> The Paradox table has a field 'Received' which is a date field.
> The SQL statement is built up in the following way in a TStringList,
> after
> the user enters a valid date (checked) in an Edit box named
> SearchItem1...

> SQLStrings.Add('FROM Patients P');
> SQLStrings.Add(' WHERE P.received = "'+SearchItem1.Text+'" ');

First problem is string and date field values should always be wrapped in
*single* quotes, that is the SQL standard. However the best solution always
for data values is to use parameters, then you do not have to worry about
the string format of the date being acceptable to the database you are
using - the database components and drivers will take care of that for you.

 SQLStrings.Add(' WHERE P.received = :recdate');
 When assigned to a query component, use the Params property:

 Query.Params[0].AsDateTime := StrToDate(SearchItem1.Text);

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
Newgroup Guidelines: info.borland.com/newsgroups/guide.html
Powered by Delphi and Interbase: www.logicfundamentals.com/RadBooks.html
"Democracy, without that guarantee of liberty, is merely a method of
selecting tyrants." - Alan Nitikman

Other Threads