I'm using a TADODataset and dynamic

[rewdee]

I'm using a TADODataset and dynamically creating the SQL. The TADOConnection connects to an Access 2000 database using OLEDB 4.0 Provider.

The code that tries to dynamically create the SQL is as follows:

sDate:=FormatDateTime('mm/dd/yyyy',FArchiveDate); 
sSQL:=Format('SELECT * FROM [%s] WHERE [%s].%s < #%s#',[FTblName,FTblName,FDateField,sDate]);

The query returns 0 records using the sSQL as the commandText property. Yet when I evaluate the expression which returns the following statement:

SELECT * FROM [Class Registration] WHERE [Class Registration].Year < #09/02/2000#

and copy and paste into Access Query Designer SQL view, it returns the correct amount of records.

Anyone know what's going on here and how I can dynamically create the SQL.

Thanks,
Bill N

P.s. [Class Registration].Year is a datetime field and NO I was not the programmer who named a datetime field "Year".

 

[davidchardonnet]

Hello,

I don't know if that's what you want but I have a 'brute force method' that always work for me.

I consider SQL commands like strings, ans i only use the '+' operator for generating the strings.

For this I use a
1 TADOConnection
1 TADOQuery
and that's all... no need for TADODataSet...

for example:
FTblName:='Class Registration';
FDateField:='Year';
sdate:=datetostr(FArchiveDate);

ADOQuery7.Close;
ADOQuery7.SQL.Clear;
ADOQuery7.SQL.Add('SELECT * FROM ['+FTblName+'] WHERE ['+FTblName+'].'+FDateField+' < #'+sDate+'#');
ADOQuery7.Open;
while (not ADOQuery7.Eof) do
begin

// For example, you read your data like this
a:=ADOQuery7.Fields[0].asInteger;
b:=ADOQuery7.Fields[1].asString;
c:=ADOQuery7.Fields[2].asInteger;
d:=ADOQuery7.Fields[3].asInteger;
end;
ADOQuery7.Close;

David

 

[LucieLastic]

It could be your date format too. Do you not need quotes around your date? (I've never used a # in sql). Anyway, you could try:

ADOQuery7.SQL.Add('SELECT * FROM ['+FTblName+'] WHERE ['+FTblName+'].'+FDateField+' < '''+ FormatDateTime('yyyy/mm/dd', YourdateTimeVar)+ ''');

 

[scottostanek]

I think the last guy who posted had the right thought, the date. Access uses a pretty strange SQL notation, but the ADO connection uses more of a vanilla SQL. Try just using the date in quotes, but you might also look closer at what
the results are like without the date option in there to see the real format being presented back to you.

Scotto the Unwise